DATA Step, Macro, Functions and more

How to create macro variables using proc sql

Reply
Super Contributor
Posts: 272

How to create macro variables using proc sql

Dear,

 

I need to create macro variables for the output values produced using proc sql;

 

my code:

proc sql;
create table one as
select count(distinct usubjid) as NS,TRT1
from data1
group by TRT1;
quit;

 

output produced by sql code;

NS     TRT1

50        A

60        B

40        C

150       Total

 

How to create 4 macro variables equal to A,B,C, and Total, so I can use these later in data step. Please help

 

Thanks

Super User
Posts: 19,772

Re: How to create macro variables using proc sql

[ Edited ]
Posted in reply to knveraraju91

Don't use SQL use a data step with CALL SYMPUTX.  

Proc SQL doesn't have a method for dynamically assigning macro variable names. 

 

Super Contributor
Posts: 272

Re: How to create macro variables using proc sql

I am sorry that if I didnot explain properly.

I am using PROC sql to get the values in my program. I also need to assign the values into a macro variables using INTO: so that I can use the values later in the step. Thank you

Trusted Advisor
Posts: 1,137

Re: How to create macro variables using proc sql

[ Edited ]
Posted in reply to knveraraju91

try the below step

proc sql;
select count(distinct usubjid) into: TRT1-
from data1
group by TRT1 order by trt1;
quit;

%put &TRT1 &TRT2 &TRT3 &TRT4;

%let A=&TRT1;
%let B=&TRT2;
%let C=&TRT3;
%let TOTAL=&TRT4;


This will create 4 macro variables like TRT1 ,TRT2, TRT3 and TRT4 with counts and macro variable names could be changed to as provided.

Thanks,
Jag
Super User
Posts: 19,772

Re: How to create macro variables using proc sql

Posted in reply to knveraraju91

I don't think you can create macro variables and a data set at the same time. Since you need another step use a data step. 

 

I understood your question as you want to create 4 macro variables called A, B, C & Total which are one column and the values to be from the second column. This can't be done in a single SQL query either, or at least simply. 

 

If you just want all 4 numerical values into 4 different macro variables named somerging like var1-var4 or a single macro variable then use the examples in the documentation. However, given your experience on the forum I assumed you were asking a more complex question. 

Trusted Advisor
Posts: 1,137

Re: How to create macro variables using proc sql

I agree with @Reeza to get the macro variable names with treatment names then the best is to use call symputx.
use the dataset you developed by proc sql which has the count and treatment variable like below

NS TRT1
50 A
60 B
40 C
150 Total

then, try

data one;
input NS TRT1$13.;
cards;
50 A
60 B
40 C
150 Total
;

data _null_;
set one;
call symputx(trt1,ns);
run;

%put &a &b &c &total;
Thanks,
Jag
Valued Guide
Posts: 505

Re: How to create macro variables using proc sql

Posted in reply to Jagadishkatam
Here is a related solution

/* T008040 Placing counts in proc report column headers inspired by How to create macro variables using proc sql https://goo.gl/EtsSFo https://communities.sas.com/t5/Base-SAS-Programming/How-to-create-macro-variables-using-proc-sql/m-p/326471 Suppose you want to produce a report with a header that contains 'treatment name and counts HAVE Up to 40 obs from SexGov total obs=50 Obs SEX GOV IDS SERIAL PAY 1 Female FBI 13 25439 3463 2 Female FBI 14 629949 28614 3 Female FBI 17 851828 27102 4 Female FBI 20 66992 19882 5 Female FBI 23 659046 34665 6 Female FBI 25 248322 17126 .... 37 Male CIA 17 786544 47492 38 Male CIA 21 716109 27873 39 Male CIA 22 731075 22004 40 Male CIA 23 933118 11092 WANT Female Male GOV N = 21 N = 29 CIA 5 9 FBI 6 10 IRS 10 10 SOLUTION WORKING CODE ===================== resolve(catx(' ','%let',sex,'=%str(',sex,'# N =',put(count(*),2. -l),');')) FULL SOLUTION ============= * create sample data; Data SexGov; Do Sex='Female','Male'; Do Gov='FBI','CIA','IRS'; Do Ids=10 to 25; Serial=Int(1E6*Uniform(5739)); Pay=Int(50000*Uniform(57343)); If Uniform(5643) < .5 Then Output; End; End; End; Run; * create macro variable Male with "Male # N=29"; proc sql; create table hdr as select resolve(catx(' ','%let',sex,'=%str(',sex,'# N =',put(count(*),2. -l),');')) ,* from sexgov group by sex;quit; %put &=female; %put &=male; /* FEMALE= Female # N = 21 MALE = Male # N = 29 */ /* Put counts in Rectangular array */ Ods Exclude All; Ods Output Observed=hdr(Rename=Label=Gov drop=sum where=(gov ne 'Sum')); Proc Corresp Data=SexGov Observed dim=1; Table Gov, Sex; Run; Ods Select All; * print report note the macro variables; proc report data=hdr nowd missing split='#' headskip; cols Gov Female Male; define Female /display "&female" width=12; define Male /display "&male" width=12; run;quit; Female Male GOV N = 21 N = 29 CIA 5 9 FBI 6 10 IRS 10 10
Ask a Question
Discussion stats
  • 6 replies
  • 1403 views
  • 4 likes
  • 4 in conversation