How to have many dates in one quarter show as one entry in time series

[bwt]

Hi There,

I have sourced two different data sets. One is quarterly and one is monthly. The quarterly data contains dates that are end of each quarter. 

I have used Proc Expand to convert the monthly data in quarterly. 

However upon converting the stored date value is different and even though i'm putting in 'observed=end' the stored date value is actually the beginning of the quarter. 

Therefore when i merge there are multiple entries for each quarter. Below is the merged dataset

When i change the date format to show the full date it is 

Is there command i can run to make one row for each quarter?

Thanks

[PaigeMiller]

You could change the code so this doesn't happen. Or you could change the dates before you do the merge so then you get one row per quarter. Or you could use PROC SUMMARY to produce one row per quarter.

[bwt]

Thanks - that would be great, are you able to let me know how to do this? 

A) How do i change the dates before i merge them. Ie is there a function like in excel where i could make every date stored converted into an end of quarter value?

B) How could i change the code - below is what i've used:  

proc expand data=housing2 out=temp1 from=month to=quarter;
id Date;
convert HP=HPqtr / observed = end;
run;

data combine;
merge temp1 temp2;
by date;
Run;

[mkeintz]

You want the last monthly record from housing2 in each quarter to merge with the quarterly data in temp2.  You can do this without proc expand:

data want;
  merge housing (keep=date in=inhousing)
              temp2 (in=inqtr);
  by date;
  if inhousing=1 then set housing (rename=(date=m_date hp=hpqtr));
  if inqtr ;
 output;
 ** see note below ** call missing(of _all_);
run;

This will keep the latest monthly record per quarter, whose DATE is renamed to M_DATE so you will always know whether the most recent monthly data is actually the third month in each quarter.

If you have quarters with no matching months, this program will keep the last non-missing month from the prior quarter.  If you don't want that, decomment the call missing statement.

[PGStats · Accepted solution]

Use the ALIGN=END option in the proc expand statement.