Do Loop until end (again)

[Minutemaid23]

Hello all,

I recently posted a problem and a solution was posted. that information is below. Unfortunately, i can not run the macro because i can not submit code to a SAS metadata server in JMP due to the configuration that i have with our IT department.

Is it possible to run a macro in a JSL script? If not, can what is accomplished below be done (or possibly converted) into JSL?

Problem:

Hello all,

I am really struggling with this one. I’m not sure if a macro would be the best option. Here is my issue.

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.

Any help is appreciated. I hope I explained this well enough.

Macro Solution that does work (i just can't run it):

data have;

input parent component;

datalines;

1 2

1 3

1 4

1. 5
2. 6
3. 7

;

Here is the solution that actually works (i am just having a hard time with a macro in SAS Jmp)

%macro loop;

%do %until (&count=0);

proc sql;

create table Drilldown 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;

[JohnHoughton]

You  can run it in SAS and save the output in a permanent dataset that you then read into JMP.

(I'm assuming that you have a SAS license)

[Minutemaid23]

I wish that i could, but my company only has SAS JMP licenses. I'm not certain if i can convince my boss to go with a SAS license, since i would be the only person that would have it.

Parent	Component
1	2
1	3
1	4
1	5
5	6
6	7

I am running the code below in JMP,

The code and log are below, but I am getting the following:

Parent	Component	SubparentID2
1	2	*
1	3	*
1	4	*
1	5	*

And am trying to get this:

Parent	Component
1	2
1	3
1	4
1	5
1	6
1	7

Snums = Open Database( **odbc connection string  **

   "SELECT [dbo].[BOM].[PARENT]  as PARENTID ,[dbo].[BOM].[COMPONENT] as SUBPARENTID

FROM [dbo].[BOM] --as tb1

--LEFT JOIN [dbo].[BOM] ON [dbo].[BOM].[PARENT] = [dbo].[BOM].[COMPONENT]"

);

//Creates the new column

Snums << New Column( "SUBPARENTID2", Numeric );

/*Loops SUBPARENTID to check all rows for matches against PARENTID.

Where a match is found the value in SUBPARENTID is assigned to SUBPARENT2ID

at the row containing the value used to initiate the match

adds 9999*/

For( i = 1, i <= N Rows( Snums ), i++,

   r = Snums << Get Rows Where( :PARENTID == :SUBPARENTID[i] );

   If(

       N Rows( r ) == 0, Continue(),

       N Rows( r ) == 1 & r[1] == i, :SUBPARENTID2[i] = 0,

       N Rows( r ) == 1, :SUBPARENTID2[i] = :SUBPARENTID[r[1]],

       :SUBPARENTID2[i] = 9999

   );

);

Here is the log file:

Snums = Open Database(

   "odbc connection string",

   "SELECT [dbo].[BOM].[PARENT]  as PARENTID ,[dbo].[BOM].[COMPONENT] as SUBPARENTID

FROM [dbo].[BOM] --as tb1

--LEFT JOIN [dbo].[BOM] ON [dbo].[BOM].[PARENT] = [dbo].[BOM].[COMPONENT]"

);

//Creates the new column

Snums << New Column( "SUBPARENTID2", Numeric );

/*Loops SUBPARENTID to check all rows for matches against PARENTID.

Where a match is found the value in SUBPARENTID is assigned to SUBPARENT2ID

at the row containing the value used to initiate the match

adds 9999*/

For( i = 1, i <= N Rows( Snums ), i++,

   r = Snums << Get Rows Where( :PARENTID == :SUBPARENTID[i] );

   If(

       N Rows( r ) == 0, Continue(),

       N Rows( r ) == 1 & r[1] == i, :SUBPARENTID2[i] = 0,

       N Rows( r ) == 1, :SUBPARENTID2[i] = :SUBPARENTID[r[1]],

       :SUBPARENTID2[i] = 9999

   );

);

/*:

Column "SUBPARENTID2" requires numeric values in access or evaluation of 'Bad Argument'

Rows in table check for default filter: 1561136

Rows in table check for default filter: 1561136

[Minutemaid23]

For( i = 1, i <= N Rows( Snums ), i++,
r = Snums << Get Rows Where( : PARENTID == : SUBPARENTID[i] );
If(
N Rows( r ) == 0, Continue(),
N Rows( r ) == 1 & r[1] == i, : SUBPARENTID2[i] = 0,
N Rows( r ) == 1, : SUBPARENTID2[i] = : SUBPARENTID[r[1]],
: SUBPARENTID2[i] = 9999
);
);

[JohnHoughton]

Hi @Minutemaid23

Same company , different product.

For jmp scripting help , try the jmp community and post a discussion there.

[Minutemaid23]

I apologize. Thanks!