topic Re: Vertical conditional logic with a new variable creation in SAS Programming

Vertical conditional logic with a new variable creation

SAS_nova — Wed, 01 May 2024 16:39:59 GMT

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!

Re: Vertical conditional logic with a new variable creation

PaigeMiller — Wed, 01 May 2024 16:42:02 GMT

Please show us the desired output table.

Re: Vertical conditional logic with a new variable creation

SAS_nova — Wed, 01 May 2024 16:46:12 GMT

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

Re: Vertical conditional logic with a new variable creation

Ksharp — Thu, 02 May 2024 03:22:50 GMT

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;

Re: Vertical conditional logic with a new variable creation

SAS_nova — Thu, 02 May 2024 13:24:23 GMT

Thank you! I see those grouping makes the entire thing different!