BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jwhite
Quartz | Level 8

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 &parameter 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 &parameter within this job it would have the year value I pointed to on that other table.

 

Thanks,

-Jeff

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@jwhite

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;

 

View solution in original post

6 REPLIES 6
LinusH
Tourmaline | Level 20

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.

Data never sleeps
jwhite
Quartz | Level 8

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

Patrick
Opal | Level 21

@jwhite

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.

 

jwhite
Quartz | Level 8

@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!

Patrick
Opal | Level 21

@jwhite

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;

 

jwhite
Quartz | Level 8

@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];

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 6 replies
  • 1891 views
  • 1 like
  • 3 in conversation