01-20-2017 10:32 PM
I need to create macro variables for the output values produced using proc sql;
create table one as
select count(distinct usubjid) as NS,TRT1
group by TRT1;
output produced by sql code;
How to create 4 macro variables equal to A,B,C, and Total, so I can use these later in data step. Please help
01-20-2017 10:37 PM - edited 01-20-2017 10:38 PM
Don't use SQL use a data step with CALL SYMPUTX.
Proc SQL doesn't have a method for dynamically assigning macro variable names.
01-20-2017 11:00 PM
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
01-20-2017 11:27 PM - edited 01-20-2017 11:29 PM
try the below step
select count(distinct usubjid) into: TRT1-
group by TRT1 order by trt1;
%put &TRT1 &TRT2 &TRT3 &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.
01-20-2017 11:30 PM
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.
01-20-2017 11:40 PM
01-21-2017 09:37 AM
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