## Looping through table multiple times

Solved
Super Contributor
Posts: 405

# 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: 8,164

## Looping through table multiple times

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;

All Replies
Solution
‎02-14-2012 02:31 PM
PROC Star
Posts: 8,164

## Looping through table multiple times

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;

Posts: 5,535

## 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
Posts: 3,167

## Looping through table multiple times

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=_;

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,784

## Re: Looping through table multiple times

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: 405

## 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.

Posts: 3,167

## 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.