topic Re: Non Missing Obs from multiple records per patients in SAS Procedures

Non Missing Obs from multiple records per patients

Almighty — Thu, 13 Feb 2014 15:45:46 GMT

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

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

DATA NEEDED:

SUBJID AETERM AESTDT

487	DIARRHEA	15-Aug-12
496	DIARRHEA	2-Nov-10
516	DIARRHEA	19-Apr-12
517	DIARRHEA	15-Mar-13
552	DIARRHEA	15-Apr-11
612	DIARRHEA	15-Dec-12
642	DIARRHEA	4-Jul-11
643	DIARRHEA	2-Mar-11
645	DIARRHEA	17-Jun-11
721	DIARRHEA	24-Nov-11
724	DIARRHEA	15-Dec-12

Re: Non Missing Obs from multiple records per patients

arun_guru — Thu, 13 Feb 2014 16:11:28 GMT

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 17JUN2011

721 DIARRHEA 24NOV2011

724 DIARRHEA 15DEC2012

;

run;

proc sql;

create table test1 as

select *

from test

where aestdt<>.;

quit;

This should work.

Re: Non Missing Obs from multiple records per patients

blackpebble — Thu, 13 Feb 2014 16:23:59 GMT

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.

Re: Non Missing Obs from multiple records per patients

Almighty — Thu, 13 Feb 2014 16:55:50 GMT

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

487	DIARRHEA
487	DIARRHEA	15AUG2012
487	DIARRHEA	26AUG2012
496	DIARRHEA	02NOV2010
516	DIARRHEA
516	DIARRHEA	19APR2012
517	DIARRHEA
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

DATA NEEDED:

SUBJID AETERM AESTDT

487	DIARRHEA	15-Aug-12
496	DIARRHEA	2-Nov-10
516	DIARRHEA	19-Apr-12
517	DIARRHEA
552	DIARRHEA	15-Apr-11
612	DIARRHEA	15-Dec-12
642	DIARRHEA	4-Jul-11
643	DIARRHEA	2-Mar-11
645	DIARRHEA	17-Jun-11
721	DIARRHEA	24-Nov-11
724	DIARRHEA	15-Dec-12

Re: Non Missing Obs from multiple records per patients

priyavarshney — Thu, 13 Feb 2014 17:48:35 GMT

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

Re: Non Missing Obs from multiple records per patients

Almighty — Thu, 13 Feb 2014 18:25:47 GMT

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

Re: Non Missing Obs from multiple records per patients

priyavarshney — Thu, 13 Feb 2014 18:29:27 GMT

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 .

Re: Non Missing Obs from multiple records per patients

priyavarshney — Fri, 14 Feb 2014 05:59:52 GMT

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

Re: Non Missing Obs from multiple records per patients

Jagadishkatam — Fri, 14 Feb 2014 07:08:58 GMT

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

Re: Non Missing Obs from multiple records per patients

HWSteinberg — Fri, 14 Feb 2014 09:47:21 GMT

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 = _:) ... "_:" stands for all variables beginning with "_" which here is _type_ and _freq_

Re: Non Missing Obs from multiple records per patients

HWSteinberg — Fri, 14 Feb 2014 10:10:12 GMT

Hi Jag,

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

Re: Non Missing Obs from multiple records per patients

Almighty — Fri, 14 Feb 2014 14:11:47 GMT

Hi HWSteinberg,

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

Best

Re: Non Missing Obs from multiple records per patients

priyavarshney — Fri, 14 Feb 2014 16:03:59 GMT

This is too good

Re: Non Missing Obs from multiple records per patients

shresthaarjun — Mon, 17 Feb 2014 05:32:09 GMT

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;