BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SidS
Calcite | Level 5
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?
1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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

View solution in original post

9 REPLIES 9
Doc_Duke
Rhodochrosite | Level 12
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
Calcite | Level 5
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
Super User
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
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Obsidian | Level 7
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
Not applicable
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
Tourmaline | Level 20

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
Calcite | Level 5
Thanks so much for all your prompt responses. Looking at the different ways of solving a problem really helps me learn SAS much quicker.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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