Create new rows with latest information until month end

Myurathan — Thu, 07 Jan 2021 20:55:39 GMT

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

Re: Create new rows with latest information until month end

novinosrin — Thu, 07 Jan 2021 21:02:24 GMT


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;

Re: Create new rows with latest information until month end

Myurathan — Thu, 07 Jan 2021 21:07:46 GMT

@novinosrin, You are the true master. Thank you so much for your help as always.

topic Re: Create new rows with latest information until month end in SAS Programming

Create new rows with latest information until month end

Re: Create new rows with latest information until month end

Re: Create new rows with latest information until month end