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