DATA Step, Macro, Functions and more

Merging Datasets

Reply
Frequent Contributor
Posts: 81

Merging Datasets

Hi,

I feel like i'm stuck on something so simple, and I just dont know why...

I have 2 data sets

FSALDU table

A1A1A1

A1B1B1

... 800,000 records

Date table

2013-01-01

2013-02-01

.. (365 days)

I would like to create a table that merges the two, where date is repeated across all FSALDU's

FSALDU Date

A1A1A1 2013-01-01

A1A1A1 2013-01-02

A1A1A1 2013-01-03

...  (800,000*365=292M records)

Your help greatly appreciated, thanks in advance.

KD

Super User
Posts: 10,516

Re: Merging Datasets

Proc sql;

     create table want as

     select FSALDU,DATE

     from name_of_dataset_with_fsaldu_goes_Here join name_of_date_table;

quit;

You'll get a message in the log about a full cartesian join and it will take some time.

But another option would be:

Data want;

     set name_of_dataset_with_fsaldu_goes_Here;

     do date = '01JAN2013'd to '31DEC2013'd;

          output;

     end;

     format date yymmdd10.;

run;

might be quicker. Also when you need to modify the process to incude a leap year SAS does that because it knows which years, or at least for the next 10,000 or so, which will need a leap day and you needn't build a new dates data set.    

Frequent Contributor
Posts: 81

Re: Merging Datasets

Thank you! I knew it was something so simple i was missing.

Ask a Question
Discussion stats
  • 2 replies
  • 192 views
  • 0 likes
  • 2 in conversation