topic Re: Merging two tables with date level and month level data in SAS Procedures

Merging two tables with date level and month level data

andp — Thu, 19 Jul 2012 21:19:55 GMT

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.

Re: Merging two tables with date level and month level data

ballardw — Thu, 19 Jul 2012 21:27:48 GMT

Instead of

AND

(A.date = B.date)

try

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

Re: Merging two tables with date level and month level data

Ksharp — Fri, 20 Jul 2012 03:34:54 GMT

I would like to us groupformat option.

data want;

merge dataset1 dataset2;

by id date groupformat;

format date monyy5.;

run;

Ksharp

Re: Merging two tables with date level and month level data

Haikuo — Sat, 21 Jul 2012 20:41:07 GMT

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