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
...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.