topic fill in values for a limited number of observations in SAS Procedures

fill in values for a limited number of observations

wcw — Mon, 07 May 2018 16:40:12 GMT

I need to fill in numeric (integer) values for a series of ID variables using the (1st and only) value for the day down for the next 2 days (say 2 here, but I actually need 42). The values will all be the same for each variable. See example and data below. The DAY variable is also numeric. The ID variable names all follow the same pattern (ID1 - IDn, I've about 50).

Thanks so much.

HAVE
DAY	ID1	ID2	ID3	ID4
1	4	.	.	.
2	.	.	.	.
3	.	.	.	.
4	.	.	.	.
5	.	2	.	.
6	.	.	2	.
7	.	.	.	.
8	.	.	.	.
9	.	.	.	2
10	.	.	.	.
11	.	.	.	.
12	.	.	.	.




WANT
DAY	ID1	ID2	ID3	ID4
1	4	.	.	.
2	4	.	.	.
3	4		.	.
4	.	.	.	.
5	.	2	.	.
6	.	2	2	.
7	.	2	2	.
8	.	.	2	.
9	.	.	.	2
10	.	.	.	2
11	.	.	.	2
12	.	.	.	.

data have;

input day id1 - id4;

cards;

1 4 . . .

2 . . . .

3 . . . .

4 . . . .

5 . 2 . .

6 . . 2 .

7 . . . .

8 . . . .

9 . . . 2

10 . . . .

11 . . . .

12 . . . .

;

run;

Re: fill in values for a limited number of observations

Astounding — Mon, 07 May 2018 17:16:04 GMT

Love the problem. Here's a way.

data temp;

set have;

if n(of id: ) = 0 then output;

else do day=day to day + 2;

output;

end;

run;

proc sort data=temp;

by day;

run;

data want;

update temp (obs=0) temp;

by day;

run;

You might end up changing "day + 2" to "day + 42".

Re: fill in values for a limited number of observations

wcw — Mon, 07 May 2018 17:24:18 GMT

Wow, that is exquisite, you are indeed astounding, thank you so much!

Re: fill in values for a limited number of observations

novinosrin — Mon, 07 May 2018 18:33:32 GMT

data have;

input day id1 - id4;

cards;
1 4 . . .
2 . . . .
3 . . . .
4 . . . .
5 . 2 . .
6 . . 2 .
7 . . . .
8 . . . .
9 . . . 2
10 . . . .
11 . . . .
12 . . . .
;


run;

data _null_;
if _n_=1 then do;
 dcl hash H (dataset:'have',ordered:'y') ;
   h.definekey  ('day') ;
   h.definedata ('day',"id1",'id2','id3','id4') ;
   h.definedone () ;
end;
set have end=last;
array t(*) id:;
if n(of id: )>0 then do;
do i=1 to dim(t);
if not missing(t(i)) then do;
_k=t(i);
do day=day to day + 2;
h.find();
t(I)=_k;
h.replace();
end;
end;
end;
end;
if last then h.output(dataset:'want');
run;