Counting by certain criteari

[Agent1592]

Dear SAS community:

I have the following table:

ID	Col1	Col2	Col3
1		0
2		0
3	0		Yes
4	0
5		40
6
7	50		No
8
9	10
10
11	0
12		0

I would like to create a new Table:

that has just two columns ID and Sum:

I would like for Col1 and Col2 to  count values greater than 0 as 1 and missing and zero values as zeros.

I would like for Col 3 to treat 'Yes" values as 1 and No and missing values as Zeros.

The want table is the following:

ID	Col1	Col2	Col3	Sum
1	0	0	0	0
2	0	0	0	0
3	0	0	1	1
4	0	0	0	0
5	0	1	0	1
6	0	0	0	0
7	1	0	1	2
8	0	0	0	0
9	1	0	0	1
10	0	0	0	0
11	0	0	0	0
12	0	0	0	0

[PeterClemmensen]

like this?

data have;
   input ID $ Col1 Col2	Col3 $;
   infile datalines dlm = ',' dsd;
   datalines;
1,,0, 
2,,0,	
3,0,,Yes
4,0,, 
5,,40,
6,,, 
7,50,,No
8,,, 
9,10,, 
10,,,
11,0,, 
12,,0, 
;

data want(drop = col3);
   set have;

   if col1 in (., 0) then col1 = 0; else col1 = 1;
   if col2 in (., 0) then col2 = 0; else col2 = 1;

   if upcase(col3) = 'YES' then col3num = 1;
   else col3num = 0;

   sum = sum(col1, col2, col3num);

   rename col3num = Col3;
run;

[Agent1592]

Thank you draycut that looks good but I am trying to learn SQL proc. Wouldn't it be easier with SQL? What is the solution wusing SQL?

[PeterClemmensen · Accepted solution]

Ah ok sure 🙂

proc sql;
   create table want as
   select ID
         ,case when Col1 in (., 0) then 0 else 1 end as Col1
         ,case when Col2 in (., 0) then 0 else 1 end as Col2
         ,case when Col3 = 'Yes' then 1 else 0 end as Col3
         ,sum(calculated Col1, calculated Col2, calculated Col3) as Sum
   from have;
quit;

[Agent1592]

Thank you that is genius code. What if I just want to keep the sum column. In the output I may not want Col1, Col2, and Col 3.

[PeterClemmensen]

Simply use the drop = statement as

proc sql;
   create table want(drop=Col1 Col2 Col3) as
   select ID
         ,case when Col1 in (., 0) then 0 else 1 end as Col1
         ,case when Col2 in (., 0) then 0 else 1 end as Col2
         ,case when Col3 = 'Yes' then 1 else 0 end as Col3
         ,sum(calculated Col1, calculated Col2, calculated Col3) as Sum
   from have;
quit;

[PeterClemmensen]

Though wouldn't your col3 for ID = 7 be 0 since 'No' --> 0?

[Patrick]

You could just create a format for this else some code as below should work.

if col1>0 then col1=1; else col1=0;

if col2>0 then col2=1; else col1=0;

if col3='Yes' the col3='1'; else col3='0';

A bit a less common way in SAS and because TRUE is represented as 1 and FALSE is represented as 0, is as follows:

col1=  (col1>0);

col2= (col2>0);

col3 = put(col3='Yes',f1.);

[Agent1592]

Thanks Patrick, is there a qay to to it with SQL Proc?

[Patrick]

case when col1>1 then 1 else 0 end as col1

...

or:

(co1>0) as col1

...