Help using Base SAS procedures

fill in values for a limited number of observations

Accepted Solution Solved
Reply
Occasional Contributor wcw
Occasional Contributor
Posts: 17
Accepted Solution

fill in values for a limited number of observations

[ Edited ]

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;

 


Accepted Solutions
Solution
‎05-07-2018 01:24 PM
Super User
Posts: 6,762

Re: fill in values for a limited number of observations

[ Edited ]

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

View solution in original post


All Replies
Solution
‎05-07-2018 01:24 PM
Super User
Posts: 6,762

Re: fill in values for a limited number of observations

[ Edited ]

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

Occasional Contributor wcw
Occasional Contributor
Posts: 17

Re: fill in values for a limited number of observations

Posted in reply to Astounding

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

PROC Star
Posts: 1,784

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 158 views
  • 2 likes
  • 3 in conversation