Subset of Data

[sbman79]

Hi, I am new to SAS so maybe this is easy...but I don't think it is.
It is difficult to explain what I want so I will provide a model:

Cat1 Cat2 Cat3
1 6 1
1 7 2
1 3 4
2 6 3
2 5 5
3 6 4
3 2 5
3 7 6
3 4 2


Ok, I want to look at rows where the CAT 1 are equal, and
given a CAT1 number if there is a CAT 2 number equal to 6
AND a CAT2 number equal to 7 or 2 so long as the absolute value of the difference between the CAT3 number of the row with CAT2=6 and CAT2=7 or 2 is equal to 1.

OK so in this case, the following would be output:

Cat1 Cat2 Cat3
1 6 1
1 7 2
3 6 4
3 2 5

Um, Makes any sense??
Anyway. If you can help at all, I would be grateful!

 

[ChrisW75]

Hiya, I can think of maybe a couple of ways to do this, but I think that the simplest is this.
Step 1 - Select just the records that meet the individual criteria into 2 different datasets. The first is for the first criteria (CAT2 = 6) the second for the second criteria (CAT2 in(2,7)).
Step 2 - Use proc sql to select records from each file where CAT1 exists in the other file.
Step 3 - append one file to the other.

Here it is in code, might need a little tweaking, this is untested.

data crit1a
     crit2a;
   set dset1;

  if cat2 = 6 then output crit1a;
  else if cat2 in(2,7) then output crit2a;
run;

proc sql;
  create table crit1b as
  select CAT1
        ,CAT2
        ,CAT3
  from crit1a where CAT1 in(select distinct(CAT1)
                           from crit2a
                          )
  order by CAT1
          ,CAT2
  ;

  create table crit2b as
  select CAT1
        ,CAT2
        ,CAT3
  from crit2a where CAT1 in(select distinct(CAT1)
                            from crit2a
                          )
  order by CAT1
          ,CAT2
  ;
quit;

data final;
  set crit1b
      crit2b;
  by CAT1;
run;

That should hopefully give you exactly what you want.


Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.