I have two slightly different datasets.
One (hourly):
Year | Month | Day | Hour |
2018 | 2 | 19 | 12 |
2018 | 2 | 19 | 13 |
2018 | 2 | 19 | 14 |
2018 | 2 | 19 | 15 |
Second (daily):
Year | Month | Day | Holiday |
2018 | 2 | 15 | 0 |
2018 | 2 | 16 | 0 |
2018 | 2 | 17 | 0 |
2018 | 2 | 18 | 0 |
2018 | 2 | 19 | 1 |
2018 | 2 | 20 | 0 |
Now I want to add Holiday variable to the first one. The problem is that the first one is hourly so it has over 80000 obs and the second is daily so it has much less observations. How can I combine them so if it is holiday it would be '1' for all hours that day ?
If you are using common US holidays:
data example; input Year Month Day Hour ; Holiday= not missing(holidayname(mdy(month,day,year))); datalines; 2018 2 18 12 2018 2 19 12 2018 2 19 13 2018 2 19 14 2018 2 19 15 2018 2 20 15 ; run;
@novinosrin's solution likely needs to include year and month as well
proc sql; create table want as select a.*,b.holiday from hours a left join daily b on a.day=b.day and a.year=b.year and a.month=b.month ; quit;
You may find it very helpful to actually create a date valued variable using the MDY function as there are many things that are doable with a date variable that are a serious pain to attempt with 3 variables, such as calculating days between values or using formats to create groups of records without have to add variables.
Join using day variable
data hours;
input Year Month Day Hour;
cards;
2018 2 19 12
2018 2 19 13
2018 2 19 14
2018 2 19 15
;
data daily;
input Year Month Day Holiday;
cards;
2018 2 15 0
2018 2 16 0
2018 2 17 0
2018 2 18 0
2018 2 19 1
2018 2 20 0
;
proc sql;
create table want as
select a.*,b.holiday
from hours a left join daily b
on a.day=b.day;
quit;
If you are using common US holidays:
data example; input Year Month Day Hour ; Holiday= not missing(holidayname(mdy(month,day,year))); datalines; 2018 2 18 12 2018 2 19 12 2018 2 19 13 2018 2 19 14 2018 2 19 15 2018 2 20 15 ; run;
@novinosrin's solution likely needs to include year and month as well
proc sql; create table want as select a.*,b.holiday from hours a left join daily b on a.day=b.day and a.year=b.year and a.month=b.month ; quit;
You may find it very helpful to actually create a date valued variable using the MDY function as there are many things that are doable with a date variable that are a serious pain to attempt with 3 variables, such as calculating days between values or using formats to create groups of records without have to add variables.
Good catch @ballardw Thank you. Morning Coffee wasn;t strong enough, will have to claim my money back
@novinosrin wrote:
Good catch @ballardw Thank you. Morning Coffee wasn;t strong enough, will have to claim my money back
It doesn't help when the example data is from a very small subset that could imply that only Feb 2018 is involved
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.