topic Re: Find in order variables in SAS Programming

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

lerdem — Thu, 05 May 2016 17:40:58 GMT

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

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

stat_sas — Thu, 05 May 2016 14:14:25 GMT

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

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

LinusH — Thu, 05 May 2016 14:15:38 GMT

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.

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

LinusH — Thu, 05 May 2016 14:18:59 GMT

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

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

RW9 — Thu, 05 May 2016 14:22:59 GMT

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;

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

stat_sas — Thu, 05 May 2016 14:27:59 GMT

@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;

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

Haikuo — Thu, 05 May 2016 15:37:20 GMT

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;

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

lerdem — Thu, 05 May 2016 15:34:49 GMT

That code doesn't work for same term

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

Haikuo — Thu, 05 May 2016 15:38:02 GMT

It does now.

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

lerdem — Thu, 05 May 2016 15:46:06 GMT

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

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

ballardw — Thu, 05 May 2016 15:53:52 GMT

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;

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

lerdem — Thu, 05 May 2016 17:43:24 GMT

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.

Find in order variables

lerdem — Thu, 05 May 2016 18:23:28 GMT

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

Re: Find in order variables

Astounding — Thu, 05 May 2016 18:47:04 GMT

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

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

ballardw — Thu, 05 May 2016 18:57:26 GMT

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.

Re: Find in order variables

FreelanceReinh — Thu, 05 May 2016 19:27:30 GMT

Third question: Isn't this the same question as you asked in the other thread?

Re: Find in order variables

RW9 — Fri, 06 May 2016 08:37:24 GMT

Please don't post the same question multiple times. To get a good response to your question post: test data, in the form of a datastep that covers your actual data and any differences - we can only work with what you give us. Also post, what you want the output to look like, then any additional logic which may help. Looking at your latest post, my post originally that was ignored should give you the result you want:

data have;
  input id term grade $ subj num;
  datalines;
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 
;
run;
data want (keep=id term subj);
  length res $10;
  set have;
  retain res;
  by id term;
  if first.term then res="";
  res=cats(res,grade);
  if last.term and index(res,"ABC")>0 then output;
run;