topic Re: Taking the earliest observation (by date) that fulfills a certain condition in SAS Enterprise Guide

Taking the earliest observation (by date) that fulfills a certain condition

misaochan — Fri, 09 May 2014 07:33:16 GMT

I have a list of IDs with their respective measurement values and dates at which the measurement was taken (some IDs have multiple measurements). I then compute a new column ('Controlled') that is 1 when the value fulfills a condition (say, <50) and 0 otherwise.

Now, what I want to do is to take only the earliest observation for each ID that fulfills a condition. Basically for each ID, if the value of the 'Controlled' column is 1, and there is more than 1 measurement that fits, I want to select the earliest observation (by date).

Is there a way to do this in SAS EG via point and click? Or will I need to write a mini-program for it?

Re: Taking the earliest observation (by date) that fulfills a certain condition

SKK — Fri, 09 May 2014 08:52:52 GMT

Hi in EG you can use the query builder to select and filter the data or u can try this code:

Proc sql;

Create table want as

select id, measurement, min(dates), controlled from have

where controlled=1

group by id

having count(*) gt 1;

quit;

~Untested~

Re: Taking the earliest observation (by date) that fulfills a certain condition

misaochan — Fri, 09 May 2014 09:17:58 GMT

Thanks for the response! If I use the code, how do I 'connect' it to my dataset in EG? Create new program, then type that code in and link it to my dataset?

Re: Taking the earliest observation (by date) that fulfills a certain condition

SKK — Fri, 09 May 2014 09:29:14 GMT

I hope u have ur dataset in EG library... Just upade ur dataset name in from clause and substitute all the variable... that should do it..

Re: Taking the earliest observation (by date) that fulfills a certain condition

TomKari — Fri, 09 May 2014 14:06:09 GMT

I don't think that SK's code will work exactly right.

I can't see a way to do it with Query Builder in one step, but you can do it in two.

First, select ID and DATE; Add a filter of CONTROLLED = 1; For the Summary column, use a MIN on DATE; and ID should be automatically select as the grouping column

When you run this, it will return the ID/DATE combination of the earliest date for each ID, but you won't have a MEASUREMENT value.

Now create a new Query Builder task on this result; add your original dataset, and join on ID and DATE. The results should be what you want.

Re: Taking the earliest observation (by date) that fulfills a certain condition

stat_sas — Fri, 09 May 2014 17:09:28 GMT

Here is my try based on the mentioned requirements:

data have;

input id date date9. measurement;

format date date9.;

datalines;

1 12-Aug-97 92

1 12-Aug-97 22

1 13-Aug-97 34

1 14-Aug-97 80

1 14-Aug-97 43

1 15-Aug-97 11

1 16-Aug-97 49

1 16-Aug-97 35

2 14-Aug-97 76

2 14-Aug-97 55

2 15-Aug-97 43

2 15-Aug-97 65

2 15-Aug-97 91

2 15-Aug-97 10

2 15-Aug-97 86

2 15-Aug-97 15

2 16-Aug-97 36

2 17-Aug-97 43

2 19-Aug-97 92

;

run;

proc sql;

select id,date,min(measurement) as measurement,

case when measurement<50 then 1 ELSE 0 END as controlled

from have

where calculated controlled=1

group by id,date,calculated controlled;

quit;

Re: Taking the earliest observation (by date) that fulfills a certain condition

misaochan — Sun, 11 May 2014 13:42:26 GMT

Thanks for the responses all. Unfortunately there is another issue that I need to solve before I try this, so I will have to work on that first (actually, am posting a thread about it too...)

I will come back and try this once I've solved that problem and have my dataset prepared properly.

Re: Taking the earliest observation (by date) that fulfills a certain condition

Vish33 — Mon, 12 May 2014 12:04:04 GMT

Hi,

Sort the data by ID, and date in ascending order and apply first.ID in data step.(after calculating the variable) You will get the required output.

*creating the Controlled variable;

proc sql;

create table yourdata as select *,(case when measurement < 50 then 1 else 0 end) as controlled

from yourdata

having calculated controlled =1;

quit;

*Sorting by ID and date (by default in ascending);

proc sort data=yourdata;

by ID date ;

run;

* Using First.ID to get the measurements on earliest date for each id;

data youwant;

set yourdata;

by ID date;

if first.ID ;

run;