DATA Step, Macro, Functions and more

Do Loop until end (again)

Reply
Occasional Contributor
Posts: 8

Do Loop until end (again)

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;

Contributor
Posts: 62

Re: Do Loop until end (again)

Posted in reply to Minutemaid23

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)

Occasional Contributor
Posts: 8

Re: Do Loop until end (again)

Posted in reply to JohnHoughton

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.

 

ParentComponent
12
13
14
15
56
67

 

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( Smiley TongueARENTID == Smiley FrustratedUBPARENTID[i] );

   If(

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

       N Rows( r ) == 1 & r[1] == i, Smiley FrustratedUBPARENTID2[i] = 0,

       N Rows( r ) == 1, Smiley FrustratedUBPARENTID2[i] = Smiley FrustratedUBPARENTID[r[1]],

       Smiley FrustratedUBPARENTID2[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( Smiley TongueARENTID == Smiley FrustratedUBPARENTID[i] );

   If(

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

       N Rows( r ) == 1 & r[1] == i, Smiley FrustratedUBPARENTID2[i] = 0,

       N Rows( r ) == 1, Smiley FrustratedUBPARENTID2[i] = Smiley FrustratedUBPARENTID[r[1]],

       Smiley FrustratedUBPARENTID2[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

Occasional Contributor
Posts: 8

Re: Do Loop until end (again)

Posted in reply to 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
);
);
Contributor
Posts: 62

Re: Do Loop until end (again)

Posted in reply to Minutemaid23

Hi @Minutemaid23

Same company , different product.

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

 

Occasional Contributor
Posts: 8

Re: Do Loop until end (again)

Posted in reply to JohnHoughton
I apologize. Thanks!
Ask a Question
Discussion stats
  • 5 replies
  • 138 views
  • 0 likes
  • 2 in conversation