Rookie here. I am trying to extend my dataset. It has the following structure
Date | A | B | C |
31-Mar-17 | 0.2 | 0.1 | 1.1 |
30-Jun-17 | 0.4 | 0.3 | 1.3 |
30-Sep-17 | 0.6 | 0.7 | 1.9 |
I am trying to extend this dataset for a number of periods. Also the variables once the dates are extended need to be a difference between current and previous value. It should follow the following structure:
Date | A | B | C |
31-Mar-17 | 0.2 | 0.1 | 1.1 |
30-Jun-17 | 0.4 | 0.3 | 1.3 |
30-Sep-17 | 0.6 | 0.7 | 1.9 |
31-Dec-17 | 0.2 | 0.4 | 0.6 |
31-Mar-18 | -0.4 | -0.3 | -1.3 |
30-Jun-18 | -0.6 | -0.7 | -1.9 |
Any ideas?
data have; infile cards expandtabs; input Date : date11. A B C; format date date11.; cards; 31-Mar-17 0.2 0.1 1.1 30-Jun-17 0.4 0.3 1.3 30-Sep-17 0.6 0.7 1.9 ; run; data want; set have end=last; laga=lag(a);lagb=lag(b);lagc=lag(c); output; if last then do; do i=1 to 3; date=intnx('month',date,1,'e'); _a=a-laga;_b=b-lagb;_c=c-lagc; laga=a;lagb=b;lagc=c; a=_a;b=_b;c=_c; output; end; end; drop i lag: _: ; run;
Post test data in the form of a datastep!
As such this is only pseudocode:
/* This adds a year to the data into a new dataset */ data have1; set have; date=intnx('year',date,1); run; /* set the two datasets */ data want; set have (in=a) have1 (in=b); if a then do; new_a=a; new_b=b; new_c=c; end; else do; new_a=lag(a)-lag2(a); new_b=lag(b)-lag2(b); new_c=lag(c)-lag2(c); end; run;
data have; infile cards expandtabs; input Date : date11. A B C; format date date11.; cards; 31-Mar-17 0.2 0.1 1.1 30-Jun-17 0.4 0.3 1.3 30-Sep-17 0.6 0.7 1.9 ; run; data want; set have end=last; laga=lag(a);lagb=lag(b);lagc=lag(c); output; if last then do; do i=1 to 3; date=intnx('month',date,1,'e'); _a=a-laga;_b=b-lagb;_c=c-lagc; laga=a;lagb=b;lagc=c; a=_a;b=_b;c=_c; output; end; end; drop i lag: _: ; run;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Check out this tutorial series to learn how to build your own steps in SAS Studio.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.