Stack Overflow
Stack Overflow

Questions tagged [proc-sql]

proc sql is a SAS procedure used to submit SQL statements to the SAS compiler. For Oracle Pro*C, please use [oracle-pro-c].

proc sql; is a SAS procedure used to submit SQL statements to the SAS compiler. It is optionally terminated by a quit; statement. An example of typical syntax would be:

proc sql;
create table z as
  select * 
  from x
  left join y
  on x.id=y.id
  order by 1,2,3;
quit;

A more 'SAS specific' example would be the direct creation of macro variables, such as:

proc sql noprint; 
select someVariable into: MyMacroArray separated by ' ' 
  from work.Input where varCondition='True';

For further details on syntax, click here (v9.4) or here (v9.3)

794 questions
1
vote
2 answers

SAS macro passing SQL or code to use in macro

I am relatively new to SAS macro programming and I am mainly using it to make my code 'cleaner' and avoid errors in repetitive code. I have done some googling but have not found easy way for this. I have 2 questions first - how can I pass list of…
StackUnderflow
  • 37
  • 4
1
vote
3 answers

finding a maximum of 10 variables using proc sql

I am using a proc sql query to extract id and marks of 5 subjects. I would like to create a new column as maximum marks for that id. i tried to use the below query. but it is giving syntax error. proc sql; select id, m1, m2,m3,m4,m5, max(m1-m5) as…
ravi teja pandilla
  • 67
  • 1
  • 2
  • 9
1
vote
2 answers

sas sql update returns nothing

I have 2 tables - parent and child, with 2 columns in each - id and address, and address looks like this - \partNumber\a$\sometext....\ - for child and \partNumber\a$\ - for parent. And I need to make a table out of it with 2 columns - for every…
Vladimir Zaguzin
  • 191
  • 5
  • 22
1
vote
1 answer

Merge vs Proc sql

I have two ways of doing the same thing but am not getting the same results. what is the reason for this? First way: Proc sql; Create table abc.immu as select ID, DATE, CODE from xyz.imm where DATE between…
SAW
  • 11
  • 6
1
vote
1 answer

Using external data in SAS pass-through

I have created dataset table_db1 as below: proc sql; CONNECT TO ORACLE (DB1 details); CREATE TABLE table_db1 AS SELECT * FROM CONNECTION TO ORACLE (select * from db1.table where rownum<10); quit; I would like to use this data set to perform joins…
user1124702
  • 1,015
  • 4
  • 12
  • 22
1
vote
1 answer

Using a SAS macro variable to select all values using the IN operator in PROC SQL

In a SAS script I have a macro variable which is later used in an SQL in statement in a PROC SQL step. %let my_list = (1,2,3); proc sql; select * from my_table where var1 in &my_list. ; quit; This works fine, but I need some flexibility and…
Mark Heckmann
  • 10,943
  • 4
  • 56
  • 88
1
vote
2 answers

SAS insert value with proc sql

So I have a rather interesting problem. I am trying to insert a current date in specific formats and styles, but for some reason it seems to fail. I know its not a formatting issue... But idk how to fix it. a data step solution is welcomed as…
RGM-79FP GM Striker
  • 155
  • 1
  • 1
  • 14
1
vote
1 answer

SAS- Calculate Top Percent of Population

I am trying to seek some validation, this may be trivial for most but I am by no means an expert at statistics. I am trying to select patients in the top 1% based on a score within each drug and location. The data would look something like this…
bmb1020
  • 25
  • 2
1
vote
1 answer

Listing Called procs in tcl

Is there any way to list out all user defined proc calls in a Tcl file. ? I have a TCL file, I want to list out all procs that has been called by that file. Regards keerthan
keerthan kumar
  • 332
  • 7
  • 25
1
vote
1 answer

Date ranges in where clause of a proc SQL statement

There is a large table containing among other fields the following: ID, effective_date, Expiration_date. expiration_date is datetime20. format, and can be NULL I'm trying to extract rows that expire after Dec 31, 2014 or do not expire (NULL). Adding…
Ben
  • 485
  • 9
  • 19
1
vote
1 answer

SQL to PROC SQL- partition By alternative (min case)

I am new to SAS but know sql so trying to use SQL code to write proc sql code and realized that PARTITION by is not available in SAS. Table Customer_id Item_type Order Size Date …. 1. A401 Fruit Small …
viji
  • 425
  • 2
  • 6
  • 16
1
vote
4 answers

SAS: Merging two tables with identical columns while dropping null values

I'm not sure if the title does this question justice, but here it goes: I have three datasets Forecasts1, Forecasts2 and Forecasts3. They are all time series data composed of a date variable and variables r1 through r241. For a given r variable…
Josh Kraushaar
  • 369
  • 5
  • 17
1
vote
1 answer

SQL conditional for a field using multiple subqueries as cases

I am using Proc SQL, but this question should be relevant for all SQL variants. I am trying to populate a field BruceDPOtest with values from two subqueries with if the first query results in blanks--CASE WHEN BruceDPO = INPUT("", 8.) --it fills…
Haris Irshad
  • 29
  • 4
1
vote
2 answers

SAS: Using PROC SQL to write a date variable into a SAS dataset

I'm working on a piece of code in SAS designed to pull the last non-null value in a given column of time series data along with its corresponding date then insert them into a new dataset. SQL seems like by far the easiest way to do this, so that's…
Josh Kraushaar
  • 369
  • 5
  • 17
1
vote
2 answers

ERROR: Attribute '2017-02-28' not found

I have the following query which runs in SAS using proc sql where I have an automated variable which contains the month end date but it results in the following error ERROR: Prepare error: ICommandPrepare::Prepare failed. : ERROR: Attribute…
Karan Pappala
  • 581
  • 2
  • 6
  • 18
1 2 3
…
52 53