below is the simple dataset.
I wanted to create macro variable, containing values of 'test' variable separated by space.
I don't want to use proc sql.
please suggest.
Expected output as ...
B A
data test;
test = "B"; output;
test = "A"; output;
run;
@abhinayingole wrote:
i believe sql will take it as alphabetically
You can modify SQL so the values are in some non-alphabetic order.
data seq;
set test;
n=_n_;
run;
proc sql noprint;
select distinct test into :macrovar separated by ' ' from seq order by n;
quit;
%put &=macrovar;
@abhinayingole wrote:
i believe sql will take it as alphabetically
Test your assumptions.
@abhinayingole wrote:
i believe sql will take it as alphabetically
Test it. You might be surprised.
data test;
test = "B"; output;
test = "A"; output;
run;
data _null_;
set test end=eof;
length str $100.;
retain str;
str = catx(" ", str, test);
if eof then call symputx('no_sql_list', str);
run;
%put &no_sql_list;
proc sql noprint;
select test into :sql_list separated by " " from test;
quit;
%put &sql_list;
@abhinayingole wrote:
below is the simple dataset.
I wanted to create macro variable, containing values of 'test' variable separated by space.
I don't want to use proc sql.
please suggest.
Expected output as ...
B A
data test; test = "B"; output; test = "A"; output; run;
For anyone else reading along who doesn't have the "No SQL" restriction (which seems rather pointless to me as PROC SQL is part of Base SAS, so everyone has it)
proc sql noprint;
select distinct(varname) into :macrovariablename separated by ' ' from datasetname;
quit;
What could be simpler?
And don't forget Maxim 14, Use the Right Tool
There does seem to be a significant proportion of posters that demand a problem be done in a certain way regardless of whether it is the best approach or not.
All I can say to them is look at all of the options and choose the best. You won't improve your SAS skills if you keep insisting your problem has to be done a certain way.
Agree with others, the SQL solution seems natural here.
But just for fun, I think the below DATA step will work too. It will be slow, as it is over-writing the macro variable for every iteration of the step, vs @Reeza's DATA step which (sensibly) only writes the macro variable once. But if you're learning the macro language, it's a helpful example of the sort of timing issues you can encounter. In this case, using the DATA step RESOLVE function allows you to resolve a macro variable during DATA step execution time rather than during DATA step compile time.
data test;
test = "B"; output;
test = "A"; output;
run;
%let no_sql_list= ;
data _null_ ;
set test ;
call symputx('no_sql_list', catx(" ",resolve('&no_sql_list'),test)) ;
run ;
%put >>&no_sql_list<<;
I might not be quite awake at the moment but macro variables do not per se create any output.
You have to use some statement or procedure to create "output". Where it appears could make a significant difference. What does your intended output go into? A data set(other questions to be answered)? A text file (no macro variables needed but likely other questions will need answers)? Word document? PDF document? External database ? Something else?
data test;
test = "B"; output;
test = "A"; output;
run;
proc transpose data=test out=have;
var test;
run;
data _NULL_;
set have;
call symputx('macvar', catx(' ',of col:));
run;
%put &=macvar;
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.