SAS grouping code help

[lulumohca]

Hi,

I am having trouble writing a code for this, say I have the following data:

product type
a 1
a 2
a 3
b 1
c 1
c 2
d 3
e 1
e 2
e 3

I want to make 3 data sets, 1st one only has products which has all 3 type (1, 2, 3) so that dataset will have product a and e.

2nd dataset is just products with 2 types (1 and 2) so it will have product c in it.

then last data set just has product with 1 type (product b and d)

or is there a way to add a column which gives each product row a value say z if it has 3 types and x if it has 2 types.

either way it will help me very much.

Thanks !!!!

 

[ChrisW75]

I think By Group processing is the way to go here.

proc sort data=mydset nodupkey;
  by product type;
run;

data dset1(drop=type) 
     dset2(drop=type) 
     dset3(drop=type);
  set mydset;
  by product;

  length type1-type3 3;

  if first.product then call missing(type1,type2,type3);
  if type = 1 then type1 = 1;
  else if type = 2 then type2 = 1;
  else if type = 3 then type3 = 1;

  if last.product then
  do;
    if sum(of type1-type3) = 3 then output dset3;
    else if sum(of type1-type3) = 2 then output dset2;
    else if sum(of type1-type3) = 1 then output dset1;
  end;
run;

That'll give you 3 datasets with lists of products with the number of types that they each have.

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

 

[lulumohca]

Thanks for the help!

Now I have data that is like:

product type count
a 1 3
a 2 3
a 3 3
b 1 2
b 2 2
c 2 2
c 3 2
d 3 1


What I would like is to group these so I know what kind they have so I want output data to be:

product type count kind
a 1 3 all3
a 2 3 all3
a 3 3 all3
b 1 2 oneAndtwo
b 2 2 oneAndtwo
c 2 2 twoAndthree
c 3 2 twoAndthree
d 3 1 oneonly



I am having alot of trouble to make something that will produce this. I thought about using arrays but cant figure out how to make it run through my data.

 

[ChrisW75]

Simple.
Use the code I wrote earlier.
Then in each dataset (dset1-dset3), create a new field which contains the flag you want to append to the original data, then join them back to the original data using either MERGE in a dataset, or Proc SQL. Merge is probably best in this situation as you can merge your original data and all 3 of the other datasets together in one go.

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

 

[lulumohca]

Hi,

I am still having trouble with this code, beacuase this part:

if last.product then
do;
if sum(of type1-type3) = 3 then output dset3;
else if sum(of type1-type3) = 2 then output dset2;
else if sum(of type1-type3) = 1 then output dset1;
end;
run;

Is only adding the last.product row.
Because you have

producta type1 1 . .
producta type2 . . 1

It only sums up the 2nd row so just give 1 back.


I used this code:


data count1 count2 count3;
set track1;
by product;

count + 1;
if first.product then count = 1;

if count = 1 then output count1;
if count = 2 then output count2;
if count >= 3 then output count3;

run;

data combine;
merge count1 count2 count3;
by product;
run;

data all;
merge track1 combine (keep=product count);
by product;
run;


But I am still having trouble differeniating between ones that have 2 types, it can have type1 and type2 or type1 and type3 or type2 and type3.

Thanks !!!!

 

[ChrisW75]

So sorry, it seems I missed a retain statement in my original code. Retain tells SAS to keep the values for these variables between records.

data dset1(drop=type) 
     dset2(drop=type) 
     dset3(drop=type);
  set mydset;
  by product;

  length type1-type3 3;
  retain type1-type3;

  if first.product then call missing(type1,type2,type3);
  if type = 1 then type1 = 1;
  else if type = 2 then type2 = 1;
  else if type = 3 then type3 = 1;

  if last.product then
  do;
    if sum(of type1-type3) = 3 then output dset3;
    else if sum(of type1-type3) = 2 then output dset2;
    else if sum(of type1-type3) = 1 then output dset1;
  end;
run;

data all; 
   merge mydset
         dset1(in=in1) 
         dset2(in=in2)
         dset3(in=in3); 
   by product ;

  if in1 then type_count = 1;
  else if in2 then type_count = 2;
  else if in3 then type_count = 3; 
run;

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