Dear,
I need help in my proc sql code. When i run the sql code, the output i am getting for the macro variables is
&treat1 Total
&treat2; a1
&treat3; b1
. The output I need is
&treat1 a1
&treat2; b1
&treat3; Total
For treat1 I need a1 value, for treat2 the value b1, treat3 the value sould be total.
Please suggest. In the sql code i tried trtn variable in the order variable, but i got error.
data sl;
input trt $1-5 trtn 7;
datalines;
a1 1
b1 2
Total 3
;
proc sql noprint;**added for table header;
select strip(put(count (distinct trt), best.)) into: cnt from sl;
select distinct trt into :treat1 -:treat&cnt. from sl
order by trt;
quit;
%put &treat1;
%put &treat2;
%put &treat3;
If you want SQL to generate data in a specify order you must tell it that by using the ORDER BY clause. Looks like trtN is the variable you should be using to order by. Note that there is no need to run the query twice just to get the count. SQL will count for you.
proc sql noprint;
select trt
into :treat1-
from sl
order by trtN
;
%let cnt=&sqlobs;
quit;
But it is probably a lot easier to just use a data step instead.
data _null_;
set sl;
call symputx(cats('treat',trtn),trt);
run;
Hi TOM Thank you very much for the help. The data step code is working for me instead of sql code. The sql code you posted is working only if there are 3 obs. In my data set there are several obs. The sql did not work if add another obs. I want to know if i can use distinct word in your sql code. For the below dataset the sql posted is not working. Please suggest
data sl;
input trt $1-5 trtn 7;
datalines;
a1 1
b1 2
Total 3
a1 1
;
proc sql noprint;
select trt
into :treat1-
from sl
order by trtN
;
quit;
%put &treat1;
%put &treat2;
%put &treat3;
Why does the file have multiple copies per TRT*TRTN combination? Doesn't seem like it was designed to use for this. The data step version appears to work because it just updates the same macro variable multiple times.
You might get the SQL query to work by just adding back the DISTINCT keyword.
Also if you want to eliminate the note about ordering by a variable not selected then you can also pull the TRTN variable and just stuff it into some other macro variable that you can ignore.
proc sql noprint;
select distinct trt,trn
into :treat1-
, :dummy
from sl
order by trtN
;
%let cnt=&sqlobs;
quit;
Another difference between the two is that the SQL method is just numbering the macro variables in the order they are generated. But the data step is using the value of TRTN to generate the macro variable name. So if your values of TRTN were 1,2,99 instead of 1,2,3 then the two methods would generate different macro variable names.
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.