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
Don't use SQL use a data step with CALL SYMPUTX.
Proc SQL doesn't have a method for dynamically assigning macro variable names.
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
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.
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.