Help using Base SAS procedures

Looping through table multiple times

Accepted Solution Solved
Reply
Super Contributor
Posts: 400
Accepted Solution

Looping through table multiple times

I'm working with someone else data and need some help trying to get all the information.

The table I'm working with has a cID, pID, txtLabel fields.

CID       PID     txtLabel

2-00      Root    level0

2-01      2-00     level1

2-02      2-01     level2

2-03      2-02     level3

2-10      Root    level0_2

2-11      2-10     level1_2

2-12      2-11     level2_2

2-13      2-12     level3_2

I have another table that only has a few specific rows with CID.  Say I have CID 2-11 and 2-03 I need to work back from this level and get every level till I get to the root.

CID       PID     txtLabel

2-00      Root    level0

2-01      2-00     level1

2-02      2-01     level2

2-03      2-02     level3

2-10      Root    level0_2

2-11      2-10     level1_2

Since I started from 2-11 I would lose the two levels below it.

I apologize if I didn't make it clear what I am trying to do.  It is a level based table and each level can have multiple rows.  I have specific levels but I need to work back and get the levels above it. 

Thank you


Accepted Solutions
Solution
‎02-14-2012 02:31 PM
PROC Star
Posts: 7,467

Looping through table multiple times

Posted in reply to jerry898969

Not sure if I correctly understand what you are trying to accomplish but, possibly, the following might be close:

data main;

  input (CID PID txtLabel) ($);

  cards;

2-00      Root    level0

2-01      2-00     level1

2-02      2-01     level2

2-03      2-02     level3

2-10      Root    level0_2

2-11      2-10     level1_2

2-12      2-11     level2_2

2-13      2-12     level3_2

2-14      2-13     level0_2

;

data lookup;

  set main;

  if PID eq "Root" then Root_Number+1;

run;

data have;

  input (CID PID txtLabel) ($);

  cards;

2-03      2-02     level3

2-11      2-10     level1_2

;

proc sql;

  create table find as

    select b.root_number,b.cid

      from have as a,

        lookup as b

         having a.cid=b.cid and

           a.pid=b.pid

  ;

  create table need as

    select a.*

      from lookup as a,find as b

        where a.root_number=b.root_number

          and a.cid le b.cid

  ;

quit;

View solution in original post


All Replies
Solution
‎02-14-2012 02:31 PM
PROC Star
Posts: 7,467

Looping through table multiple times

Posted in reply to jerry898969

Not sure if I correctly understand what you are trying to accomplish but, possibly, the following might be close:

data main;

  input (CID PID txtLabel) ($);

  cards;

2-00      Root    level0

2-01      2-00     level1

2-02      2-01     level2

2-03      2-02     level3

2-10      Root    level0_2

2-11      2-10     level1_2

2-12      2-11     level2_2

2-13      2-12     level3_2

2-14      2-13     level0_2

;

data lookup;

  set main;

  if PID eq "Root" then Root_Number+1;

run;

data have;

  input (CID PID txtLabel) ($);

  cards;

2-03      2-02     level3

2-11      2-10     level1_2

;

proc sql;

  create table find as

    select b.root_number,b.cid

      from have as a,

        lookup as b

         having a.cid=b.cid and

           a.pid=b.pid

  ;

  create table need as

    select a.*

      from lookup as a,find as b

        where a.root_number=b.root_number

          and a.cid le b.cid

  ;

quit;

Respected Advisor
Posts: 4,919

Looping through table multiple times

Your problem seems to be structured as a hierarchical tree. If you make your dataset look like the output of proc cluster (or a subset thereof) then you could feed it to proc tree for post treatment such as reducing the number of levels or extracting the subtree below a given node. - PG

PG
Respected Advisor
Posts: 3,156

Looping through table multiple times

Posted in reply to jerry898969

Another approach using mainly data step, though I can't guarantee more general application.

data h1;

input CID $   PID $     txtLabel :$10.;

cards;

1-01      Root    level

1-02      1-01    level

2-00      Root    level0

2-01      2-00     level1

2-02      2-01     level2

2-03      2-02     level3

2-10      Root    level0_2

2-11      2-10     level1_2

2-12      2-11     level2_2

2-13      2-12     level3_2

;

data h2;

input CID $ ;

cards;

2-03

2-11

;

data want (drop=_Smiley Happy;

   do until (last1);

      set h2 (rename=cid=_cid) end=last1;

do until (cid=_cid);

set h1;

if pid='Root' then _n+1;

end;

do until (cid=_cid);

set h1;

if pid='Root' then _m+1;

if _m=_n then output;

end;

end;

run;

proc print;run;

Regards,

Haikuo

Super User
Posts: 10,018

Re: Looping through table multiple times

Posted in reply to jerry898969

Hai.Kou

There is a problem in your code.

If the order of h2 is different from the order of h1, the code will get not right result.

i.e.

2-11

2-03

So to optimize Hai.Kou's code

data h1;
input CID $   PID $     txtLabel :$10.;
cards;
1-01      Root    level
1-02      1-01    level
2-00      Root    level0
2-01      2-00     level1
2-02      2-01     level2
2-03      2-02     level3
2-10      Root    level0_2
2-11      2-10     level1_2
2-12      2-11     level2_2
2-13      2-12     level3_2
;
run;
 
data h2;
input CID $ ;
cards;
2-11
2-03
; 
run;
data h2(keep=cid);
if _n_ eq 1 then do;
if 0 then set h2;
 declare hash ha(dataset:'h2',hashexp:10);
  ha.definekey('cid');
  ha.definedone();
end;
set h1;
 if ha.check()=0 then output ;
run;

data want (drop=_:);
do until (last1);
  set h2 (rename=cid=_cid) end=last1;
do until (cid=_cid);
set h1;
if pid='Root' then _n+1;
end;

do until (cid=_cid);
set h1;
if pid='Root' then _m+1;
if _m=_n then output;
end;
end;
run;


Ksharp

Super Contributor
Posts: 400

Looping through table multiple times

Thank you everyone for your replies.

Art's code pointed me in the right direction for what I was trying to do. 

Thanks again for everyones help. 

Respected Advisor
Posts: 3,156

Looping through table multiple times

Many Thanks, Ksharp! I had this hunch that my code was not good enough.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 509 views
  • 0 likes
  • 5 in conversation