Learning SAS? Welcome to the exclusive online community for all SAS learners.

SQL query to print grandparent & grandchild name.

Reply
Occasional Contributor
Posts: 17

SQL query to print grandparent & grandchild name.

Hello Experts,

I have a dataset in the following form:

ParentName                                                 ChildName

Harivansh Rai Bachchan                               Amitabh Bachchan

Amitabh Bachchan                                        Abhishek Bachchan

Abhishek Bachchan                                      Aradhaya Bachchan

Aradhaya Bachchan                                                                      

I need to create a table from the above table having the following output:

GrandParentName                                                   GrandchildName

Harivansh Rai Bachchan                                          Abhishek Bachchan

Amitabh Bachchan                                                  Aradhaya Bachchan

Abhishek Bachchan                                                                                                   

Aradhaya Bachchan            

I am not able to create a proc sql query for the above dataset.

Please help.

Super Contributor
Posts: 254

Re: SQL query to print grandparent & grandchild name.

Not a SQL solution. How about a Hash way?

data want;

if _n_ = 1 then do;

   if 0 then set have;

   declare hash h(dataset:'have');

   h.definekey('ParentName');

   h.definedata('ChildName');

   h.definedone();

end;

   do until(last);

      set have end = last;

      if h.find() = 0 then do;

         P = ChildName;

         if h.find(keySmiley Tongue) = 0 then output;

      end;

   end;

drop P;

run;

Respected Advisor
Posts: 3,893

Re: SQL query to print grandparent & grandchild name.

What if a grand parent has more than one grand child?

Contributor
Posts: 52

Re: SQL query to print grandparent & grandchild name.

your solution should still hold.

There is a PROC SQL solution.

Given the input:

data have;

input Parent $ Child $;

cards;

A1  B1

A1  B2

A2  B3

B1  C1

B1  C2

C1  D2

B3  C3

;

run;

The PROC SQL solution is:

proc sql;

  create table want as

  select a.Parent, b.Child as GrandChild

  from  have a,

        have b

  where a.child = b.parent;

quit;

Which gives:

          Grand

Parent    Child

=====================

  A1       C1

  A1       C2

  A2       C3

  B1       D2

=====================

The following gives an error:

proc sql;

  create table want as

  select a.Parent, a.Child, (select b.Child from have b where a.Child=b.Parent) as GrandChild

  from have a;

quit;

because

(select b.Child from have b where a.Child=b.Parent)

can produce more than 1 result.

Note: Although Common Table Expression (CTE) is ANSI SQL, it is not implemented in SAS. CTE is implemented in Oracle, DB2, MS SQL Server, PosgreSQL, amongst others.Where one would use CTE in MS SQL Server, SAS Hash Objects should do.

Respected Advisor
Posts: 3,893

Re: SQL query to print grandparent & grandchild name.

Something like below should do:

data have;

  infile datalines truncover dsd;

  input (ParentName ChildName) (:$40.);

  datalines;

Harivansh Rai Bachchan,Amitabh Bachchan

Amitabh Bachchan,Abhishek Bachchan

Abhishek Bachchan,Aradhaya Bachchan

Aradhaya Bachchan,

;

run;

proc sql;

  create table want as

  select

    l.ParentName as GrandParentName,

    r.ChildName as GrandchildName

  from have l left join have r

    on l.ChildName=r.ParentName

  ;

quit;

Valued Guide
Posts: 3,208

Re: SQL query to print grandparent & grandchild name.

Before you want to code anything understand the relationships in the data.

One person could have 0-very many children/grandchildren  these childrens are also persons. Just the condition of DNA inheritance.

Every person is hopefully having two and by that many (four) Grandparents. Hopefully as for DNA inheritance it is not a requirement.
As this questions and relationships are not trivial you can find many dedicate genealogy software programs

Would be a nice exercise.

Design a database of persons with some identifer to a name. With each of them the relationship (child of -suppose max=100-  and parent of -2- of getting)  and then some path analysis

---->-- ja karman --<-----
Super User
Posts: 9,681

Re: SQL query to print grandparent & grandchild name.

You just only want the first generation map to the third generation ?  Here is one .

data Forms;

input  Parent & $40. Child & $40. @@;

cards;

Harivansh Rai Bachchan                               Amitabh Bachchan

Amitabh Bachchan                                        Abhishek Bachchan

Abhishek Bachchan                                      Aradhaya Bachchan

Aradhaya Bachchan            .

;

run;

data want(keep= GrandParentName GrandchildName);

if _n_ eq 1 then do;

length path _path  $ 400 ;

if 0 then set Forms;

declare hash ha(hashexp:20,dataset:'Forms(where=(Parent is not missing and Child is not missing))',multidata:'Y');

ha.definekey('Parent');

ha.definedata('Child');

ha.definedone();

declare hash pa(ordered:'Y');

declare hiter hi_path('pa');

pa.definekey('count');

pa.definedata('path');

pa.definedone();

end;

set Forms(where=(Parent is not missing));

count=1;

path=catx('|',Parent,Child);

pa.add();

do while(hi_path.next()=0);                     

_path=path; 

Parent=scan(path,-1,'|');

rc=ha.find();

generations=countw(path,'|');

if generations eq 3 then do;

   GrandParentName=scan(path,1,'|');

   GrandchildName=scan(path,-1,'|');

   output;

                          end;

else if generations lt 3 and (rc ne 0 or (rc eq 0 and find(path,strip(Child)))) then do;

       GrandParentName=scan(path,1,'|');

   GrandchildName=' ';

   output;

  end;

do while(rc=0);

  if not find(path,strip(Child)) then do;

                                        count+1;

                                        path=catx('|',path,Child);

                                        pa.add();

                                        path=_path;

                                      end;

  rc=ha.find_next();

end;

end;

pa.clear();

run;

Xia Keshan

Ask a Question
Discussion stats
  • 6 replies
  • 1489 views
  • 0 likes
  • 6 in conversation