Loop help!

Question

Ok, I'm loving JMP but need help with a loop. What i am trying to do is look if a 'Child' value is in the parent table(an example would be number 2 below). If so, I want to pull the Child values (3 and 4 below) for that parent (2). Then i want the code to check the parent column for 3,4 and so on....

Parent   Child

1               2

2               3

2               4

4               5

7               8

Parent       Child

1                2

1                3

1                4

1                5

7                8

I hope i am describing this well...

uday_guntupalli · Answer

@cjs05, 
          A couple of things.

1. This community is a JMP community and not a SAS community. While there could be some users who are familiar with both, don't expect this community to provide code guidance on SAS, I would recommend trying this question on communities.sas.com if you want the code in SAS.

2. If you would like to acheive this in JMP, you are at the right place and let us try looking at your data.

Table 1 i.e. Beginning Table has 2 columns namely Parent and Component. Try doing something like below

dt = Current Data Table();

Mat = dt << Get As Matrix;

DesComponent = 6 ;

Check = Loc(Mat[0,2] == DesComponent); // To Initialize

While(N Rows(Check)!= 0,
		Check = Loc(Mat[0,2] == DesComponent); 
		DesComponent = Mat[Check,1]; 
		Print(DesComponent);
     );

cjs05 · Answer

I have 1 table with two columns. Parent and component. I am trying to write a program that does the following.

Beginning table
Parent component
1 2
1 3
1 4
2. 5
2. 6
6. 7

Into this
Parent component
1. 2
1. 3
1. 4
1. 5
1. 6
1. 7

Basically if a component is in the parent column (like 2) I want to pull any components of 2.

The part that I am really struggling with is getting this to loop. I need sas to look for 6 in the parent column since it was a component of 2 and continue until there are no matches.

This table has about 1.2 million records. Some parents have 30 components and some have much less.

At one point i was given the macro below, but i only have JMP and can not run it (that i know of)...

%macro loop;
%do %until (&count=0);

proc sql;
create table have as select   coalesce(b.parent,a.parent) as parent , coalesce(a.component,b.component) as component
from have a  left join have b on b.component=a.parent;
run;
* if there are no components in parent then done;
proc sql ;
select count(*) into :count
from have a   join have b on b.component=a.parent;
run;

%end;
%mend;
%loop;

https://community.jmp.com/t5/forums/replypage/board-id/discussions/message-id/34434

Any help is appreciated, if this can even be done at all...

uday_guntupalli · Answer

@cjs05, 
     I would love to help but I dont understand what is the parent table and what is it that you are after. If you have 2 tables , with Parent and Child information and you are trying to look up, you can use "JOIN". Look it up under :

Tables -> Join

Else, could you please upload sample sets of your data . Here is an example I put together using Sample data which you can also try and adapt to your case:

// Clean 
Clear Globals();  Clear Log(); Close All(datatables,"No Save");

dt1 = Open( "$SAMPLE_DATA/Animals.jmp" );

dt2 = Open( "$SAMPLE_DATA/Animals Subset.jmp" );

// For Demo
Column(dt2,"miles") << Set Selected(1);

dt2 << Delete Columns;

// Look Up Information 
 dt3 = dt2 << Join(
						With( dt1 ),
						Select( :species, :subject, :season ),
						SelectWith( :miles ),
						By Matching Columns(
							:species = :species,
							:subject = :subject,
							:season = :season
						),
						Drop multiples( 0, 0 ),
						Include Nonmatches( 0, 0 ),
						Preserve main table order( 1 )
					);