topic Re: Loop Through Columns in SAS Programming

Loop Through Columns

SidS — Wed, 19 Jan 2011 02:20:56 GMT

I'm a SAS newbie and trying to figure out the best way to solve this problem :

A table with stock index values:
Date US UK Germany
Jan 1 100 100 100
Jan 2 101 104 103
Jan 3 102 105 104

I want to loop through all the countries I have and calculate returns for each one. So for example Jan 2 return for US will be (101-100)/100.
I could create lagged columns and then calculate returns in another column, but I am not sure how to loop through all the columns without hard coding the names of the country. Is there an elegant way using data step or will I have to venture into IML?

Re: Loop Through Columns

Doc_Duke — Wed, 19 Jan 2011 03:05:47 GMT

In the DATA step, this would be a classic application of an ARRAY. This paper puts it together pretty well:
http://support.sas.com/resources/papers/proceedings10/158-2010.pdf

You could also search support.sas.com for
array example
for a bunch of others.

Re: Loop Through Columns

SidS — Wed, 19 Jan 2011 04:40:03 GMT

Thanks Doc. If I used arrays like in the examples which you have attached, I would still need to specify the name of the countries. Is there a way to dynamically create arrays in which for example I could say use all columns after column 2?

Re: Loop Through Columns

Ksharp — Wed, 19 Jan 2011 09:05:35 GMT

Hi.
Try this . us is your first country, germany is your last country.

[pre]
data temp;
input Date $ US UK Germany;
cards;
Jan1 100 100 100
Jan2 101 104 103
Jan3 102 105 104
run;
data _null_;
set temp;
array country{*} us -- germany;
do i=1 to dim(country);
put country(i)=;
end;
run;
[/pre]

Ksharp

Re: Loop Through Columns

data_null__ — Wed, 19 Jan 2011 12:37:27 GMT

You need to learn about the "SAS Variable List".

http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a000695105.htm

Re: Loop Through Columns

sbb — Wed, 19 Jan 2011 13:24:21 GMT

One suggestion is PROC IMPORT with GETNAMES=YES.

Otherwise, a two DATA step approach will work. The first DATA step only reads the header row, extracting the list of country variables/columns, creating a SAS macro variable.

Then a second DATA step uses the macro variable (in the INPUT statement) to read your input file, starting with row #2 (FIRSTOBS= parameter on INFILE).

Also, given your data sample, the DATE information is split and will need to be parsed/input and assigned a SAS numeric (DATE type) variable likely using the MDY function or some other technique.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search arguments, this topic / post:

proc import getnames site:sas.com

data step variable list first row site:sas.com

Re: Loop Through Columns

chang_y_chung_hotmail_com — Wed, 19 Jan 2011 17:50:48 GMT

Calculation/data handling gets much easier if you reshape your data to a long format. Because, then you don't have to loop over columns.

The line creating the var, return, is from Howard Schreier's "Conditional Lagging" paper, available here.

/* test data */

data one;

format date e8601da.;

input mon $ day us uk germany;

date = input(catt(day,mon,year(date())),anydtdte.);

drop mon day;

cards;

Jan 1 100 100 100

Jan 2 101 104 103

Jan 3 102 105 104

run;

/* re-shape to long */

data long;

length cntry $20.;

set one;

cntry="us"; val = us; output;

cntry="uk"; val = uk; output;

cntry="germany"; val = germany; output;

keep date cntry val;

run;

/* sort and do by processing */

proc sort data=long;

by cntry date;

run;

data two;

set long;

by cntry date;

return = ifn(first.cntry, ., dif(val));

run;

/* check */

proc print data=two;

run;

/* on lst

Obs cntry date val return

1 germany 2011-01-01 100 .

2 germany 2011-01-02 103 3

3 germany 2011-01-03 104 1

4 uk 2011-01-01 100 .

5 uk 2011-01-02 104 4

6 uk 2011-01-03 105 1

7 us 2011-01-01 100 .

8 us 2011-01-02 101 1

9 us 2011-01-03 102 1

Re: Loop Through Columns

deleted_user — Wed, 19 Jan 2011 18:31:09 GMT

hello,

you can also take advantage of dictionary tables to create an array variable which
will be used later in a data step:

data temp;
input Date $ US UK Germany;
cards;
Jan1 100 100 100
Jan2 101 104 103
Jan3 102 105 104
run;

proc sql noprint;

select catx('_','ret',name) into :test separated by ' '
from sashelp.vcolumn
where name not eq 'Date' and libname='WORK' and memname='TEMP';

run;

data test;
set temp;
array country{*} us -- germany ;
array ret_country{*} &test;

do i=1 to dim(country);
ret_country{i}=(country{i}-lag(country{i}))/lag(country{i});
end;

run;

Marius

Re: Loop Through Columns

ChrisNZ — Tue, 27 Sep 2016 20:11:11 GMT

Editor's note: Thanks to @chang_y_chung_hotmail_com for offering alternate solutions. @Ksharp and @ChrisNZ both offered efficient one step solutions that address the question.

Quick and short:

data TEMP; 
input DATE $ US UK GERMANY;
cards;
Jan1 100 100 100
Jan2 101 104 103
Jan3 102 105 105
run;

data OUT; 
set TEMP; 
array country US -- GERMANY; 
do over country; 
country=dif(country)/lag(country);
end;
run;

proc print;
format _numeric_ percent7.2;
run;

Obs DATE US UK GERMANY

1 Jan1 . . .
2 Jan2 1.00% 4.00% 3.00%
3 Jan3 0.99% 0.96% 2.91%

As Chang said, a long table would be easier to handle though, especially the day you want to add the NZX or the North Korean stock exchange values (for example :o).

Re: Loop Through Columns

SidS — Thu, 20 Jan 2011 23:23:18 GMT

Thanks so much for all your prompt responses. Looking at the different ways of solving a problem really helps me learn SAS much quicker.