topic Re: Select unique obs when a variable first meets a condition and is less than other condition. in SAS Programming

Select unique obs when a variable first meets a condition and is less than other condition.

rtniblett15 — Wed, 17 Jan 2024 19:51:06 GMT

Hello all.

I am running a pig study and am trying to get SAS to output a dataset which requires the following:

the first time each animal reaches 136 kg before 200 d of age. I could just go with the 200 d mark, however, with weight fluctuation, an animal may meet the criteria x number of days before. I have attached a datastep of the data with each of the codes that I have tried. I have tried using the data step, proc sql, and proc summary, to no avail. I can get a list of all the times each animal (giltid) reaches the critera(avgwtkg>=136 and ageontest<=200), but I want something similar to the first. utilized in the data step.

I am looking for results which include all variables in the main set, but the key info would be:

Giltid:223307 avgwtkg:136.321 ageontest:193

Giltid:223308 avgwtkg:136.02 ageontest: 188

and so on...

data esfe2200dwt;
set esfe2visitestrus;
by giltid avgwtkg;
first.avgwtkg=avgwtkg;
if (ageontest<=200) and (first.avgwtkg>=136) then output;
run;
proc sql ;
select distinct(giltid) as giltid, avgwtkg, ageontest
from esfe2visitestrus
distinct where avgwtkg>=136 and ageontest<=200;
proc summary data=esfe2visitestrus nway;
class giltid feedlevel pg600 entrydate;
where avgwtkg>=136 and ageontest<=200;
id _all_;
output out=test200dwt ;
run;

Thank you for your time and consideration.

Re: Select unique obs when a variable first meets a condition and is less than other condition.

Patrick — Thu, 18 Jan 2024 00:13:52 GMT

Below should work for you

/* option 1 */
proc sort data=esfe2visitestrus out=inter;
  by giltid ageontest avgwtkg;
run; 
data want_1;
  set inter;
  by giltid ageontest avgwtkg;
  retain selected_flg;
  if first.giltid then selected_flg=0;
  if selected_flg=0 and avgwtkg>=136 then
    do;
      output;
      selected_flg=1;
    end;
  drop selected_flg;
run;

/* option 2 */
proc sql;
  create table want_2 as
  select *
  from esfe2visitestrus
  where avgwtkg>=136
  group by giltid
  having min(ageontest)=ageontest
  ;
quit;

Re: Select unique obs when a variable first meets a condition and is less than other condition.

rtniblett15 — Mon, 22 Jan 2024 16:44:59 GMT

Thank you! Option 1 has one obs that is over 200 d of age. Option 2 still has every gilt obs with a weight over 136 kg like before.