Case when for multiple columns

[rapt1]

Hi everyone,

Is there a way to create another column based on this objective - as long as the client visit any day from Day1 to Day3, they will be tagged in another column called Visit:

Client Day1 Day2 Day3

ABC   1        0       1

DEF   0        0       0

GHI    1        1       1

I was thinking of using case when in proc sql but I am stuck with the syntax or if case when is even the proper statement to use. Basically, the output will be:

Client Day1 Day2 Day3 Visit

ABC   1        0       1        1

DEF   0        0       0        0

GHI    1        1       1        1

[PaigeMiller]

Using math makes the problem simple

case when sum(day1,day2,day3) > 0 then 1 else 0 end as visit

[rapt1]

Thanks! Is this also a valid syntax when a row/observation contains a missing value? Say,

Client Day1 Day2 Day3

ABC   1        0       1

DEF   0        0       0

GHI    1        1       1

JKL    .         .        1

I think I got this error:

NOTE: Invalid (or missing) arguments to the XXX function have caused the function to return a missing value.

[PaigeMiller]

I'm not getting this particular note when I use missing values. (and you didn't get an ERROR, you got a NOTE)

Please show us the ENTIRE log from this code (so we can see the code and the ERRORs, WARNINGs and NOTEs). Do not pick parts to show us and then not show us other parts of the log. Please copy the entire log as text and paste it into the window that appears when you click on the </> icon. DO NOT SKIP THIS STEP.

[Reeza]

You're doing something differently than suggested then. Why did you blank out the XXX? What does that mean?

This generates no errors or warnings.

 80         
 81         
 82         proc sql;
 83         create table want as
 84         select *, case when sum(day1, day2, day3) > 0 then 1 else 0 end as visit
 85         from have;
 NOTE: Table WORK.WANT created, with 4 rows and 5 columns.
 
 86         quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              5465.87k
       OS Memory           29096.00k
       Timestamp           08/17/2021 10:21:06 PM
       Step Count                        32  Switch Count  2
       Page Faults                       0
       Page Reclaims                     205
       Page Swaps                        0
       Voluntary Context Switches        10
       Involuntary Context Switches      0
       Block Input Operations            0

---

@rapt1 wrote:

Thanks! Is this also a valid syntax when a row/observation contains a missing value? Say,

Client Day1 Day2 Day3

ABC   1        0       1

DEF   0        0       0

GHI    1        1       1

JKL    .         .        1

I think I got this error:

NOTE: Invalid (or missing) arguments to the XXX function have caused the function to return a missing value.

---

[Reeza]

You will get that NOTE if you have ALL missing values though.
How do you want to handle that case - explicitly deal with it.
This codes it to -1.

case when nmiss(day1, day2, day3) eq 3 then -1
when sum(day1, day2, day3) > 0 then 1
else 0 end as visit

[ChrisNZ]

To handle all missing values, another way is:

when sum(day1, day2, day3, 0) > 0 then 1

[andreas_lds · Accepted solution]

Sometimes using a data step instead of proc sql avoids unnecessary notes and keeps code short.

data want;
   set have;

   Visit = sum(of Day:) > 0;
run;

[Kurt_Bremser]

The logical shortcut can also be used in SQL:

proc sql;
create table want as
  select
    t1.*,
    (sum(day1,day2,day3,0) > 0) as visit
  from have t1
;
quit;

[rapt1]

Thank you everyone for the inputs! I would try to do all your suggestions and look into data step as well. I have been using proc sql since I am more familiar with sql but will get into data step soon.

Also, thank you for the reminder on sharing the code. I oftentimes forget about this.