topic Re: how to convert every observation in to individual macros in SAS Programming

how to convert every observation in to individual macros

sahoositaram555 — Mon, 23 Sep 2019 08:24:13 GMT

Hi All,

I have a dataset named " masterdataset" looks like below

type	Count
A	223
B	123
C	343
D	767
E	233

I want to convert every individual rows in to individual macros. Eg: if i will do %put &=A then the result should be 223.

Tried to do with proc sql , but getting error

proc sql;
select distinct typeinto :groupA_bign-:groupB_bign from masterdataset;
quit;

ERROR: The following columns were not found in the contributing tables: masterdataset.

Kindly let me know if there is any other way to do this or how to modify the code to get the result.

Re: how to convert every observation in to individual macros

Kurt_Bremser — Mon, 23 Sep 2019 08:43:49 GMT

Use the Swiss Army Knife of SAS processing, the data step:

data _null_;
set masterdataset;
call symputx(type,count);
run;

Re: how to convert every observation in to individual macros

sahoositaram555 — Mon, 23 Sep 2019 09:02:01 GMT

Thanks,
It worked. @Kurt_Bremser Any comment/valuable inputs on proc sql statement which i have provided?

Re: how to convert every observation in to individual macros

Kurt_Bremser — Mon, 23 Sep 2019 10:06:44 GMT

@sahoositaram555 wrote:
Thanks,
It worked. @Kurt_Bremser Any comment/valuable inputs on proc sql statement which i have provided?

SQL does not support the dynamic creation of macro variable names from dataset columns, so it would be extremely clumsy to somehow re-engineer the call symput(x) function with it:

determine the number of observations
in a macro do loop:
get the name from a certain observation (using firstobs= and obs= dataset options)
set the value in a similar way, using the name from the previous step

This algorithm is just a suggestion, not tested in any way. I'm not sure if the obs= and firstobs= dataset options work in proc sql.