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

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
1 ACCEPTED SOLUTION

Accepted Solutions
HWSteinberg
Calcite | Level 5

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_

View solution in original post

13 REPLIES 13
arun_guru
Calcite | Level 5

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.

blackpebble
Calcite | Level 5

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.

Almighty
Fluorite | Level 6

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
priyavarshney
Calcite | Level 5

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

Almighty
Fluorite | Level 6

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

Capture.PNG

priyavarshney
Calcite | Level 5

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

HWSteinberg
Calcite | Level 5

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_

Almighty
Fluorite | Level 6

Hi HWSteinberg,

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

Best

Jagadishkatam
Amethyst | Level 16

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
HWSteinberg
Calcite | Level 5

Hi Jag,

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

priyavarshney
Calcite | Level 5

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 .

shresthaarjun
Calcite | Level 5

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;

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
  • 13 replies
  • 1646 views
  • 4 likes
  • 7 in conversation