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
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;
Using math makes the problem simple
case when sum(day1,day2,day3) > 0 then 1 else 0 end as visit
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.
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.
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.
To handle all missing values, another way is:
when sum(day1, day2, day3, 0) > 0 then 1
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;
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;
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.
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.