When building a job in DI Studio, I require a job level parameter. However, I don't want to simply hardcode a default but read a value (year) off of a SAS dataset.
When opening the 'New Prompt' in job level properties, and selecting the 'Prompt Type and Values' tab, I can select 'Data source item' as a Prompt Type. This enables me to browse to the desired SAS table as a data source, select a data type (numeric), then select one of the numeric columns off the table. This all seems to appear as if I've selected a dataset, and a specific column off of it.
This is exactly what I need.
However, when I run a job, use the ¶meter as the value of a workfile column (to test) I get a message that:
"The following columns were not found in the contributing tables: [column_name]"
What have I missed? I would expect any time I use ¶meter within this job it would have the year value I pointed to on that other table.
Thanks,
-Jeff
That's how you can create and populate a macro variable from data:
/** option 1 **/
%let age=;
proc sql noprint;
select put(age,5. -l) into :age
from sashelp.class
where name='Alfred'
;
quit;
%put Age is: &age;
/** option 2 **/
%let age2=;
data _null_;
set sashelp.class;
if name='Alfred';
call symputx('age2',put(age,5.));
stop;
run;
%put Age2 is: &age2;
Hi,
I feel your confusion. But as far as my understanding, prompts in DI Studio is of use only when you are deploying a job as a Stored Process. There is no prompts functionality when you are running a job within DI Studio client, not in batch.
If you select a default value I assume you'll get a proper value during execution.
If you want to feed your job with a value based on a lookup table there are several other options:
- Use a loop transform, which will et you feed the inner (original) job with a parameter table.
- Use a logic in autoexec or Pre-process to read the table and assign thr macro variable
- Have the parameter table as a part of the job and lookup/join in it'sd value. Not very neat since there's probably no join column.
Hi Linus,
Unfortunately, we don't deploy our jobs as Stored Processes.
I am curious...instead of building a macro, can we build code in a User Written Code transformation and include that transformation in the job. Code within that transformation that would go look at a year value on a SAS dataset and load it as a job level parameter to be used in any needed nodes within the job?
Thanks,
-Jeff
It's the LOOP transformation which allows you OOTB to use a control table and then call an inner job once per observation in the control table passing the values as parameters to the inner job.
If using a scheduler/batch command then you can also pass in parameters via the batch command line (within the code then available as macro variable).
....or you just have a user written code node (or if used in multiple jobs a custom transformation) which reads your table and creates a macro variable for later use.
@Patrick, it's the latter option that I'm interested in doing...creating a transformation that will read a table and create a macro variable that can be used as a parameter in jobs.
I'm not a SAS programmer by trade (yet), so still working through the kinks on how to do it. Any tips? Particularly, in setting up a macro variable to be used as a parameter.
Cheers!
That's how you can create and populate a macro variable from data:
/** option 1 **/
%let age=;
proc sql noprint;
select put(age,5. -l) into :age
from sashelp.class
where name='Alfred'
;
quit;
%put Age is: &age;
/** option 2 **/
%let age2=;
data _null_;
set sashelp.class;
if name='Alfred';
call symputx('age2',put(age,5.));
stop;
run;
%put Age2 is: &age2;
@Patrick, thank you!
I was able to tweak that code a bit to get what I needed for our purpose. For anyone interested, I ended up with:
LIBNAME [libname] "[path]"; %let [variable]=; data _null_ ; set [libname].[table]; call symputx ('[variable]', put([table column], 8.)); run; %put value is: &[variable];
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!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.