10-20-2015 09:53 PM
I have a scenario where i need to check the current values with the next value, I need to identify those subject whose values has 1 followed by 2 , if the subject have value 2 followed by 1 then i should exclude them.
please consider the sample data like below
data have; input subjid value; cards; 101 1 101 . 101 1 101 2 101 1 102 1 102 2 102 . 102 1 103 2 103 1 103 . 103 1 ; proc sort data=have; by subjid ; run; data want; set have; by subjid ; retain first ; if first.subjid then do;first=.;end; if value ne . then first=value; run; data want2; obs=_n_+1; set want end=eof; by subjid; if nobs>=obs then set want(keep=first rename=(first=nextfirst)) point=obs nobs=nobs; run; data want2; set want2; by subjid; if not last.subjid and first=1 and nextfirst=2 then output; run;
I wrote the above code which gets me the expected output, however i am looking for a better approach.
Appreciate your responses.
10-20-2015 11:36 PM
Try the LAG() function as shown below:
if value = 2 and lag1(value) = 1 then output;
This writes 3 records to the output.
Is this what you are looking for ?
10-21-2015 12:32 AM
Yes, thank you so much for the thought.
This works but here is the code which gives me the expected output. I need to get 101 and 102 subjects as these are the only subjects with value variable 1 followed by 2.
proc sort data=have; by subjid ; run; data want; set have; by subjid ; retain first ; if first.subjid then do;first=.;end; if value ne . then first=value; if last.subjid then first=.; if first=2 and lag(first)=1; run;
10-21-2015 05:30 AM
proc sql; create table WANT as select distinct SUBJID from HAVE A where exists (select SUBJID from HAVE where SUBJID=A.SUBJID and VALUE=1) and exists (select SUBJID from HAVE where SUBJID=A.SUBJID and VALUE=2); quit;
As there is no logical sequence in the data, I can only say there is a 1 and a 2. If there is other data such as datetime, length you can easily pull that up as min(datetime) where > A.datetime, but I am just guessing here as the test data doesn't show it.
10-21-2015 08:58 AM
One thing that's not clear about your question ... if you are keeping a SUBJID, which observations do you want to keep? I'll assume that you want to keep all of them. Here's one approach:
do until (last.subjid);
if value=1 then found_1='Y';
if value=2 and found_1='Y' then keepme='Y';
do until (last.subjid);
if keepme='Y' then output;
drop found_1 keepme;