DATA Step, Macro, Functions and more

Loop Through Columns

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

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: 1,759

Re: Loop Through Columns

[ Edited ]
Posted in reply to deleted_user

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

View solution in original post


All Replies
Trusted Advisor
Posts: 2,115

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,020

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
Respected Advisor
Posts: 3,799

Re: Loop Through Columns

Super Contributor
Super Contributor
Posts: 3,174

Re: Loop Through Columns

Posted in reply to data_null__
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
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


   */

N/A
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: 1,759

Re: Loop Through Columns

[ Edited ]
Posted in reply to deleted_user

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

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 16768 views
  • 0 likes
  • 8 in conversation