Dear Experts,
I have the following data (table1)
data table01;
input Date :ddmmyy10. CID Value_1 Value_2;
format date ddmmyy10.;
cards;
27/01/2020 111 652 356
28/01/2020 111 325 658
29/01/2020 111 632 999
29/01/2020 222 256 658
30/01/2020 222 852 963
24/02/2020 111 652 356
25/02/2020 111 325 658
27/02/2020 222 256 658
;
run;
Table 01
Date | CID | Value_1 | Value_2 |
27/01/2020 | 111 | 652 | 356 |
28/01/2020 | 111 | 325 | 658 |
29/01/2020 | 111 | 632 | 999 |
29/01/2020 | 222 | 256 | 658 |
30/01/2020 | 222 | 852 | 963 |
24/02/2020 | 111 | 652 | 356 |
25/02/2020 | 111 | 325 | 658 |
27/02/2020 | 222 | 256 | 658 |
I want to insert new rows for each CID to have the data until the month-end with the latest values of value_1 and value_2.
I want the output table to look like below. (BOLD ITALIC values are the newly created values)
Date | CID | Value_1 | Value_2 |
27/01/2020 | 111 | 652 | 356 |
28/01/2020 | 111 | 325 | 658 |
29/01/2020 | 111 | 632 | 999 |
30/01/2020 | 111 | 632 | 999 |
31/01/2020 | 111 | 632 | 999 |
29/01/2020 | 222 | 256 | 658 |
30/01/2020 | 222 | 852 | 963 |
31/01/2020 | 222 | 852 | 963 |
24/02/2020 | 111 | 652 | 356 |
25/02/2020 | 111 | 325 | 658 |
26/02/2020 | 111 | 325 | 658 |
27/02/2020 | 111 | 325 | 658 |
28/02/2020 | 111 | 325 | 658 |
29/02/2020 | 111 | 325 | 658 |
27/02/2020 | 222 | 256 | 658 |
28/02/2020 | 222 | 256 | 658 |
29/02/2020 | 222 | 256 | 658 |
Thank you so much in advance for your time and help.
Regards,
Myu
data table01;
input Date :ddmmyy10. CID Value_1 Value_2;
format date ddmmyy10.;
cards;
27/01/2020 111 652 356
28/01/2020 111 325 658
29/01/2020 111 632 999
29/01/2020 222 256 658
30/01/2020 222 852 963
24/02/2020 111 652 356
25/02/2020 111 325 658
27/02/2020 222 256 658
;
run;
data want;
do until(last.cid);
set table01;
by cid notsorted;
output;
end;
do date=date+1 to intnx('mon',date,0,'e');
output;
end;
run;
data table01;
input Date :ddmmyy10. CID Value_1 Value_2;
format date ddmmyy10.;
cards;
27/01/2020 111 652 356
28/01/2020 111 325 658
29/01/2020 111 632 999
29/01/2020 222 256 658
30/01/2020 222 852 963
24/02/2020 111 652 356
25/02/2020 111 325 658
27/02/2020 222 256 658
;
run;
data want;
do until(last.cid);
set table01;
by cid notsorted;
output;
end;
do date=date+1 to intnx('mon',date,0,'e');
output;
end;
run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.