I have a following sample dataset and looking to check iteratively for each ID if the combinations on Var2 (eg: CC85 & CC86) or (cc54 & CC57) are present. Each ID can have one or more (than 5) observations. If those combinations are present then create a new variable called 'combo' and assign values like 'Hcc801' and 'Hcc501' respectively.
No combo value for a single ID or if the conditions are not satisfied then the combo = 'NA'.
Please note, there are many other combinations along with CC85 & CC86 or CC54 & CC57 that need to be incorporated.
data t1;
input ID Var1 $ Var2 $ Rank;
datalines;
1 N179 CC135 2
2 I214 CC85 1
2 A419 CC86 1
2 A500 CC100 3
3 A100 CC54 1
3 A101 CC57
;
run;
Thank you in advance for your help!
data t1;
input ID Var1 $ Var2 $ Rank;
datalines;
1 N179 CC135 2
2 I214 CC85 1
2 A419 CC86 1
2 A500 CC100 3
3 A100 CC54 1
3 A101 CC57 2
;
proc sql;
create table want as
select *,
case when sum(upcase(var2)='CC85') and sum(upcase(var2)='CC86') and upcase(var2) in ('CC85' 'CC86') then 'HCC801'
when sum(upcase(var2)='CC54') and sum(upcase(var2)='CC57') and upcase(var2) in ('CC54' 'CC57') then 'HCC501'
else 'NA'
end as Combo
from t1
group by id;
quit;
Please show us the desired output table.
output should look like below:
ID | Var1 | Var2 | Rank | Combo |
1 | N179 | CC135 | 2 | NA |
2 | I214 | CC85 | 1 | HCC801 |
2 | A419 | CC86 | 1 | HCC801 |
2 | A500 | CC100 | 3 | NA |
3 | A100 | CC54 | 1 | HCC501 |
3 | A101 | CC57 | 2 | HCC501 |
data t1;
input ID Var1 $ Var2 $ Rank;
datalines;
1 N179 CC135 2
2 I214 CC85 1
2 A419 CC86 1
2 A500 CC100 3
3 A100 CC54 1
3 A101 CC57 2
;
proc sql;
create table want as
select *,
case when sum(upcase(var2)='CC85') and sum(upcase(var2)='CC86') and upcase(var2) in ('CC85' 'CC86') then 'HCC801'
when sum(upcase(var2)='CC54') and sum(upcase(var2)='CC57') and upcase(var2) in ('CC54' 'CC57') then 'HCC501'
else 'NA'
end as Combo
from t1
group by id;
quit;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.