topic Re: Failure to merge two data sets by date - month to quarterly data frequency conversion in SAS Programming

Failure to merge two data sets by date - month to quarterly data frequency conversion

Rcoder — Thu, 04 Nov 2021 17:37:29 GMT

I want to combine two data sets by a common date variable. The first data set was produced using proc expand to convert monthly data to quarterly frequency. The second data set has quarterly data and dates. I cannot merge the two files by date because they don't match up in the two files - while the second data set has informat and format of YYQ6. for the date, the first data set has informat of MMDDYY10. and format of YYQC6. for the date. I gather that the specific day of the quarterly data varies by day within a given quarter in the second data set. I've tried reformatting the date to quarter in the first data set without success. I'd appreciate your thoughts on how to merge the two files.

I'm using SAS 9.4 (TS1M6) on an X64_10PRO platform. Thanks for your help.

Re: Failure to merge two data sets by date - month to quarterly data frequency conversion

Reeza — Fri, 05 Nov 2021 16:19:54 GMT

Why post your code as a PDF? In the future please post it directly into your post using a code block.

SAS formats control the appearance but in the MERGE it's using the underlying variable unless you specify the GROUPFORMAT option. Try adding that to your BY statement and then see if it works.

data stocks_A;
set sashelp.stocks;
where stock='IBM';
format date yymmn6.;
*keep only relevant variables for testing;
keep date open;

*rename to identify source;
rename open=OpenA;
run;

data stocks_B;
set sashelp.stocks;
where stock='IBM';
*dates are not the same now in each data set;
DATE = DATE + 1;
format date yymmn6.;

*keep only relevant variables for testing;
keep date open;

*rename to identify source;
rename open=OpenB;
run;

proc sort data=stocks_A; by date;
proc sort data=stocks_B; by date;
run;

*with group format;
data merged;
merge stocks_A stocks_B;
by date groupformat;
format date yymmn6.;
run;

*without group format;
data merged_WRONG;
merge stocks_A stocks_B;
by date;
format date yymmn6.;
run;

Re: Failure to merge two data sets by date - month to quarterly data frequency conversion

ChrisNZ — Fri, 05 Nov 2021 21:18:26 GMT

1. The PDF file is out of bounds, post your code as code (there's an icon for that).

2. The merge happens on the value, the format has no influence.

3. If the values are indeed different for a given quarter, it's better better to align them say on the first day of the quarter.

Otherwise try SQL merging with put(DATE1,yyq.)=put(DATE2,yyq.)

Re: Failure to merge two data sets by date - month to quarterly data frequency conversion

Rcoder — Fri, 05 Nov 2021 16:08:13 GMT

Thanks for your observations. This was my first post and I'll make sure to post in code in the future. The put statements in SQL worked perfectly.

Re: Failure to merge two data sets by date - month to quarterly data frequency conversion

ChrisNZ — Fri, 05 Nov 2021 23:37:11 GMT

>This was my first post and I'll make sure to post in code in the future.

No worries, all good. 🙂

I wish there were better explanations and requirements on the post page.

The grey box on the right is much too incomplete, too easily ignored, and posters keep being reminded by repliers about how they should post so they can be helped. @ChrisHemedinger