How to do create macros at a time

[thanikondharish]

Proc SQL;
Select distinct (sex), distinct (age) into:s1-s2,a1-a6 from sashelp.class;
Quit;


If I run above program it is not creating macros
How to do ?

[andreas_lds]

Read the documentation of "into" statement in proc sql ... there is no option to create a macro variable for each value. It is possible to create value lists:

proc sql noprint;
   select distinct Sex
      into :sList separated by ' '
      from sashelp.class
   ;
   select distinct Age
      into :aList separated by ' '
      from sashelp.class
   ;

quit;


%put &=sList;
%put &=aList;

Why do you need data in macro-variables?

[thanikondharish]

I know this coding here you are using 2 select statements but how to do in
single line I mean use single select statement

[Tom]

---

@thanikondharish wrote:
Proc SQL;
Select distinct (sex), distinct (age) into:s1-s2,a1-a6 from sashelp.class;
Quit;


If I run above program it is not creating macros
How to do ?

---

First it is probably a terrible idea to create so many macro VARIABLES from your data. Why not just leave your data in a dataset where it belongs?  Converting it into strings to store in macro variables is not only going to make it harder to work with it is going to cause the values of any floating point number to change.

The DISTINCT keyword in the SQL SELECT clause will make distinct OBSERVATIONS. So if you want the set of distinct combinations of SEX and AGE then you could do it in one query.  But if you want two separate lists of the distinct values of SEX and the distinct values of AGE then you need to use two separate queries.

proc sql noprint;
select distinct sex,age
  into :sxa1-
     , :axs1-
  from sashelp.class
;
%let n_sex_age=&sqlobs;
select distinct sex into :s1- from sashelp.class;
%let n_sex=&sqlobs;
select distinct age into :a1- from sashelp.class;
%let n_age=&sqlobs;
quit;

%put &=n_sex_age &=n_sex &=n_age ;

1219  %put &=n_sex_age &=n_sex &=n_age ;
N_SEX_AGE=11 N_SEX=2 N_AGE=6

So there are two values of SEX and six of AGE, but only 11 distinct combinations of SEX and AGE.

[Tom]

If you want the distinct values for a number of variables then use PROC SUMMARY.

proc summary data=sashelp.class ;
  class sex age ;
  ways 1;
  output out=want;
run;

proc print data=want;
run;

Obs    Sex    Age    _TYPE_    _FREQ_

 1             11       1         2
 2             12       1         5
 3             13       1         3
 4             14       1         4
 5             15       1         4
 6             16       1         1
 7      F       .       2         9
 8      M       .       2        10

[thanikondharish]

But need to keep in macros

[Tom]

---

@thanikondharish wrote:
But need to keep in macros

---

You haven't explained anything about what you are doing so how do you KNOW you need to make macro variables?

Macro variables can be useful for generating code, but perhaps what you need to do does not require any code generation? Or perhaps it would be easier to generate the code using SAS code instead of macro code?

[Kurt_Bremser]

---

@thanikondharish wrote:
But need to keep in macros

---

Maxim 14.

[Ksharp]

Your SQL statement

Select distinct (sex), distinct (age)

is not right statement. Therefore, try @Tom   code .

[ballardw]

You likely need distinct per variable for any use.

Proc SQL;
Select distinct sex into:s1-:s99 from sashelp.class;
quit;
%let levels_sex=&sqlobs.;
proc sql;
Select distinct age into:a1-:a99 from sashelp.class;
Quit;
%let levels_age=&sqlobs.;
%put &levels_sex. &levels_age. ;

The added Levels variable keeps track of how many actual S and A variables were created.