topic Re: How to assign groups to vales in a variable in SAS Programming

How to assign groups to vales in a variable

meetagupta — Fri, 04 Oct 2019 19:58:28 GMT

If I have a SAS dataset with the following values. I want to create a new variable and assign value"A" if cust_id is there 2 times in the table and assign value "B" if cust_id is there 3 times. For ex. For cust_id=1113, I want new variable value to be A and for cust_id=1115, I want new variable value to be B.

Thanks.

cust_id month

1112 Mar

1113 apr

1113 feb

1114 feb

1115 mar

1115 apr

1115 feb

Re: How to assign groups to vales in a variable

PeterClemmensen — Fri, 04 Oct 2019 20:04:03 GMT

What if it's there 1 or 4 times?

Re: How to assign groups to vales in a variable

meetagupta — Fri, 04 Oct 2019 20:11:49 GMT

they can be set to missing..

Re: How to assign groups to vales in a variable

novinosrin — Fri, 04 Oct 2019 20:12:20 GMT

data have;
input cust_id $   month $;
cards;
1112       Mar
1113       apr
1113       feb
1114        feb
1115        mar
1115       apr
1115        feb
;

proc sql;
create table want as
select *,case when count(cust_id)=2 then 'A' 
when count(cust_id)=3 then 'B' else ' ' end as New_var
from have
group by cust_id;
quit;

Re: How to assign groups to vales in a variable

PeterClemmensen — Fri, 04 Oct 2019 20:27:29 GMT

Ok. Do something like this

data have;
input cust_id month $;
datalines;
1112 Mar
1113 apr
1113 feb
1114 feb
1115 mar
1115 apr
1115 feb
;

data want;
   do _N_=1 by 1 until (last.cust_id);
      set have;
      by cust_id;
   end;
   do until (last.cust_id);
      set have;
      by cust_id;
      if      _N_=2 then newValue='A';
      else if _N_=3 then newValue='B';
      output;
   end;
run;

Result:

cust_id month newValue 
1112    Mar     
1113    apr   A 
1113    feb   A 
1114    feb     
1115    mar   B 
1115    apr   B 
1115    feb   B