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

Hello Everyone!

In my below sample, I would like to find the previous date of lab sample collected on Sdate. After that, retrieve all invalid records where previous date is greater than sample sdate.

I am not getting proper result. Lag function is not working when PID got changed.

I have some done error in the programming. can anybody help me.

data hospital;

input patno serial lab $ Sdate :date9. visit;

format sdate date9.;

cards;

1001 1 HCG 25JAN2011 1

1001 2 HCG 20JAN2011 2

1001 3 HCG . 5

1001 2 SBP 24MAR2011 2

1001 1 SBP 20MAR2011 2

1002 1 SBP . 2

1002 3 SBP 19FEB2012 8

1002 2 SBP 16FEB2012 4

1003 1 DBP 25MAR2011 4

1003 2 DBP . 5

1003 3 DBP 15MAR2011 6

1003 4 DBP . 8

1004 1 CPK 12MAR2011 1

1004 2 CPK 15MAR2011 4

1005 1 LDL 18JUN2012 1

1005 2 LDL 14JUN2012 2

1005 3 LDL . 3

1005 4 LDL . 4

1005 5 LDL 16JUN2012 5

1005 6 LDL 17JUN2012 6

;

run;

proc sort data=hospital; by patno lab serial;run;

data report;

set hospital(keep=patno serial lab  Sdate);

by patno lab serial;

prev_date=lag(sdate);

format prev_date date9.;

run;

data error;

set report;

where prev_date is not missing and sdate is not missing;

if prev_date >sdate;

run;

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

OK, I can see clearer now.

data report;

set hospital (keep=patno serial lab  Sdate);

by patno lab serial;

retain prev_date;

format prev_date date9.;

if first.lab then prev_date = .;

output;

if sdate ne . then prev_date = sdate;

run;

Now prev_date should always be the last non-missing sdate within the "lab" group.

View solution in original post

8 REPLIES 8
Kurt_Bremser
Super User

data report;

set hospital(keep=patno serial lab  Sdate);

by patno lab serial;

prev_date=lag(sdate);

if first.lab then prev_date = .; * or first.patno?;

format prev_date date9.;

run;

Abraham
Obsidian | Level 7

Thanks Kurt for your help. But still i am not getting correct output.

For the pid (1003), lab sample is DBP with first collection of sdate as 25MAR2011 and last collection of sdate as 15MAR2011 .

Therefore previous record of 15MAR2011 is 25MAR2011

When I use the logic of "if prev_date >sdate;", I could not capture this invalid record.

Could you please help once or any other programming approach to consider


patno serial   lab       Sdate

1003 1        DBP       25MAR2011

1003 2        DBP           .

1003 3        DBP       15MAR2011

1003 4        DBP             .

Kurt_Bremser
Super User

OK, I can see clearer now.

data report;

set hospital (keep=patno serial lab  Sdate);

by patno lab serial;

retain prev_date;

format prev_date date9.;

if first.lab then prev_date = .;

output;

if sdate ne . then prev_date = sdate;

run;

Now prev_date should always be the last non-missing sdate within the "lab" group.

Abraham
Obsidian | Level 7

Thank you very much for providing nice logic. Its working fine for all records except below scenario.

Suppose I change the dataset slightly by adding one more record (1001 4 HCG 24JAN2011 7). In that cases the program is not capturing last record.

The last record should capture because previous occurrence of first lab test date is 25JAN2011 and last occurence is 24JAN2011.

As 25JAN2011 >24JAN2011, the record with serial no '4' satisfy the condition.

May I request you to check once.

1001 1 HCG 25JAN2011 1

1001 2 HCG 20JAN2011 2

1001 3 HCG . 5

1001 4 HCG 24JAN2011 7

data hospital;

input patno serial lab $ Sdate :date9. visit;

format sdate date9.;

cards;

1001 1 HCG 25JAN2011 1

1001 2 HCG 20JAN2011 2

1001 3 HCG . 5

1001 4 HCG 24JAN2011 7

1001 2 SBP 24MAR2011 2

1001 1 SBP 20MAR2011 2

1002 1 SBP . 2

1002 3 SBP 12FEB2012 8

1002 2 SBP 16FEB2012 4

1003 1 DBP 25MAR2011 4

1003 2 DBP . 5

1003 3 DBP 15MAR2011 6

1003 4 DBP . 8

1004 1 CPK 12MAR2011 1

1004 2 CPK 15MAR2011 4

1005 1 LDL 18JUN2012 1

1005 2 LDL 14JUN2012 2

1005 3 LDL . 3

1005 4 LDL . 4

1005 5 LDL 16JUN2012 5

1005 6 LDL 17JUN2012 6

;

run;

proc sort data=hospital; by patno lab serial;run;

data report;

set hospital (keep=patno serial lab  Sdate);

by patno lab serial;

retain prev_date;

format prev_date date9.;

if first.lab then prev_date = .;

output;

if sdate ne . then prev_date = sdate;

run;

data error;

set report;

where prev_date is not missing and sdate is not missing;

if prev_date >sdate;

run;

Output

patnoseriallabSdateprev_date
10012HCG20-Jan-1125-Jan-11
10023SBP12-Feb-1216-Feb-12
10033DBP15-Mar-1125-Mar-11
10052LDL14-Jun-1218-Jun-12
10014HCG24-JAN-11 20-JAN-11
10055LDL16-JUN-1218-Jun-12
10056LDL17-JUN-1218-Jun-12
Abraham
Obsidian | Level 7

Yes, I think I need to use the retain statement several times.

How can I proceed.

Kurt_Bremser
Super User

Try this:

data report;

set hospital (keep=patno serial lab  Sdate);

by patno lab serial;

retain prev_date;

format prev_date date9.;

if first.lab then prev_date = .;

output;

if sdate ne . and sdate > prev_date then prev_date = sdate;

run;

Patrick
Opal | Level 21

Just for clarification: Which of these 4 records would you like to be marked as "invalid"?

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 8 replies
  • 1072 views
  • 0 likes
  • 3 in conversation