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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.