Help using Base SAS procedures

Merging two tables with date level and month level data

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

Merging two tables with date level and month level data

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.


Accepted Solutions
Solution
‎07-19-2012 05:27 PM
Super User
Posts: 10,500

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

Instead of

     AND  

      (A.date = B.date)

try

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

View solution in original post


All Replies
Solution
‎07-19-2012 05:27 PM
Super User
Posts: 10,500

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

Instead of

     AND  

      (A.date = B.date)

try

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

Super User
Posts: 9,681

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

I would like to us groupformat option.

data want;

merge dataset1 dataset2;

by id date groupformat;

format date monyy5.;

run;

Ksharp

Respected Advisor
Posts: 3,124

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

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 167 views
  • 3 likes
  • 4 in conversation