topic Re: Summarize the given value by one category in SAS Programming

Summarize the given value by one category

Sathish_jammy — Thu, 26 Jul 2018 06:58:40 GMT

Hi community,

Here i have a example dataset

data xxx;
input col1 col2 col3;
cards;
20 20 25.34 
55 10 22.33
42 55 22.22
22 45 14.22
run;
proc print;
run;

I want to summarize the value using

Var	Yes	No
col1≥40	2	0
col2/col1 ratio≥1	1	0
col3≥25	1.5	0

The output should be like,

Condition:

if col11 ge 40 and ratio ge 1 and col3 ge 25 result = 4.5

Result
2.5	(0+1+1.5)
3	(2+1+0)
2	(2+0+0)
0	(0+0+0)

I'm looking for Result column.

I can done this by calculate individual column. but that takes more time and line of codes.

Can anyone suggest me the effective codes to resolve this method.

Re: Summarize the given value by one category

Kurt_Bremser — Thu, 26 Jul 2018 07:16:00 GMT

Make use of the fact that boolean operations result in either 0 (false) or 1 (true), so it ends up a simple calculation:

data have;
input col1 col2 col3;
cards;
20 20 25.34 
55 10 22.33
42 55 22.22
22 45 14.22
;
run;

data want;
set have;
result =
  (col1 >= 40) * 2 +
  (col2/col1 >= 1) +
  (col3 >= 25) * 1.5
;
run;

Re: Summarize the given value by one category

Sathish_jammy — Thu, 26 Jul 2018 10:38:05 GMT

Thank you for your help.. @Kurt_Bremser

I have a addition condition in this step

data have;
input gen$ col1 col2 col3;
cards;
m 20 20 25.34 
f 55 10 22.33
m 42 55 22.22
f 22 45 14.22
;
run;

data want;
set have;
result =
  (col1 >= 40) * 2 +
  (col2/col1 >= 1) +
  (col3 >= 25) * 1.5
;
run;

the condition is

Var

Yes

col2/col1≥0.9 (Male) and col2/col1≥0.8 (Female)

how to add this in the step.

Thanks in advance!

Re: Summarize the given value by one category

Kurt_Bremser — Thu, 26 Jul 2018 10:48:08 GMT

Use the ifn() function to get your variable comparison values:

result2 = (col2/col1 >= ifn(gen='m',0.9,0.8));