Quartz | Level 8

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

I need to find who has in order A-B-C. Please check the table for example;

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
Ammonite | Level 13

## 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
quit;

Tourmaline | Level 20

## 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
Tourmaline | Level 20

## 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
Ammonite | Level 13

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

proc sort data=have;
run;

data want(keep=id term subj);
set have;
if first.id then cnt=0;
cnt+1;
if cnt=3;
run;

Diamond | Level 26

## 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="";
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;
```
Onyx | Level 15

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

Here is another option, concatenate all the grade within same ID following the data order, then check 'ABC':

``````data have;
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;
end;

if find(_cat,'ABC') then
output;
run;``````
Quartz | Level 8

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

That code doesn't work for same term

Onyx | Level 15

It does now.

Quartz | Level 8

## 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

## 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;
run;``````
Quartz | Level 8

## 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

## 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.

Quartz | Level 8

## Find in order variables

I need to find who has in order A-B-C. Please check the table for example;

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 Star

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

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