Date:         Mon, 3 May 2004 11:12:46 -0400
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: merge two datasets by id for every year
Comments: To: sportsfun <sportsfun77@HOTMAIL.COM>
Content-Type: text/plain

Three suggestions:
- move the year data values from file names to column variables;
- stack the 20 datasets into one dataset. Doing that often reduces 20
programs to 1;
- merge/join on a compound key that includes YEAR as well as ID.

In SAS SQL,
create view ALLYearsANNVW as
select * from
   ( (select *,84 as year from ONE84)
         union corr
     (select *,85 as year from ONE85)
         union corr
         .......
   )
;
create view ALLYearsEXEVW as
select * from
   ( (select *,84 as year from TWO84)
         union corr
     (select *,85 as year from TWO85)
         union corr
         .......
   )
;
create table ALL as
select t1.ID as ID,t1.year as year,t1.Var as Var1, ....,t2.Var as Var2,....
from ALLYearsANNVW as t1 inner join ALLYearsEXEVW as t2
on t1.ID=t2.ID and t1.year=t2.year
;

SQL lets you qualify variable selections by dataset name and assign an alias
to them (rename) in a SELECT statement. This feature alone will save you
many headaches.

Sig

-----Original Message-----
From: sportsfun [mailto:sportsfun77@HOTMAIL.COM]
Sent: Sunday, May 02, 2004 4:42 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: merge two datasets by id for every year


Hi,

I have two different datasets, for each of the datasets I have the data for
each variable for 20 years (in 20 different data files).

I want to merge the two datasets:

DATA ONE;
INFILE 'D:\DATA\ANN84.CSV' DLM=',' DSD MISSOVER FIRSTOBS=2; INPUT VAR1 VAR2
VAR3 YEAR ID;

DATA TWO;
INFILE 'D:\DATA\EXE84.CSV' DLM=',' DSD MISSOVER FIRSTOBS=2; INPUT VAR1 VAR2
VAR3 YEAR ID;

DATA THREE;
MERGE ONE (IN=A) TWO (IN=B);
BY ID;
IF A AND B;
RUN;

I need to do this for every year. Is there some way to run the program just
once and get the required results? I just don't like the idea of run the
same program over and over again.

Thanks!