BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Agent1592
Pyrite | Level 9

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
1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

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;

View solution in original post

9 REPLIES 9
PeterClemmensen
Tourmaline | Level 20

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;
Agent1592
Pyrite | Level 9

Thank you 

PeterClemmensen
Tourmaline | Level 20

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;
Agent1592
Pyrite | Level 9

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.

PeterClemmensen
Tourmaline | Level 20

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;
PeterClemmensen
Tourmaline | Level 20

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

Patrick
Opal | Level 21

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

Agent1592
Pyrite | Level 9

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

Patrick
Opal | Level 21

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

...

 

or:

(co1>0) as col1

...

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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