BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Situation: have some monthly and weekly time series. Weekly data is fine. Took the monthly data and used "prepare time series data" application to spread the data down to the weekly level for each month. Output data set (weekly data) from that procedure looks okay, but is somewhat different than my first weekly data set in that the "date" variable has a date format "WEEKDATXw.d", whereas the first weekly data set date is formated "DATETIMEw.d" (a date/time format). (This is the default from the process.)

The informats for both appear to be the same - DATETIMEw.d. I decide "what, me worry? Enterprise Guide will know how to do the merge."

Result 1: Alas, it does not work. No error message in the log, but the resulting output data set is empty. Changing the type of join is not successful--I get either one or the other data set in the output, but not the combined series from both original sets.

So: I decide to go back to the dataset that resulted from the "prepare time series" process. I change the format to DATETIMEw.d on the off chance that that will create something that is "mergable". However, what I end up with after changing the format is a Date/time series that looks like every observation is from the same calendar day, with the only thing changing in each successive observation is the "second" increment. Obviously, this is NOT what is going on.

I am at a loss how the transformation process has caused this problem. Any suggestions before I go to Tech Support?

Thanks.
2 REPLIES 2
Patrick
Opal | Level 21
I don't know what "prepare time series data" does but from what you tell I have the suspicion that your merge doesn't work because the keys are different.

The format of your 'date variable' doesn't matter as formats are only used for printing values.
A 'SAS date variable' is numeric and contains the DAYS since 1/1/1960.
A 'SAS datetime variable' is also numeric and contains the SECONDS since 1/1/1960.
And here could be the problem: You write about datetime. informat (so the variable should contain a number representing a SAS datetime) but then the key variable has in one case a weekdatx. format (used for date variables; days since 1/1/1960) and in the other case a datetime format (=seconds since 1/1/1960).

Check the values of your keys (i.e. with 'put myvar best32.;)

This would then also explain your join results:
Inner Join: Null result
Left Join: Only data from left table
Right Join: Only data from right table

If you want to create a denormalised dataset by joining weekly data with monthly ones then I assume that you have to transform your keys before merging - i.e. by: KeyForMerge=put(MyDateTimeVar,dtmonyy7.); (for datetime vars) and KeyForMerge=put(MyDateTimeVar,monyy7.); (for date vars).

Alternatively you could use the function 'intnx' to transform your keys to a common basis.

HTH
Patrick

Message was edited by: Patrick
RichardH_sas
SAS Employee
Agreed with what Patrick wrote. 95% if the time if a join runs without error but the table has 0 rows, it's some issue with the join conditions. I strongly suspect (like Patrick) the issue is that one table has SAS datetime values, the other has SAS date values.

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