topic Re: Combine two files with matching variables in New SAS User

Combine two files with matching variables

dapenDaniel — Mon, 02 Sep 2019 06:31:06 GMT

Hello,

I have two files and I want to add the missing value from file 2 to file 1.

For example, File 1 has ALL IDs but incompleted variable 1 (character variable). File 2 has completed Variable1 and their corresponding IDs.

When ID = 1 or 3, variable1 is incomplete as highlighted in File 2. ID = 2 does not show in File 2, which means when ID = 2, Variable1 is completed. I would like to merge these two files as File 3 shows.

File 1

ID Variable1

1 A10

1 B20

2 G11

2 G12

2 R13

3 E25

3 E38

3 F69

File 2

ID Variable1

1 A10

1 B20

1 C30

3 E25

3 E38

3 F69

3 D87

File 3

ID Variable1

1 A10

1 B20

1 C30

2 G11

2 G12

2 R13

3 E25

3 E38

3 F69

3 D87

what code do I need to use? Thanks.

Re: Combine two files with matching variables

PeterClemmensen — Mon, 02 Sep 2019 06:50:40 GMT

data one;
input ID $ Variable1 $;
datalines;
1 A10
1 B20
2 G11
2 G12
2 R13
3 E25
3 E38
3 F69
;

data two;
input ID $ Variable1 $;
datalines;
1 A10
1 B20
1 C30
3 E25
3 E38
3 F69
3 D87
;

proc sort data=one; by ID Variable1; run;
proc sort data=two; by ID Variable1; run;

data want;
    update one two;
    by ID Variable1;
run;

Re: Combine two files with matching variables

Jagadishkatam — Mon, 02 Sep 2019 06:56:53 GMT

By merge step

data have1;
input ID  Variable1$;
cards;
1  A10
1   B20
2   G11
2  G12
2  R13
3   E25
3   E38
3   F69
;
 

data have2;
input ID  Variable1$;
cards;
1  A10
1  B20
1  C30
3  E25
3    E38
3  F69
3   D87
;

data want;
merge have1 have2;
by id;
run;

Re: Combine two files with matching variables

PeterClemmensen — Mon, 02 Sep 2019 08:07:27 GMT

If you do not want the PROC SORT steps, you can do

data _null_;
    if _N_=1 then do;
        declare hash h(dataset:'one', ordered:'Y');
        h.definekey('ID', 'Variable1');
        h.definedone();
    end;

    set two end=lr;
    h.ref();

    if lr then h.output(dataset:'want');
run;

Re: Combine two files with matching variables

Ksharp — Mon, 02 Sep 2019 12:44:07 GMT

data one;
input ID $ Variable1 $;
datalines;
1 A10
1 B20
2 G11
2 G12
2 R13
3 E25
3 E38
3 F69
;

data two;
input ID $ Variable1 $;
datalines;
1 A10
1 B20
1 C30
3 E25
3 E38
3 F69
3 D87
;
proc sql;
create table want as
select * from one
union
select * from two;
quit;