topic fill data for previous and after records in New SAS User

fill data for previous and after records

meckarthik — Thu, 06 Jun 2019 14:27:16 GMT

Hi, need help!

got a scenario, where need to populate the same record for above and below the value 1 and value 2 records until it got the same ID or value 1 or value 2 is not empty

ID	Date	Value1	Value2
1	14-Oct-17	.	.
1	15-Oct-17	.	.
1	16-Oct-17	.	.
1	17-Oct-17	.	.
1	18-Oct-17	.	.
1	19-Oct-17	1	2
1	20-Oct-17	.	.
1	21-Oct-17	.	.
1	22-Oct-17	2	5
1	23-Oct-17	.	.
1	24-Oct-17	.	.
2	14-Oct-17	.	.
2	15-Oct-17	.	.
2	16-Oct-17	.	.
2	17-Oct-17	4	6
2	18-Oct-17	.	.
2	19-Oct-17	.	.
2	20-Oct-17	.	.
2	21-Oct-17	.	.
2	22-Oct-17	.	.

I want something like

ID	Date	Value1	Value2
1	14-Oct-17	1	2
1	15-Oct-17	1	2
1	16-Oct-17	1	2
1	17-Oct-17	1	2
1	18-Oct-17	1	2
1	19-Oct-17	1	2
1	20-Oct-17	1	2
1	21-Oct-17	1	2
1	22-Oct-17	2	5
1	23-Oct-17	2	5
1	24-Oct-17	2	5
2	14-Oct-17	4	6
2	15-Oct-17	4	6
2	16-Oct-17	4	6
2	17-Oct-17	4	6
2	18-Oct-17	4	6
2	19-Oct-17	4	6
2	20-Oct-17	4	6
2	21-Oct-17	4	6
2	22-Oct-17	4	6

Re: fill data for previous and after records

PeterClemmensen — Thu, 06 Jun 2019 14:36:17 GMT

A little sorting and updating does the trick 🙂

data have;
input ID	Date:date11. 	Value1	Value2;
format Date date11.;
datalines;
1	14-Oct-17	.	.
1	15-Oct-17	.	.
1	16-Oct-17	.	.
1	17-Oct-17	.	.
1	18-Oct-17	.	.
1	19-Oct-17	1	2
1	20-Oct-17	.	.
1	21-Oct-17	.	.
1	22-Oct-17	2	5
1	23-Oct-17	.	.
1	24-Oct-17	.	.
2	14-Oct-17	.	.
2	15-Oct-17	.	.
2	16-Oct-17	.	.
2	17-Oct-17	4	6
2	18-Oct-17	.	.
2	19-Oct-17	.	.
2	20-Oct-17	.	.
2	21-Oct-17	.	.
2	22-Oct-17	.	.
;

data temp;
   update have(obs=0) have;
   by ID;
   output;
run;

proc sort data=temp;
   by ID descending Date; 
run;

data temp2;
   update temp(obs=0) temp;
   by ID;
   output;
run;

proc sort data=temp2 out=want;
   by ID Date; 
run;

proc datasets lib=work nolist;
   delete temp:;
run;

Re: fill data for previous and after records

meckarthik — Thu, 06 Jun 2019 15:20:33 GMT

It works! Thank you!

Re: fill data for previous and after records

PeterClemmensen — Thu, 06 Jun 2019 17:37:58 GMT

No problem, glad to help 🙂