topic Case when for multiple columns in SAS Programming

Case when for multiple columns

rapt1 — Tue, 17 Aug 2021 21:38:21 GMT

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

Re: Case when for multiple columns

PaigeMiller — Tue, 17 Aug 2021 21:56:49 GMT

Using math makes the problem simple

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

Re: Case when for multiple columns

rapt1 — Tue, 17 Aug 2021 22:05:22 GMT

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.

Re: Case when for multiple columns

PaigeMiller — Tue, 17 Aug 2021 22:14:28 GMT

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.

Re: Case when for multiple columns

Reeza — Tue, 17 Aug 2021 22:22:37 GMT

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.

Re: Case when for multiple columns

Reeza — Tue, 17 Aug 2021 22:25:59 GMT

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

Re: Case when for multiple columns

ChrisNZ — Tue, 17 Aug 2021 22:52:43 GMT

To handle all missing values, another way is:

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

Re: Case when for multiple columns

andreas_lds — Wed, 18 Aug 2021 09:00:24 GMT

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;

Re: Case when for multiple columns

Kurt_Bremser — Wed, 18 Aug 2021 09:18:45 GMT

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;

Re: Case when for multiple columns

rapt1 — Wed, 18 Aug 2021 12:12:00 GMT

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.