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.
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;
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;
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;
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;
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.