Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

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

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?


Accepted Solutions
Solution
‎05-09-2014 10:06 AM
PROC Star
Posts: 1,258

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

Posted in reply to misaochan

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.

View solution in original post


All Replies
Contributor SKK
Contributor
Posts: 35

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

Posted in reply to misaochan

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~

Occasional Contributor
Posts: 18

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

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?

Contributor SKK
Contributor
Posts: 35

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

Posted in reply to misaochan

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..

Solution
‎05-09-2014 10:06 AM
PROC Star
Posts: 1,258

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

Posted in reply to misaochan

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.

Trusted Advisor
Posts: 1,270

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

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;

Occasional Contributor
Posts: 18

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

Posted in reply to misaochan

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.

Frequent Contributor
Posts: 142

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

Posted in reply to misaochan

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;

🔒 This topic is solved and locked.

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

Discussion stats
  • 7 replies
  • 2375 views
  • 6 likes
  • 5 in conversation