BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Myurathan
Quartz | Level 8

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

DateCIDValue_1Value_2
27/01/2020111652356
28/01/2020111325658
29/01/2020111632999
29/01/2020222256658
30/01/2020222852963
24/02/2020111652356
25/02/2020111325658
27/02/2020222256658

 

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)

DateCIDValue_1Value_2
27/01/2020111652356
28/01/2020111325658
29/01/2020111632999
30/01/2020111632999
31/01/2020111632999
29/01/2020222256658
30/01/2020222852963
31/01/2020222852963
24/02/2020111652356
25/02/2020111325658
26/02/2020111325658
27/02/2020111325658
28/02/2020111325658
29/02/2020111325658
27/02/2020222256658
28/02/2020222256658
29/02/2020222256658

 

Thank you so much in advance for your time and help. 

 

Regards,

Myu

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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;

View solution in original post

2 REPLIES 2
novinosrin
Tourmaline | Level 20

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;
Myurathan
Quartz | Level 8
@novinosrin, You are the true master. Thank you so much for your help as always.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 2 replies
  • 425 views
  • 1 like
  • 2 in conversation