DATA Step, Macro, Functions and more

Counting by certain criteari

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

Counting by certain criteari

Dear SAS community:

I have the following table:

IDCol1Col2Col3
1 0 
2 0 
30 Yes
40  
5 40 
6   
750 No
8   
910  
10   
110  
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:

     
IDCol1Col2Col3Sum
10000
20000
30011
40000
50101
60000
71012
80000
91001
100000
110000
120000

Accepted Solutions
Solution
‎11-29-2016 04:09 AM
PROC Star
Posts: 763

Re: Counting by certain criteari

Posted in reply to Agent1592

Ah ok sure Smiley Happy

 

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;

View solution in original post


All Replies
PROC Star
Posts: 763

Re: Counting by certain criteari

[ Edited ]
Posted in reply to Agent1592

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;
Contributor
Posts: 36

Re: Counting by certain criteari

Thank you 

Solution
‎11-29-2016 04:09 AM
PROC Star
Posts: 763

Re: Counting by certain criteari

Posted in reply to Agent1592

Ah ok sure Smiley Happy

 

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;
Contributor
Posts: 36

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: 763

Re: Counting by certain criteari

Posted in reply to Agent1592

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: 763

Re: Counting by certain criteari

Posted in reply to Agent1592

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

Respected Advisor
Posts: 4,173

Re: Counting by certain criteari

Posted in reply to Agent1592

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

Contributor
Posts: 36

Re: Counting by certain criteari

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

Respected Advisor
Posts: 4,173

Re: Counting by certain criteari

Posted in reply to Agent1592

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

...

 

or:

(co1>0) as col1

...

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 331 views
  • 3 likes
  • 3 in conversation