BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
eabc0351
Quartz | Level 8

Hello. I have the following data:

data have;
input id day1 day2 day3 day4 otherd7;
datalines;
1 -11 -10 -9 -8 0
1 -7 -6 . . .
2 -20 -19 -18 -17 1
2 -16 . . . .
3 -8 -7 -6 -5 .
3 -4 . . . .
;
run;

 I would like the following data:

data want;
input id day1 day2 day3 day4 otherd7;
datalines;
1 -11 -10 -9 -8 0
1 -7 -6 -10 -9 0
2 -20 -19 -18 -17 1
2 -16 -19 -18 -17 1
3 -8 -7 -6 -5 .
3 -4 -7 -6 -5 .
;
run;

This is just a sample of the variables in the table. I would like to do this for every variable in the table. Each ID has 2 observations. Essentially, I would like to "fill down" if the value in last.id is missing. Could someone please help me with some code. I figured out how to do this for one variable at a time, but cannot figure out how to do it for all the variables in one easy step. Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
data have;
input id day1 day2 day3 day4 otherd7;
datalines;
1 -11 -10 -9 -8 0
1 -7 -6 . . .
2 -20 -19 -18 -17 1
2 -16 . . . .
3 -8 -7 -6 -5 .
3 -4 . . . .
;
run;

data want;
  update have(obs=0) have;
  by id;
  output;
run;

View solution in original post

5 REPLIES 5
eabc0351
Quartz | Level 8
Quick edit -- the first row is supposed to be:
1 -11 -10 -9 -8 0
1 -7 -6 -9 -8 0
novinosrin
Tourmaline | Level 20
data have;
input id day1 day2 day3 day4 otherd7;
datalines;
1 -11 -10 -9 -8 0
1 -7 -6 . . .
2 -20 -19 -18 -17 1
2 -16 . . . .
3 -8 -7 -6 -5 .
3 -4 . . . .
;
run;

data want;
  update have(obs=0) have;
  by id;
  output;
run;
eabc0351
Quartz | Level 8
Oh that was so easy. I tried it but had the code a little off. Thank you!
CurtisMackWSIPP
Lapis Lazuli | Level 10

Glad you made that correction, I was having trouble replicating it.  Here is a fairly elegant solution.

 

data mywant;
  set have;
  array toretain day1 day2 day3 day4 otherd7;
  by id;
  do over toretain;
    junk = lag(toretain);
    toretain = coalesce(toretain,junk);
  end;
  output;
  if last.id then do over toretain;
    toretain = .;
  end;
run;

Note that the real trick is setting junk = to the lag.  Lag only works if it is called every row.  If the lag was in the coalesce will not call it if the current row has a value.

eabc0351
Quartz | Level 8
Thank you @CurtisMackWSIPP, I will give that a try as well.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 495 views
  • 0 likes
  • 3 in conversation