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

Hi, hopefully this will be a relatively easy question.

 

I have a data set with the following format

 

ID    status

1        . 

1        .

1        . 

1        5

2        .

2        .

2        6

etc

 

would like to get it like this

 

ID    status

1        5 

1        5

1        5

1        5

2        6

2        6

2        6

 

pretty much need to fill "up" the rows instead of down. any thoughts?

 

thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

There are many scenario you need to consider about .

 

data have;
input ID    status;
cards;
1        . 
1        .
1        . 
1        5
2        .
2        .
2        6
;
run;

data want;
 do until(not missing(status) or last.id );
  set have;
  by id;  
 end;
 temp=status;
 do until(not missing(status) or last.id );
  set have;
  by id;
  _status=temp;output;
 end;
 drop temp status;
run;
 

View solution in original post

12 REPLIES 12
Reeza
Super User

If you only have one value per ID, you can use a summary function within SQL to obtain the value. 

See the example below.

 

proc sql;
create table want as
select *, max(status) as status2
from have
group by ID;
quit;
Ksharp
Super User

There are many scenario you need to consider about .

 

data have;
input ID    status;
cards;
1        . 
1        .
1        . 
1        5
2        .
2        .
2        6
;
run;

data want;
 do until(not missing(status) or last.id );
  set have;
  by id;  
 end;
 temp=status;
 do until(not missing(status) or last.id );
  set have;
  by id;
  _status=temp;output;
 end;
 drop temp status;
run;
 
madelman
Fluorite | Level 6

thanks!

Astounding
PROC Star

This works as long as it's always the last observation (per ID) that contains a nonmissing value.  If a nonmissing value comes earlier, you will run into trouble.

data_null__
Jade | Level 19

@Astounding wrote:

This works as long as it's always the last observation (per ID) that contains a nonmissing value.  If a nonmissing value comes earlier, you will run into trouble.


I believe you are wrong about that.

 

Capture.PNG

 

 

 

Astounding
PROC Star

Now try it with a slight variation to the data.

 

ID  Status

1    .

1    .

1    5

1    .

2    4

2    .

2    .

3    .

3   1

3   .

3   .

 

data_null__
Jade | Level 19

As expected.  @madelman mentions only "filling-up".

 

Capture.PNG

madelman
Fluorite | Level 6

Hi @Astounding, how would you deal with this data? the proc sql solution mentioned above didnt work for my data. thanks for your help.

FreelanceReinh
Jade | Level 19

The PROC SQL solution works with Astounding's test data. If it doesn't work with your data (incorrect output? error message in the log?), please post a sample of your data so that we can tell you how to fix the issue.

data_null__
Jade | Level 19

@madelman wrote:

Hi @Astounding, how would you deal with this data? the proc sql solution mentioned above didnt work for my data. thanks for your help.


@madelman Are you now saying that you DO want to fill down too?  I think this modification of @Ksharp program will do.

 

title;
data have;
   input ID status @@; 
   cards;
1 . 1 . 1 5 1 .
2 4 2 . 2 . 
3 . 3 1 3 . 3 .
4 . 4 . 4 5 4 . 4 4 4 . 4 .
;
run;
proc print;
   run;

data want;
   if 0 then set have;
   do until(not missing(status) or last.id );
      set have;
      by id;  
      if first.id then ltemp=.;
      end;
   temp=status;
   do until(not missing(status) or last.id );
      set have;
      by id;
      _status=coalesce(temp,ltemp);
      output;
      end;
   retain ltemp;
   ltemp=temp;
   drop temp ltemp;
   run;
proc print;
   run;

Capture.PNG

madelman
Fluorite | Level 6

thanks @data_null__ that worked beautifully!

Astounding
PROC Star

First, let me say that KSharp's solution is both elegant and satisfied the original request. I like his code, but wasn't sure if that original request actually illustrated all the conditions in the data that might occur.

 

To me, the real issue is the structure of the data originally.  For example, will there always be exactly one nonmissing STATUS per ID?  If so, you could code:

 

data want;

merge have (keep=ID) have (where=(status > .));

by ID;

run;

 

This also does no harm if an ID has all missing STATUS values.

 

But this has flaws if there could be more than one nonmissing STATUS per ID.  So the question goes back to STATUS.  Could there be more than one value per ID?  What positions could it occupy?  If there are two, what value should be used?  (Does it matter?)  In particular, what values should be used if the data looks like this?

 

ID   Status

1         .

1        1

1        .

1        2

1        .

 

Now there is a need to fill up, fill down, and fill in between.  The programming depends on the intended result.

 

A better modification due to the likelihood that you have additional variables in the data:

 

data want;

merge have (drop=status) have (keep=id status where=(status > .));

by id;

run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 12 replies
  • 6210 views
  • 6 likes
  • 6 in conversation