topic how to merge three datasets using merge statement or proc sql in SAS Programming

how to merge three datasets using merge statement or proc sql

sss — Sat, 01 Oct 2011 12:03:10 GMT

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

how to merge three datasets using merge statement or proc sql

Tom — Sat, 01 Oct 2011 13:11:39 GMT

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;

how to merge three datasets using merge statement or proc sql

SASJedi — Sat, 01 Oct 2011 14:33:44 GMT

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;