topic Re: turning each field in a one-record table into a macro variable in SAS Programming

turning each field in a one-record table into a macro variable

WillM — Fri, 27 May 2016 17:36:30 GMT

I use SAS EG 7.1. I have a one record table with 150 columns, creatively named fieldname1 - fieldname150. I would like to turn each field's value into a macro variables &fieldname1 - &fieldname150. I have written it out as

data _NULL_;

set mylib.proj_details;

call symputx('fieldname1',fieldname1,'l');

call symputx('fieldname2',fieldname2,'l');

call symputx('fieldname3',fieldname3,'l');

...

call symputx('fieldname149',fieldname149,'l');

call symputx('fieldname150',fieldname150,'l');

run;

There's got to be a sexier way to do this. I tried using a do loop but couldn't get the second argument (in which I was concatenating "fieldname" and my index variable) to be recognized. Any ideas? Thank you in advance!

Re: turning each field in a one-record table into a macro variable

Reeza — Fri, 27 May 2016 18:19:57 GMT

You can use an array to shorten your code.

data _NULL_;
     set mylib.proj_details;
    array field(150) fieldname1-fieldname150;

do i=1 to 150;
     call symputx(catt('fieldname', put(i, 3. -l)),field(i),'l');
end;

run;

Re: turning each field in a one-record table into a macro variable

WillM — Fri, 27 May 2016 18:55:50 GMT

Thanks! It's beautiful.

Re: turning each field in a one-record table into a macro variable

Astounding — Fri, 27 May 2016 18:57:41 GMT

Perhaps use macro language to generate your statements:

%macro all150;

%local i;

%do i=1 %to 150;

call symputx("fieldname&i", fieldname&i, 'l');

%end;

run;

data _null_;

set mylib.proj_details;

%all150

run;