topic Re: Proc sql: merging two datasets and possibly two columns in SAS Programming

Proc sql: merging two datasets and possibly two columns

ywon111 — Wed, 17 May 2023 03:59:44 GMT

I am trying to merge two datasets. The issue is the first dataset has missing values in column c1, if missing c1 then merge with c2.

data have1;
input ID c1 $ c2 $;
infile datalines missover;
datalines;
1 A A
2 B
3 C
4 . E
;

data have2;
input c1 $ c2 $;
infile datalines missover;
datalines;
A AA
B BB
C CC
D DD
E EE
;

data want;
input ID c1 $ result $;
infile datalines missover;
datalines;
1 A AA
2 B BB
3 C CC
4 E EE
;

Re: Proc sql: merging two datasets and possibly two columns

Kurt_Bremser — Wed, 17 May 2023 04:40:33 GMT

data want;
set have1;
if _n_ = 1
then do;
  length result $8;
  declare hash h2 (dataset:"have2 (rename=(c2=result))");
  h2.definekey("c1");
  h2.definedata("result");
  h2.definedone();
end;
c1 = coalescec(c1,c2);
if  h2.find() ne 0 then result = "";
run;

Untested, posted from my tablet.

Re: Proc sql: merging two datasets and possibly two columns

LinusH — Wed, 17 May 2023 11:50:07 GMT

SQL version:

proc sql;
	create table want as
		select id, coalesce(have1.c1, have1.c2) as c1, have2.c2 as result
		from have1
		left join have2
		on coalesce(have1.c1, have1.c2) = have2.c1
	;
quit;

Small remark, since c1 is declared as char, the <.> is stored as a period, not as MISSING.