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 |
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;
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;
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?
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;
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.
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;
Though wouldn't your col3 for ID = 7 be 0 since 'No' --> 0?
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.);
Thanks Patrick, is there a qay to to it with SQL Proc?
case when col1>1 then 1 else 0 end as col1
...
or:
(co1>0) as col1
...
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.