topic Re: merge two datasets in SAS Procedures

merge two datasets

michtka — Sun, 21 Oct 2012 18:35:52 GMT

Hi everyone,

I've got two datasets...

data new;
input no week locf;
datalines;
1 0 0
1 3 0
1 6 0
1 0 1
1 3 1
1 6 1
1 0 2
1 3 2
1 6 2
2 0 0
2 3 0
2 6 0
2 0 1
2 3 1
2 6 1
2 0 2
2 3 2
2 6 2
;
run;

data new2;
length class $10.;
input no week class;
datalines;
1 0 a
1 3 a
1 6 a
1 0 b
1 3 b
1 6 b
1 0 c
1 3 c
1 6 c
2 0 a
2 3 a
2 6 a
2 0 b
2 3 b
2 6 b
2 0 c
2 3 c
2 6 c
;
run;

I would like to obtain it:...but I need some help...thanks V.

1 0 0 a

1 3 0 a

1 6 0 a

1 0 1 a

1 3 1 a

1 6 1 a

1 0 2 a

1 3 2 a

1 6 2 a

1 0 0 b

1 3 0 b

1 6 0 b

1 0 1 b

1 3 1 b

1 6 1 b

1 0 2 b

1 3 2 b

1 6 2 b

1 0 0 c

1 3 0 c

1 6 0 c

1 0 1 c

1 3 1 c

1 6 1 c

1 0 2 c

1 3 2 c

1 6 2 c

Re: merge two datasets

PGStats — Sun, 21 Oct 2012 18:50:40 GMT

Simplest, but fragile (relies on exact correspondence between the datasets) :

data want;

set new;

set new2;

run;

Re: merge two datasets

PGStats — Sun, 21 Oct 2012 18:54:55 GMT

More robust :

proc sql;

create table want as

select * from new natural join new2;

quit;

Re: merge two datasets

michtka — Sun, 21 Oct 2012 19:05:22 GMT

sorry PGStats....the non sql procedure is not right:

1 1 0 0 a

2 1 3 0 a

3 1 6 0 a

4 1 0 1 b

5 1 3 1 b

6 1 6 1 b

7 1 0 2 c

8 1 3 2 c

9 1 6 2 c

10 2 0 0 a

11 2 3 0 a

12 2 6 0 a

13 2 0 1 b

14 2 3 1 b

15 2 6 1 b

16 2 0 2 c

17 2 3 2 c

18 2 6 2 c

Re: merge two datasets

michtka — Sun, 21 Oct 2012 19:18:09 GMT

Brilliant, proc sql works.

I think with not proc sql more manipulation is required for thiis kind of many-many merge I think.

Thanks a lot.