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

I have a data set where I'm using the prior row to populate data in the subsequent row with missing data.  I then need to delete the prior row.  Any ideas on how to properly do this?

 

data research.master;

set research.prior;

 

prev_value = lag(value);

 

if id = lag(id) then do;

     if value = '' then value = prev_value;

 

    //I THEN NEED TO DELETE THE PRIOR ROW HERE

end;

run;

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

The UPDATE statement does what you are looking for:  take in multiple records per ID, keep track of the latest nonmissing values, and output one record per ID.  Assuming your data set is sorted by ID, here is how it could be applied here:

 

data research.master;

update research.prior (obs=0) research.prior;

by id;

run;

 

This assumes that research.master does not exist prior to this step.  If it does, the program would have to change slightly (unless research.master can be replaced without losing important data).

View solution in original post

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

You would be better off merging the base record to empty record and coalescing the data:

proc sql;
  create table WANT as
  select  COALESCE(A.VAR1,B.VAR1) as VAR1,
          COALESCE(A.VAR2,B.VAR2) as VAR2
  from    (select * from HAVE where RECORD=1) A
  left join (select * from HAVE where RECORD=2) B
  on      A.IDVAR=B.IDVAR;
quit;

As you have not presented test data ( in the form of a datastep) the above is purely an example and you will need ot modify to your data.

Astounding
PROC Star

The UPDATE statement does what you are looking for:  take in multiple records per ID, keep track of the latest nonmissing values, and output one record per ID.  Assuming your data set is sorted by ID, here is how it could be applied here:

 

data research.master;

update research.prior (obs=0) research.prior;

by id;

run;

 

This assumes that research.master does not exist prior to this step.  If it does, the program would have to change slightly (unless research.master can be replaced without losing important data).

akordula
Calcite | Level 5

Thanks!! This did exactly what I was looking for.

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
  • 3 replies
  • 1679 views
  • 2 likes
  • 3 in conversation