Help using Base SAS procedures

Non Missing Obs from multiple records per patients

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

Non Missing Obs from multiple records per patients

Hi

I am trying to get the non missing min date record if first.subjid date is missing:  Can gurus help.

Thanks

Data have

subjid               AETERM                              AESTDT   

487DIARRHEA
487DIARRHEA15AUG2012
487DIARRHEA26AUG2012
496DIARRHEA02NOV2010
516DIARRHEA
516DIARRHEA19APR2012
517DIARRHEA15MAR2013
552DIARRHEA15APR2011
612DIARRHEA15DEC2012
642DIARRHEA04JUL2011
642DIARRHEA07JAN2013
643DIARRHEA02MAR2011
645DIARRHEA
645DIARRHEA
645DIARRHEA17JUN2011
721DIARRHEA24NOV2011
724DIARRHEA15DEC2012

DATA NEEDED:

SUBJID          AETERM               AESTDT    

487DIARRHEA15-Aug-12
496DIARRHEA2-Nov-10
516DIARRHEA19-Apr-12
517DIARRHEA15-Mar-13
552DIARRHEA15-Apr-11
612DIARRHEA15-Dec-12
642DIARRHEA4-Jul-11
643DIARRHEA2-Mar-11
645DIARRHEA17-Jun-11
721DIARRHEA24-Nov-11
724DIARRHEA15-Dec-12

Accepted Solutions
Solution
‎02-14-2014 04:47 AM
Occasional Contributor
Posts: 15

Re: Non Missing Obs from multiple records per patients

I think the simplest way is to use proc summary. I have 3 proposals:

1.) proc summary data = have nway; class subjid; var aestdt; output out = want1 min =; run;     * AETERM lost, 1 record per patient;

2.) proc summary data = have nway; class subjid; id AETERM; var aestdt; output out = want2 min =; run;     * maximum AETERM (latest in alphabet)kept, 1 record per patient;

3.) proc summary data = have nway; class subjid AETERM; var aestdt; output out = want3 min =; run;     * 1 record per patient and AETERM;

you may want to remove additional variables that proc summary creates, then use ... out = want1 (drop = _Smiley Happy ... "_:" stands for all variables beginning with "_" which here is _type_ and _freq_

View solution in original post


All Replies
N/A
Posts: 1

Re: Non Missing Obs from multiple records per patients

data test;

    input subjid AETERM $ AESTDT date9.;

    datalines;

subjid               AETERM                              AESTDT  

487    DIARRHEA   

487    DIARRHEA    15AUG2012

487    DIARRHEA    26AUG2012

496    DIARRHEA    02NOV2010

516    DIARRHEA   

516    DIARRHEA    19APR2012

517    DIARRHEA    15MAR2013

552    DIARRHEA    15APR2011

612    DIARRHEA    15DEC2012

642    DIARRHEA    04JUL2011

642    DIARRHEA    07JAN2013

643    DIARRHEA    02MAR2011

645    DIARRHEA   

645    DIARRHEA   

645    DIARRHEA    17JUN2011

721    DIARRHEA    24NOV2011

724    DIARRHEA    15DEC2012

;

run;

proc sql;

create table test1 as

select *

from test

where aestdt<>.;

quit;

This should work.

New Contributor
Posts: 3

Re: Non Missing Obs from multiple records per patients

Delete all obs that have a missing date. Then:

proc sort data=a; by subjid aestdt; run;

data b;

set a;

by subjid;

if first.subjid;

run;

This will give you the first non-missing date per subject.

Contributor
Posts: 34

Re: Non Missing Obs from multiple records per patients

Posted in reply to blackpebble

Thanks blakpebble;

i need to keep ( first.subjid and last.subjid) even if aestdt is is missing (ex: plz see subjid: 517 below)

subjid               AETERM                              AESTDT  

487DIARRHEA
487DIARRHEA15AUG2012
487DIARRHEA26AUG2012
496DIARRHEA02NOV2010
516DIARRHEA
516DIARRHEA19APR2012
517DIARRHEA
552DIARRHEA15APR2011
612DIARRHEA15DEC2012
642DIARRHEA04JUL2011
642DIARRHEA07JAN2013
643DIARRHEA02MAR2011
645DIARRHEA
645DIARRHEA
645DIARRHEA17JUN2011
721DIARRHEA24NOV2011
724DIARRHEA15DEC2012

DATA NEEDED:

SUBJID          AETERM               AESTDT   

487DIARRHEA15-Aug-12
496DIARRHEA2-Nov-10
516DIARRHEA19-Apr-12
517DIARRHEA
552DIARRHEA15-Apr-11
612DIARRHEA15-Dec-12
642DIARRHEA4-Jul-11
643DIARRHEA2-Mar-11
645DIARRHEA17-Jun-11
721DIARRHEA24-Nov-11
724DIARRHEA15-Dec-12
New Contributor
Posts: 4

Re: Non Missing Obs from multiple records per patients

proc sort data=test; by subjid   aestdt; run;

data test1;

retain t;

set test;

by subjid;

if t=1 and aestdt ^="" then do;  output;  t=0; end;

else

if first.subjid and last.subjid   then   output;

else

if first.subjid and aestdt ^="" then output;

else  t=1;

format aestdt date9.;

run;

try it

Contributor
Posts: 34

Re: Non Missing Obs from multiple records per patients

Posted in reply to priyavarshney

It worked partially, but I do see multiple records per subect as shown in the snapshot

Capture.PNG

New Contributor
Posts: 4

Re: Non Missing Obs from multiple records per patients

I think It should to have do . please send raw data file and desire output

Solution
‎02-14-2014 04:47 AM
Occasional Contributor
Posts: 15

Re: Non Missing Obs from multiple records per patients

I think the simplest way is to use proc summary. I have 3 proposals:

1.) proc summary data = have nway; class subjid; var aestdt; output out = want1 min =; run;     * AETERM lost, 1 record per patient;

2.) proc summary data = have nway; class subjid; id AETERM; var aestdt; output out = want2 min =; run;     * maximum AETERM (latest in alphabet)kept, 1 record per patient;

3.) proc summary data = have nway; class subjid AETERM; var aestdt; output out = want3 min =; run;     * 1 record per patient and AETERM;

you may want to remove additional variables that proc summary creates, then use ... out = want1 (drop = _Smiley Happy ... "_:" stands for all variables beginning with "_" which here is _type_ and _freq_

Contributor
Posts: 34

Re: Non Missing Obs from multiple records per patients

Posted in reply to HWSteinberg

Hi HWSteinberg,

Great, it worked!  Thanks for your time....

Best

New Contributor
Posts: 4

Re: Non Missing Obs from multiple records per patients

Posted in reply to HWSteinberg

This is too good

Trusted Advisor
Posts: 1,137

Re: Non Missing Obs from multiple records per patients

Please try the below code

proc sort data=have;

  by subjid AETERM aestdt;

run;

data want;

  set have;

  by subjid AETERM aestdt;

  if first.subjid=0 and last.subjid=0 and aestdt=. then delete;

run;

This will delete the records which have aestdt=. and it is not first.subjid and last.subjid.

Thanks,

Jag

Thanks,
Jag
Occasional Contributor
Posts: 15

Re: Non Missing Obs from multiple records per patients

Posted in reply to Jagadishkatam

Hi Jag,

this will delete also a patient completely which has more than 1 record but all with missing aestdt.

New Contributor
Posts: 4

Re: Non Missing Obs from multiple records per patients

using a flag variable t default 0

if t=1 and date is not missing then output and set t=0(default)

else if have single record then output

else if first record and date have non missing value then output

other wise set t=1

those records would be  in output which are first nonmissing value of date

there are two case

1. A single record then output same

2. if more same dup record

   nonmissing of date then output otherwise goto next record

goto next record repeated until not found .

New User
Posts: 1

Re: Non Missing Obs from multiple records per patients

proc sql;

create table x as

select  subjid, aeterm, min(aestdt) as mindate format=date9. from test

group by subjid

having aestdt = min(aestdt);

quit;

proc print;

run;

🔒 This topic is solved and locked.

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

Discussion stats
  • 13 replies
  • 430 views
  • 4 likes
  • 7 in conversation