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

[abhinayingole]

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;

[Reeza]

Why no SQL?

[abhinayingole]

i believe sql will take it as alphabetically

[PaigeMiller]

---

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

[Reeza]

---

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

---

Test your assumptions. 

[Kurt_Bremser]

---

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

---

Test it. You might be surprised.

[Reeza]

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;

---

[PaigeMiller]

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

[SASKiwi]

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.

[Astounding]

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.

[Quentin]

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

[ballardw]

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?

[ghosh]

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;