BookmarkSubscribeRSS Feed
lerdem
Quartz | Level 8

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

 

 

16 REPLIES 16
stat_sas
Ammonite | Level 13

proc sql;
select distinct id, term, subj from have
group by id
having count(distinct grade)=3;
quit;

LinusH
Tourmaline | Level 20
Data step, by Id, and use retain to keep track of the previous grade.
You probably need to use first. and last. logic as well.
Data never sleeps
LinusH
Tourmaline | Level 20
@stat_sas: that will give you students with three grades, but not necessarily in A-B-C order.
This problem is row order based, which suits a data step better, whereas SQL is column based and usually having more problems keeping track of row order.
Data never sleeps
stat_sas
Ammonite | Level 13

 

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;

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Haikuo
Onyx | Level 15

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;
lerdem
Quartz | Level 8

That code doesn't work for same term

lerdem
Quartz | Level 8

it works for the table i send example, but in my bug table gives me nothing in this code.

ballardw
Super User

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;
lerdem
Quartz | Level 8

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.

ballardw
Super User

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.

lerdem
Quartz | Level 8

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

 

Astounding
PROC Star

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 16 replies
  • 1818 views
  • 0 likes
  • 8 in conversation