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
0
votes
1 answer

How to use select statement in a value statement

I'm not sure what's wrong with my code. The log is saying that the select has a syntax error which I copied below. How do I fix this? "Syntax error, expecting one of the following: a quoted string, a numeric constant,a datetime constant, a missing…
Sarah
  • 93
  • 1
  • 1
  • 8
0
votes
2 answers

SAS differences in outcome between sql and proc means

I want to have a weighted average of some variable in a macro variable. My var is zindi&aa and my weight is wprm&aa I am trying to make sense of two ways of doing it : one with a proc sql proc sql noprint; select mean(zindi&aa. *…
Anthony Martin
  • 767
  • 1
  • 9
  • 28
0
votes
2 answers

how can i create a variable depends on other observations

I have a college data, problem is finding if the same id, same course1 and course2 have next term or not. I have the table as below first 4 variables: id, term, course1 and course2. I am trying to create 5th variable 'nextterm'. Terms are like this:…
joys
  • 21
  • 5
0
votes
0 answers

proc sql join in do loop with macro

I am trying to join over 15 tables and I wrote a macro which has two do loops and the program I wrote is this way. %macro joinnow (year, Tno); %do year=1991 %to 2012; %do Tno=2 % to 23; Full join A_&year T&Tno ON…
user3658367
  • 641
  • 1
  • 14
  • 30
0
votes
1 answer

error with quotes while passing macro variable to proc sql

I am trying to do following in macro: proc sql; select * from table1 where col1 like 'x%' quit; %macro temp(val=x); proc sql; select * from table1 where col1 like '&val%' quit; %mend; The problem is that to resolve the value of val, it has…
user2542275
  • 137
  • 1
  • 7
0
votes
2 answers

PROC SQL select all of 2 tables and specifics in another

I have code like this PROC SQL; CREATE TABLE my_table as SELECT DISTINCT( t1.*, t2.*, t3.value3 ) FROM table1 as t1 INNER JOIN table2 as t2 ON t1.value = t2.value INNER JOIN t3.value as t3 ON t1.value2 = t3.value2 ; …
Jacob Ian
  • 669
  • 3
  • 9
  • 17
0
votes
2 answers

Using CASE and LIKE to evaluate - logic issues

Unfortunately I am not a coder…I have used CASE statements before but not in combination with "LIKE". I'm not sure if my issues have to do with CASE or something else. I am using proc sql (SAS) to identify cases that have purchased specific products…
user3791254
  • 45
  • 1
  • 1
  • 5
0
votes
1 answer

Group by date using proc sql in SAS

I want to sum up all values for the same cost centre per each year in SAS. I tried with Group By function in Proc SQL. PROC SQL CREATE TABLE incident_data AS SELECT Cost_Centre_Dim.Cost_Centre_Name, …
Bex Haina
  • 3
  • 1
  • 4
0
votes
1 answer

SAS- Counting Occurences happening during a given time period

I am trying to write a simple code to count number of services provided each year for a variety of providers, based on a single original column. Ideally my output would simplistically look something like: Input data would look something like…
Brad
  • 85
  • 12
0
votes
0 answers

Proc sql is Unable to insert into temporary index while processing summary functions. Query looks simple, just calculating summaries but data is huge

I'm trying to calculate Sums and Counts using proc sql...Query used to work but started throwing errors as data got huge... proc sql feedback; create table saslib.pre as select '01MAY2015'd as RUN_DT format=DATE9., '01APR2015'D as MONTHYR…
SAS_learner
  • 521
  • 1
  • 13
  • 30
0
votes
2 answers

Loop to read fixed record number of records at a time from a dataset until all the records are read

I'm working in the sales department of a company and every month we have some products which sales revenue are negative. My job is to extract those products ID to analyse them. I use WPS which uses SAS language. So I need first to extract the…
Serge Mbengue
  • 21
  • 2
0
votes
1 answer

SAS PROC SQL to join two tables by comparing date

Good day everyone. I am trying to join two datasets by comparing the dates. Suppose I have two datasets as such: ---Table A--- ---Table B--- ID Date1 ID Date2 01 29/1/2010 01 28/1/2011 01 29/1/2011 01 28/1/2012 01 …
saspower
  • 53
  • 1
  • 1
  • 4
0
votes
1 answer

how to count using proc sql certain conditions

I have a dataset (patients) as such: Pat_ID Hos Date A 11 1/1/2012 B 12 2/3/2012 B 13 2/3/2012 C 11 4/1/2012 C 11 4/5/2012 How do…
PinkyL
  • 341
  • 1
  • 8
  • 19
0
votes
1 answer

issue with nested select statements in proc sql

I was given this code to run but I keep getting errors even after I've made sure there are the right number of left/right brackets. This is the original code as my adding of brackets seemed to be in the wrong place. proc sql; create table…
PinkyL
  • 341
  • 1
  • 8
  • 19
0
votes
2 answers

how to count repeat dates using proc sql

I have a dataset as such: Pat_ID Date Prov_ID A 05/12/2012 X1 A 05/12/2012 X2 B 11/12/2012 X1 B 03/22/2012 X1 C 04/25/2012 X1 C 04/25/2012 X2 C 04/25/2012 X3 …
PinkyL
  • 341
  • 1
  • 8
  • 19
1 2 3
…
52 53