topic Re: query for parent in SAS Programming

query for parent

Aexor — Sun, 02 May 2021 11:05:43 GMT

I have a table like this:

Parent Child

A B

B C

C D

I J

J K

K L

i want to write a query which which output values which are parent only having no child.

Output A and I

Re: query for parent

sbxkoenk — Sun, 02 May 2021 11:40:39 GMT

I think this is what you want:

data have;
 input Parent $ Child $;
 cards;
A            B
B            C
C            D
I            J
J            K
K            L
;
run;

PROC SQL noprint;
 create table want as
 select Parent
 from have
 where Parent NOT IN (select Child from have)
;
QUIT;

dm 'FSV';
/* end of program */

Koen

Re: query for parent

singhsahab — Sun, 02 May 2021 12:50:16 GMT

@Aexor can be solve by using Hash Object


data want;
if _n_ eq 1 then do;
declare hash h(dataset:'have');
h.defineKEY('Child');
h.definedata('parent');
h.definedone();
end;
do until ( eof2 ) ;
 set have end = eof2 ;
 if h.find(key:parent) ne 0 then 
 OUTPUT;
 end ;
 stop ; 
run;

Regards,

Re: query for parent

PeterClemmensen — Tue, 04 May 2021 17:57:01 GMT

I suspect you want to know more than just A and I here?