topic Re: RETRIEVING PARTICULAR RECORDS USING JOINS? in SAS Programming

RETRIEVING PARTICULAR RECORDS USING JOINS?

shravanisreeyan — Sun, 18 Oct 2020 14:41:34 GMT

Data Have1;

input id @@;

cards;

1 1 2 2 3 . .

;

run;

Data Have2;

input id @@;

cards;

1 1 2 2 2 4 . .

;

run;

In above program . . indicates missing values

By using joins how can i retrieve 3 , 4 i.e resultant dataset contain 3, 4 values from id variable.

Re: RETRIEVING PARTICULAR RECORDS USING JOINS?

Kurt_Bremser — Sun, 18 Oct 2020 15:06:16 GMT

data want;
merge
  have1 (in=h1)
  have2 (in=h2)
;
by id;
if not h1 or not h2;
run;

Re: RETRIEVING PARTICULAR RECORDS USING JOINS?

shravanisreeyan — Sun, 18 Oct 2020 15:31:34 GMT

Happy with your code. I want to achieve same output using sql joins?

Re: RETRIEVING PARTICULAR RECORDS USING JOINS?

Kurt_Bremser — Sun, 18 Oct 2020 15:59:18 GMT

Untested:

proc sql;
create table want as
  select coalesce(h1.id,h2.id) as id
  from have1 h1
  full join have2 h2
  where h1.id is missing or h2.id is missing
;
quit;

Re: RETRIEVING PARTICULAR RECORDS USING JOINS?

Ksharp — Mon, 19 Oct 2020 13:34:28 GMT

Data Have1;                     
input id @@;
cards;
1 1 2 2 3  . .
;
run;

Data Have2;
input id @@;
cards;
1 1 2 2 2 4  . .
;
run;

proc sql;
create table want as
(select id from have1 except select id from have2) 
union
(select id from have2 except select id from have1) 
;
quit;