Merging / Combining data

[matt23]

Hi, I have two excel files but they are not formatted the same way and I want to merge them together. I'd do it manually but there is almost 90,000 observations.

One of them goes by day and looks like this:

Year	Month	Day	Weekday	Holiday
2009	1	1	4	1
2009	1	2	5	0
2009	1	3	6	0
2009	1	4	0	0
2009	1	5	1	0
2009	1	6	2	0
2009	1	7	3	0
2009	1	8	4	0
2009	1	9	5	0

the other one goes by hour and looks like this:

Year	Month	Day	Hour
2009	1	1	0
2009	1	1	1
2009	1	1	2
2009	1	1	3
2009	1	1	4
2009	1	1	5
2009	1	1	6
2009	1	1	7
2009	1	1	8
2009	1	1	9
2009	1	1	10
2009	1	1	11
2009	1	1	12

and goes to year 2017

Is there a way to combine them into one? Or anything at all ? Maybe in excel ?

[ballardw · Accepted solution]

Why do you need to "merge" these data sets?

If they are SAS data sets and if you make an actual date value from month, day and year (large economy sized hint: function MDY) then there are SAS functions that will return the day of the week (weekday function)  and whether the date is a standard holiday (holidaytest function).

[matt23]

Tha would be great but I have no idea how to do it. I'm new to SAS.

Would you be able to show an example of a code?

[jebjur]

Can you show an example of what the final merged data set would look like?

[matt23]

Something like this:

Year	Month	Day	Hour	Weekday	Holiday	Var1	Var2	Var3
2009	1	31	19	3	0	12312	24	12
2009	1	31	20	3	0	345345	24	23
2009	1	31	21	3	0	24345	54	24
2009	1	31	22	3	0	345345	43	25
2009	1	31	23	3	0	435345	65	25
2009	2	1	0	4	1	345345	44	26
2009	2	1	1	5	1	4353354	43	28

[matt23]

NVM, just did it in excel.

[Kurt_Bremser]

Weekdays have a function in SAS, and holidays also. Since most holidays in the Western world are either fixed by date (eg Christmas or Independence Day) or depend on Easter (which can be determined by the holiday() function), it's easy to determine that on the fly during other data step processing.