DI Studio Parameter From SAS Dataset

[jwhite]

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

[LinusH]

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.

[jwhite]

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]

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

@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 · Accepted solution]

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

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