Loop Through Columns

[SidS]

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?

[Doc_Duke]

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.

[SidS]

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?

[Ksharp]

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

[data_null__]

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

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

[sbb]

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

[chang_y_chung_hotmail_com]

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

   */

[deleted_user]

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

[ChrisNZ · Accepted solution]

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

[SidS]

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