BookmarkSubscribeRSS Feed
knveraraju91
Barite | Level 11

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

6 REPLIES 6
Reeza
Super User

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

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

 

knveraraju91
Barite | Level 11

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

Jagadishkatam
Amethyst | Level 16

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
Reeza
Super User

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. 

Jagadishkatam
Amethyst | Level 16
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
rogerjdeangelis
Barite | Level 11
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 4865 views
  • 4 likes
  • 4 in conversation