topic Re: Problem merging 2 data sets after frequency transformation in SAS Enterprise Guide

Problem merging 2 data sets after frequency transformation

deleted_user — Wed, 03 Dec 2008 23:27:30 GMT

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.

Re: Problem merging 2 data sets after frequency transformation

Patrick — Thu, 04 Dec 2008 10:27:12 GMT

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

Re: Problem merging 2 data sets after frequency transformation

RichardH_sas — Fri, 05 Dec 2008 18:40:27 GMT

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.