DATA Step, Macro, Functions and more

how to merge three datasets using merge statement or proc sql

Reply
Contributor sss
Contributor
Posts: 65

how to merge three datasets using merge statement or proc sql

Hi,

I have 3 tables and I want table 4 exactly in the way I haveshown down the page, objective is no information should be lost.

Problem is, Table2 and Table 3 has 1 column name common and1 column name different i.e. PID and DATE_1 and DATE_2 but Date_1 and Date_2providing same information so I want in table4 only one column with the name asDate.

Table1

PID

 

SEX

 

DOB

 

OCC

 

1

 

F

 

1985

 

EDU

 

2

 

M

 

1986

 

IT

 

3

 

M

 

1986

 

EDU

 

Table2

PID

 

Date_1

 

1

 

1999

 

2

 

1999

 

Table3

PID

 

Date_2

 

MS

 

2

 

2002

 

A

 

3

 

2000

 

B

 

I want this kind of table in sas;

Table4

PID

 

SEX

 

DOB

 

OCC

 

MS

 

DATE

 

1

 

F

 

1985

 

EDU

 

Null

 

1999

 

2

 

M

 

1986

 

IT

 

A

 

1999

 

2

 

M

 

1986

 

IT

 

A

 

2002

 

3

 

M

 

1986

 

EDU

 

B

 

2000

 

Waiting for reply its urgent. thnx in advence

Super User
Super User
Posts: 6,502

how to merge three datasets using merge statement or proc sql

The problem is TABLE3.  You want to concatenate it with TABLE2 so that you get all the dates, but you also want to merge it by PID so that all records for the same PID get the value of the MS variable.

data table1 ;

  input pid sex $ dob occ $ ;

cards;

1 F 1985 EDU

2 M 1986 IT

3 M 1986 EDU

run;

data table2 ;

  input pid date_1 ;

cards;

1 1999

2 1999

run;

data table3 ;

  input pid date_2 ms $;

cards;

2 2002 A

3 2000 B

run;

data table4 ;

  input pid sex $ dob occ $ ms $ date ;

cards;

1 F 1985 EDU . 1999 

2 M 1986 IT A 1999

2 M 1986 IT A 2002

3 M 1986 EDU B 2000

run;

data dates;

  set table2 table3 ;

  by pid;

  drop ms;

run;

data want ;

  merge table1 table3 (keep=pid ms) dates ;

  by pid ;

  date = coalesce(date_1,date_2);

run;

proc compare data=want compare=table4;

run;

SAS Employee
Posts: 104

how to merge three datasets using merge statement or proc sql

Nice!  For efficiency's sake (I know, I know - this data is small, but it's a personal obsession...) consider renaming the date variables during the data step concatenation to avoid executing the COALESCE function in the subsequent merge:

data dates;
  set table2 (rename=(date_1=date))
      table3 (rename=(date_2=date)
              drop=ms);
  by pid;
run;
 
data want ;
  merge table1 table3 (keep=pid ms) dates;
  by pid ;
run;

proc compare data=want compare=table4;
run;
Ask a Question
Discussion stats
  • 2 replies
  • 183 views
  • 1 like
  • 3 in conversation