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
;
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;
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)
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
Same company , different product.
For jmp scripting help , try the jmp community and post a discussion there.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.