Desktop productivity for business analysts and programmers

Merging / Combining data

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 89
Accepted Solution

Merging / Combining data

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:

YearMonthDayWeekdayHoliday
20091141
20091250
20091360
20091400
20091510
20091620
20091730
20091840
20091950

 

the other one goes by hour and looks like this:

YearMonthDayHour
2009110
2009111
2009112
2009113
2009114
2009115
2009116
2009117
2009118
2009119
20091110
20091111
20091112

 

and goes to year 2017

 

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


Accepted Solutions
Solution
‎06-26-2018 11:38 AM
Super User
Posts: 13,941

Re: Merging / Combining data

[ Edited ]

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).

 

 

View solution in original post


All Replies
Solution
‎06-26-2018 11:38 AM
Super User
Posts: 13,941

Re: Merging / Combining data

[ Edited ]

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).

 

 

Frequent Contributor
Posts: 89

Re: Merging / Combining data

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?

SAS Employee
Posts: 19

Re: Merging / Combining data

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

Frequent Contributor
Posts: 89

Re: Merging / Combining data

Something like this:

YearMonthDayHourWeekdayHolidayVar1Var2Var3
20091311930123122412
200913120303453452423
20091312130243455424
200913122303453454325
200913123304353456525
2009210413453454426
20092115143533544328
Frequent Contributor
Posts: 89

Re: Merging / Combining data

NVM, just did it in excel.
Super User
Posts: 10,571

Re: Merging / Combining data

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 111 views
  • 0 likes
  • 4 in conversation