Calcite | Level 5

## fill in values for a limited number of observations

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Opal | Level 21

## Re: fill in values for a limited number of observations

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".

3 REPLIES 3
Opal | Level 21

## Re: fill in values for a limited number of observations

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".

Calcite | Level 5

## Re: fill in values for a limited number of observations

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

Tourmaline | Level 20

## Re: fill in values for a limited number of observations

``````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;``````
Discussion stats
• 3 replies
• 658 views
• 2 likes
• 3 in conversation