SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

HOW TO: Assign a column value to a global variable

Reply
Contributor
Posts: 38

HOW TO: Assign a column value to a global variable

I have a work flow composed of 4 nested jobs.
In one of the jobs I am retrieving Oracle SEQUENCE NEXTVAL and insert into a table. However I need to carry the same value through out the entire work flow and carry it over to other jobs for processing. I know how to assign a global variable to a column value using EXPRESSION in the mapping but I need to do the reverse here in this case. I tried a code in post code but that advances the SEQUENCE value to its next value and that is not acceptable.

I'll appreciate suggestions and solutions.

Thanks,

MI
SAS Employee
Posts: 51

Re: HOW TO: Assign a column value to a global variable

MI,

I don't know all the details of what you're trying to do, but if this value needs to be passed between jobs, your best bet is to write it to a table or file.

Thanks,

Tim Stearn
Contributor
Posts: 38

Re: HOW TO: Assign a column value to a global variable

Posted in reply to TimStearn_SASProductManagement_
Thanks Tim,
But I am wondering if I can assign to global variable whose value I am passing in the inner jobs. And what makes you suggest temp table option, is global variable use is not stable in SAS?

Thanks for taking time to reply.

Misbah.
Super User
Posts: 5,438

Re: HOW TO: Assign a column value to a global variable

Please try more exactly describe your job.
You have an outer job, with four inner jobs, right?
Are they run in sequence or in parallel (using the loop transform)?
Is it in the first job that you retrieve this value?
Does the rest of the jobs use tha same value, or does all jobs need to regenerate it (for next job)?
You said about writing to a table "and that is not acceptable". Why is that?

/Linus
Data never sleeps
Contributor
Posts: 38

Re: HOW TO: Assign a column value to a global variable

This is the workflow:

Job1: Retrieve the SEQUENCE NEXTVAL from Oracle
Job2Smiley Tonguerocess data using the NEXTVAL value
Job3: Process data using the NEXTVAL value
Job4: call Job1 then LOOP Job2 & Job3 END LOOP

What I intend to do is to retrieve the NEXTVAL in Job1 and pass to global variable g_logid
and then use the &g_logid in the expression section of TABLELOADER throughout.

Thnaks,

Misbah.
Super User
Posts: 5,438

Re: HOW TO: Assign a column value to a global variable

Are you using Loop for actual looping, or just a way of nesting jobs?
If the later, you could just nest job without the Loop transform (if you are in DIS 4.X). In this scenario, there are no SAS/CONNECT remote sessions launched, just expanded code in a single SAS session, which means that global macro variables are available in each job.

/Linus
Data never sleeps
Respected Advisor
Posts: 4,173

Re: HOW TO: Assign a column value to a global variable

I agree with Linus: If you're calling job2 & job3 only once then just add them to the flow of job1 without a loop transformation.
This way you basically create one big job which executes in a single workspace. So whatever you define in job1 will be available for the included jobs (job2 & job3).
You might want to set "source2" option as well.

If you have to loop - meaning running job2 & job3 several times with different parameters - then you would need a loop transformation. You possibly would create a control table (one row per parameter value) as input for the loop transformation, under parameter mapping you would assign this control table field to a macro variable, and you would have to parameterize job2 & job3 (done via a hidden prompt which uses the macro variable defined in the loop transformation). Message was edited by: Patrick
SAS Employee
Posts: 13

Re: HOW TO: Assign a column value to a global variable

Have you tried CURRVAL? it returns the current value of a sequence. here is an example.

create sequence seq_customers;

select seq_customers.nextval from dual;
NEXTVAL
----------------------
2
select seq_customers.currval from dual;
CURRVAL
----------------------
2
Ask a Question
Discussion stats
  • 7 replies
  • 455 views
  • 0 likes
  • 5 in conversation