BookmarkSubscribeRSS Feed
Minutemaid23
Calcite | Level 5

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;

5 REPLIES 5
JohnHoughton
Quartz | Level 8

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
Calcite | Level 5

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( :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
Calcite | Level 5
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
Quartz | Level 8

Hi @Minutemaid23

Same company , different product.

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

 

Minutemaid23
Calcite | Level 5
I apologize. Thanks!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 5 replies
  • 1306 views
  • 0 likes
  • 2 in conversation