Merging two tables with date level and month level data

[andp]

Hi all,

I'm currently attempting to merge two databases, one has date level data, the other has month level data and I need the month level data to be repeated for all the days in that month for the first dataset.

for example:

dataset1

ID     Date

1     1/1/01

1     1/2/01

1     1/3/01

1     1/4/01

1      1/5/01

1     1/6/01

dataset2

ID     Date(month level)     Values

1          1/01                         5

Final dataset

ID     Date          Values

1     1/1/01               5

1     1/2/01               5

1     1/3/01               5

1     1/4/01               5

1     1/5/01               5

1     1/6/01               5

This is the code I've tried (ignore the date format as I'm using a diff format in my actual data) but this results in only the first day of each month assuming the value that I want applied to all days of the month.

PROC SQL;

  CREATE TABLE Finaldata AS

    SELECT A.ID,

      A.date FORMAT=YYMMDD8. AS date, 

      B.Values

      FROM dataset1A LEFT JOIN dataset2 B

        ON (A.ID= B.ID) AND 

          (A.date = B.date)

          ORDER BY ID, date;

QUIT;

Thanks for your help.

[ballardw · Accepted solution]

Instead of

     AND  

      (A.date = B.date)

try

where     (month(a.date)=month(b.date) and year(a.date)=year(b.date))

[Ksharp]

I would like to us groupformat option.

data want;

merge dataset1 dataset2;

by id date groupformat;

format date monyy5.;

run;

Ksharp

[Haikuo]

It has been a very long time before I saw 'groupformat' in action! Seems more efficient comparing to sql if running on SAS side.

Haikuo