I need to find who has in order A-B-C. Please check the table for example;
id term grade subj num
10 2002 D 332 1
10 2002 A 333 2
11 2005 C 232 1
11 2005 A 232 2
11 2005 B 232 3
11 2005 C 232 4
15 2010 A 130 1
15 2010 B 130 2
15 2010 C 130 3
What i need fromthis table is id : 11 AND 15
The output should be like
id term subj
11 2005 232
15 2010 130
So i need list the id's that had Grade of 'A' in it then was changed to 'B' then it was changed to 'C' .
Num could be in order. It dosen't have to start from 1, it could be 1 or 2 or 3, etc. But it should be in order A then B then C
proc sql;
select distinct id, term, subj from have
group by id
having count(distinct grade)=3;
quit;
@LinusH: True, If order matters then data step would be a better option.
proc sort data=have;
by id grade;
run;
data want(keep=id term subj);
set have;
by id grade;
if first.id then cnt=0;
cnt+1;
if cnt=3;
run;
Not tested as tets data not in datastep form, but somethign like:
data want (drop=res grade); set have; by id; length res $10; if first.id then res=""; res=cats(res,grade); if last.id and res in ("ABC","ACB","BAC","BCA","CAB","CBA") then output; run;
Or another option:
proc sql; create table WANT as select distinct A.* from HAVE A where exists(select distinct ID from HAVE where ID=A.ID and GRADE="A") and exists(select distinct ID from HAVE where ID=A.ID and GRADE="B") and exists(select distinct ID from HAVE where ID=A.ID and GRADE="C"); quit;
Here is another option, concatenate all the grade within same ID following the data order, then check 'ABC':
data have;
input id term grade$ subj;
cards;
10 2002 D 332
10 2002 A 333
11 2005 A 232
11 2005 B 232
11 2005 C 232
15 2010 A 130
15 2010 B 130
;
data want;
length _cat $ 100;
do until (last.term);
set have;
by id term notsorted;
_cat=cats(_cat,grade);
end;
if find(_cat,'ABC') then
output;
run;
That code doesn't work for same term
It does now.
it works for the table i send example, but in my bug table gives me nothing in this code.
Assuming the "want" data is where the ID is in sequence AND the sequence of grades is exactly as proposed, A, B and C in order this may work for you:
data want;
set have;
if id=lag(id) and Id=Lag2(id) and grade='C' and lag(grade)='B' and Lag2(grade)='A';
drop grade;
run;
I don;t know the Lag means but with this way i got nothing. Actually i make it better, i renewed my example table . It should be same id same term an same subj, but it should be in order grade.
Please show the exact code you ran.
The example I provided works with both sets of example data you have provided.
So there may be something else going on in your data that you are not providing to us.
LAG and the related LAGn functions allow you to examine previous values of your data. Lag (or Lag1) looks at the value of the variable in the previous record, Lag2 in the record before that.
I need to find who has in order A-B-C. Please check the table for example;
id term grade subj num
10 2002 D 332 1
10 2002 A 333 2
11 2005 C 232 1
11 2005 A 232 2
11 2005 B 232 3
11 2005 C 232 4
15 2010 A 130 1
15 2010 B 130 2
15 2010 C 130 3
What i need fromthis table is id : 11 AND 15
The output should be like
id term subj
11 2005 232
15 2010 130
So i need list the id's that had Grade of 'A' in it then was changed to 'B' then it was changed to 'C' .
Num could be in order. It dosen't have to start from 1, it could be 1 or 2 or 3, etc. But it should be in order A then B then C
Two questions ...
It looks like SUBJ should be important here. Must all grades be for the same SUBJ as well as for the same ID?
Would this pattern be one that you want: A B B C
(Or perhaps it's not a possible pattern because "B" can't change to "B".)
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 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.