DATA Step, Macro, Functions and more

'A' in it then was changed to 'B' then it was changed to 'C' .

Reply
Contributor
Posts: 47

'A' in it then was changed to 'B' then it was changed to 'C' .

[ Edited ]

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

 

 

Trusted Advisor
Posts: 1,231

Re: 'A' in it then was changed to 'B' then it was changed to 'C' .

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

Super User
Posts: 5,441

Re: 'A' in it then was changed to 'B' then it was changed to 'C' .

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
Super User
Posts: 5,441

Re: 'A' in it then was changed to 'B' then it was changed to 'C' .

@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
Trusted Advisor
Posts: 1,231

Re: 'A' in it then was changed to 'B' then it was changed to 'C' .

 

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;

 

Super User
Super User
Posts: 7,997

Re: 'A' in it then was changed to 'B' then it was changed to 'C' .

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;
Respected Advisor
Posts: 3,156

Re: 'A' in it then was changed to 'B' then it was changed to 'C' .

[ Edited ]

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;
Contributor
Posts: 47

Re: 'A' in it then was changed to 'B' then it was changed to 'C' .

That code doesn't work for same term

Respected Advisor
Posts: 3,156

Re: 'A' in it then was changed to 'B' then it was changed to 'C' .

It does now.

Contributor
Posts: 47

Re: 'A' in it then was changed to 'B' then it was changed to 'C' .

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

Super User
Posts: 11,343

Re: 'A' in it then was changed to 'B' then it was changed to 'C' .

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;
Contributor
Posts: 47

Re: 'A' in it then was changed to 'B' then it was changed to 'C' .

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.

Super User
Posts: 11,343

Re: 'A' in it then was changed to 'B' then it was changed to 'C' .

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.

Contributor
Posts: 47

Find in order variables

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

 

Super User
Posts: 5,518

Re: Find in order variables

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

 

Ask a Question
Discussion stats
  • 16 replies
  • 570 views
  • 0 likes
  • 8 in conversation