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?
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.
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~
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?
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..
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.
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;
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: