merge two datasets

[michtka]

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

[PGStats]

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

data want;

set new;

set new2;

run;

PG

[michtka]

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

[PGStats · Accepted solution]

More robust :

proc sql;

create table want as

select * from new natural join new2;

quit;

PG

[michtka]

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.