BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jerry898969
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

6 REPLIES 6
art297
Opal | Level 21

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;

PGStats
Opal | Level 21

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
Haikuo
Onyx | Level 15

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

Ksharp
Super User

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

jerry898969
Pyrite | Level 9

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. 

Haikuo
Onyx | Level 15

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

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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