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
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;
What if it's there 1 or 4 times?
they can be set to missing..
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.