## Loop Through Columns

Solved
Occasional Contributor
Posts: 9

# Loop Through Columns

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?

Accepted Solutions
Solution
‎09-27-2016 04:11 PM
PROC Star
Posts: 2,329

## Re: Loop Through Columns

[ Edited ]

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 ).

All Replies
Posts: 2,124

## Re: Loop Through Columns

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.
Occasional Contributor
Posts: 9

## Re: Loop Through Columns

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?
Super User
Posts: 10,761

## Re: Loop Through Columns

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
Posts: 3,852

## Re: Loop Through Columns

Super Contributor
Posts: 3,176

## Re: Loop Through Columns

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.

proc import getnames site:sas.com

data step variable list first row site:sas.com
Regular Contributor
Posts: 241

## Re: Loop Through Columns

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

*/

Not applicable
Posts: 0

## Re: Loop Through Columns

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
Solution
‎09-27-2016 04:11 PM
PROC Star
Posts: 2,329

## Re: Loop Through Columns

[ Edited ]

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 ).

Occasional Contributor
Posts: 9

## Re: Loop Through Columns

Thanks so much for all your prompt responses. Looking at the different ways of solving a problem really helps me learn SAS much quicker.
🔒 This topic is solved and locked.