topic Re: Need to run this macro but I have 600 variables (var1, var2.... var600) in SAS Programming

Need to run this macro but I have 600 variables (var1, var2.... var600)

rykwong — Wed, 11 Jan 2017 19:05:31 GMT

Dear SAS community

I was given this macro which is useful to use but it only runs it for var1. I need to repeat this for all 600 variables (var1, var2, var3... ...var600) in a dataset. Each of var1, var2....var600 is a column of data in the dataset.

My purpose of running this macro is to extract the name of the dependent variables, name of the variable (i.e. var1), and the P-value of the interaction term (drug*var1). Is there a way to automate this? Many thanks

ods graphics / reset=all;

%macro loop;

data pv2_var1;

format y $20.;

run;

%let endpoint=d_lvef ln_lvesvi d_globalecvrem ln_ecv_rem d_total_lge_fw ln_total_lge_fw;

%do i=1 %to 6;

%let y=%scan(&endpoint,&i,' ');

title "Endpoint = &y";

ODS OUTPUT SOLUTIONF=RESULTS;

proc mixed data=testset covtest noclprint plots=studentpanel method=reml;

class patient_id site_id;

model0: model &y = drug var1 drug*var1/ s cl ddfm=kenwardroger;

run;

DATA pv2_var1;

SET pv2_var1 RESULTS(IN=A);

if missing(effect) then delete;

IF A THEN Y="&y";

RUN;

%end;

title " ";

%mend;

%loop

Re: Need to run this macro but I have 600 variables (var1, var2.... var600)

Astounding — Wed, 11 Jan 2017 22:52:51 GMT

What is it that you want to automate, that isn't already automated? How to get a list of 600 variable names? The rest looks pretty well automated except for one piece. The final DATA step should be replaced with:

data results;

set results;

length y $ 32;

y = "&y";

if missing(effect) then delete;

run;

proc append data=results base=pv2_var1;

run;

Re: Need to run this macro but I have 600 variables (var1, var2.... var600)

rykwong — Thu, 12 Jan 2017 00:13:12 GMT

thanks. I needed to run 600 variables, so instead of var1, run the macro with var2, then var3..... all the way to var600. Note that var1, var2 ...etc are just names for the purpose of discussion, the variables have read names and they are not patterned. There are also variable columns that are not a part of the 600 in between these 600 variables in the list (although I can remove them)

many thanks

Re: Need to run this macro but I have 600 variables (var1, var2.... var600)

Reeza — Thu, 12 Jan 2017 02:41:24 GMT

http://www.ats.ucla.edu/stat/sas/seminars/sas_macros_introduction/

If you make your macro more generic, to take a parameter that's the variable name then you can query the DICTIONARY.COLUMNS table to get a variable list. Then you can use CALL EXECUTE to execute the macro for each variable.

Re: Need to run this macro but I have 600 variables (var1, var2.... var600)

rykwong — Sun, 15 Jan 2017 00:26:26 GMT

thanks so much. this solves this issue. Much appreciated!