topic Macro: looping through columns in SAS Programming

Macro: looping through columns

lucysas — Mon, 12 Mar 2012 17:32:47 GMT

Hi,

I'm a bit of a Macro noob, and was wondering whether someone could help me tackle this problem.

What I have is an array where individual data is organized by columns, e.g.

Obs a1 a2 b1 b2 etc

1 200 300 250 300

2 220 280 280 320

3 210 280 270 300

100 210 310 260 300

The names of the columns do not ascend in any particular order. I would like to cycle through each of the columns and perform some macro functions on each, and I'm not quite sure how to go about it. It would make sense to me to use a do loop, but I'm not sure how to retrieve the column names. I have also made another table which is just the column names, so I could iterate through that, but I'm not sure how to retrieve single table elements.

Please help! Greatly appreciated!

Thanks,

Lucy

Macro: looping through columns

Tom — Mon, 12 Mar 2012 17:43:19 GMT

I doubt that you want SAS macro logic to do anything with the values in columns of your data.

Usually you use macro code to generate SAS code that can perform calculations on values.

Can you explain what you want to do? It might even be possible to do it without any macro code.

Macro: looping through columns

lucysas — Mon, 12 Mar 2012 17:58:52 GMT

I subsequently do perform a set of functions on the columnar data: cumulative sum, loess procedure on the csum, regression etc. I have written these functions for data from one column, which I specify, and I just want to apply the same functions in the same order to every column in the dataset.

Macro: looping through columns

Tom — Mon, 12 Mar 2012 18:04:00 GMT

You can get a list of the variable names in a dataset from PROC CONTENTS or by querying SAS's internal metadata tables.

proc contents data=mydata out=contents noprint; run;

proc sql ;

select name into :varlist separated by ' '

from contents

;

%let n=&sqlobs;

quit;

%do i=1 %to &n ;

%let currentvar = %scan(&varlist,&i);

....repetitive code....

%end;

Macro: looping through columns

Tom — Mon, 12 Mar 2012 18:06:33 GMT

You can also look into transposing your data and then using BY variable processing.

proc transpose data=mydate out=vertical;

run;

proc means data=vertical;

by _name_ ;

var col1;

run;

.....