topic Re: Merging Datasets in SAS Programming

Merging Datasets

DangIT — Fri, 14 Feb 2014 21:50:01 GMT

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.

Re: Merging Datasets

ballardw — Fri, 14 Feb 2014 22:06:06 GMT

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.

Re: Merging Datasets

DangIT — Sat, 15 Feb 2014 13:41:58 GMT

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