topic Re: how to created macro variable containing all values of the variable (no proc sql wanted) in SAS Programming

how to created macro variable containing all values of the variable (no proc sql wanted)

abhinayingole — Fri, 31 Dec 2021 18:18:55 GMT

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;

Re: how to created macro variable containing all values of the variable (no proc sql wanted)

Reeza — Fri, 31 Dec 2021 18:23:23 GMT

Why no SQL?

Re: how to created macro variable containing all values of the variable (no proc sql wanted)

abhinayingole — Fri, 31 Dec 2021 18:25:54 GMT

i believe sql will take it as alphabetically

Re: how to created macro variable containing all values of the variable (no proc sql wanted)

Reeza — Fri, 31 Dec 2021 18:26:54 GMT

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;

Re: how to created macro variable containing all values of the variable (no proc sql wanted)

PaigeMiller — Fri, 31 Dec 2021 18:26:57 GMT

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

Re: how to created macro variable containing all values of the variable (no proc sql wanted)

PaigeMiller — Fri, 31 Dec 2021 18:30:34 GMT

@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;

Re: how to created macro variable containing all values of the variable (no proc sql wanted)

Reeza — Fri, 31 Dec 2021 18:28:35 GMT

@abhinayingole wrote:
i believe sql will take it as alphabetically

Test your assumptions.

Re: how to created macro variable containing all values of the variable (no proc sql wanted)

Kurt_Bremser — Fri, 31 Dec 2021 18:40:54 GMT

@abhinayingole wrote:
i believe sql will take it as alphabetically

Test it. You might be surprised.

Re: how to created macro variable containing all values of the variable (no proc sql wanted)

SASKiwi — Sat, 01 Jan 2022 01:28:01 GMT

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.

Re: how to created macro variable containing all values of the variable (no proc sql wanted)

Astounding — Sat, 01 Jan 2022 07:24:02 GMT

The DISTINCT option forces SQL to alphabetize the values. Just leave it out (assuming your data doesn't contain duplicated values).

Better yet: describe the next step(s) once you have your macro variable. There are enough clever posters here that you may not need this macro variable at all.

Re: how to created macro variable containing all values of the variable (no proc sql wanted)

Quentin — Sat, 01 Jan 2022 22:14:07 GMT

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<<;

Re: how to created macro variable containing all values of the variable (no proc sql wanted)

ballardw — Sun, 02 Jan 2022 07:54:45 GMT

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?

Re: how to created macro variable containing all values of the variable (no proc sql wanted)

ghosh — Sun, 02 Jan 2022 21:52:13 GMT

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;