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
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;
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;
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
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
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
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.
Many Thanks, Ksharp! I had this hunch that my code was not good enough.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.