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

I have two slightly different datasets.

One (hourly):

YearMonthDayHour
201821912
201821913
201821914
201821915

Second (daily):

YearMonthDayHoliday
20182150
20182160
20182170
20182180
20182191
20182200

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 ?

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

 

View solution in original post

5 REPLIES 5
novinosrin
Tourmaline | Level 20

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;
ballardw
Super User

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.

 

novinosrin
Tourmaline | Level 20

Good catch @ballardw Thank you. Morning Coffee wasn;t strong enough, will  have to claim my money back Smiley Embarassed

ballardw
Super User

@novinosrin wrote:

Good catch @ballardw Thank you. Morning Coffee wasn;t strong enough, will  have to claim my money back Smiley Embarassed


It doesn't help when the example data is from a very small subset that could imply that only Feb 2018 is involved

matt23
Quartz | Level 8
Thank you, these are really helpful

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!

SAS Enterprise Guide vs. SAS Studio

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.

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
  • 5 replies
  • 2005 views
  • 0 likes
  • 3 in conversation