topic Adding a column from different dataset in SAS Enterprise Guide

Adding a column from different dataset

matt23 — Fri, 03 Aug 2018 16:17:22 GMT

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 ?

Re: Adding a column from different dataset

novinosrin — Fri, 03 Aug 2018 16:28:16 GMT

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;

Re: Adding a column from different dataset

ballardw — Fri, 03 Aug 2018 16:39:51 GMT

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.

Re: Adding a column from different dataset

novinosrin — Fri, 03 Aug 2018 16:41:17 GMT

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

Re: Adding a column from different dataset

ballardw — Fri, 03 Aug 2018 16:48:24 GMT

@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

Re: Adding a column from different dataset

matt23 — Fri, 03 Aug 2018 17:59:02 GMT

Thank you, these are really helpful