topic Looping through table multiple times in SAS Procedures

Looping through table multiple times

jerry898969 — Tue, 14 Feb 2012 19:01:49 GMT

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

Looping through table multiple times

art297 — Tue, 14 Feb 2012 19:31:29 GMT

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;

Looping through table multiple times

PGStats — Tue, 14 Feb 2012 20:19:42 GMT

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

Looping through table multiple times

Haikuo — Tue, 14 Feb 2012 20:24:49 GMT

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;

run;

proc print;run;

Regards,

Haikuo

Re: Looping through table multiple times

Ksharp — Wed, 15 Feb 2012 06:53:20 GMT

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

Looping through table multiple times

jerry898969 — Mon, 20 Feb 2012 13:58:45 GMT

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.

Looping through table multiple times

Haikuo — Mon, 20 Feb 2012 14:09:05 GMT

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