topic Re: Counting by certain criteari in SAS Programming

Counting by certain criteari

Agent1592 — Thu, 24 Nov 2016 08:21:46 GMT

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

Re: Counting by certain criteari

PeterClemmensen — Thu, 24 Nov 2016 08:40:12 GMT

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;

Re: Counting by certain criteari

PeterClemmensen — Thu, 24 Nov 2016 08:37:19 GMT

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

Re: Counting by certain criteari

Patrick — Thu, 24 Nov 2016 08:40:36 GMT

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.);

Re: Counting by certain criteari

Agent1592 — Thu, 24 Nov 2016 08:52:01 GMT

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?

Re: Counting by certain criteari

Agent1592 — Thu, 24 Nov 2016 08:53:05 GMT

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

Re: Counting by certain criteari

PeterClemmensen — Thu, 24 Nov 2016 08:58:43 GMT

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;

Re: Counting by certain criteari

Agent1592 — Thu, 24 Nov 2016 09:20:06 GMT

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.

Re: Counting by certain criteari

PeterClemmensen — Thu, 24 Nov 2016 09:22:49 GMT

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;

Re: Counting by certain criteari

Patrick — Thu, 24 Nov 2016 13:12:41 GMT

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

...

or:

(co1>0) as col1

...