## Counting by certain criteari

Solved
Frequent Contributor
Posts: 76

# Counting by certain criteari

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

Accepted Solutions
Solution
‎11-29-2016 04:09 AM
PROC Star
Posts: 1,400

## Re: Counting by certain criteari

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;``````

All Replies
PROC Star
Posts: 1,400

## Re: Counting by certain criteari

[ Edited ]

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;``````
Frequent Contributor
Posts: 76

## Re: Counting by certain criteari

Thank you

Solution
‎11-29-2016 04:09 AM
PROC Star
Posts: 1,400

## Re: Counting by certain criteari

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;``````
Frequent Contributor
Posts: 76

## Re: Counting by certain criteari

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.

PROC Star
Posts: 1,400

## Re: Counting by certain criteari

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;``````
PROC Star
Posts: 1,400

## Re: Counting by certain criteari

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

Posts: 4,797

## Re: Counting by certain criteari

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

Frequent Contributor
Posts: 76

## Re: Counting by certain criteari

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

Posts: 4,797

## Re: Counting by certain criteari

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

...

or:

(co1>0) as col1

...

☑ This topic is solved.