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

DI Studio Parameter From SAS Dataset

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 105
Accepted Solution

DI Studio Parameter From SAS Dataset

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


Accepted Solutions
Solution
‎04-20-2018 10:16 AM
Respected Advisor
Posts: 4,702

Re: DI Studio Parameter From SAS Dataset

@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


All Replies
Super User
Posts: 5,853

Re: DI Studio Parameter From SAS Dataset

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
Frequent Contributor
Posts: 105

Re: DI Studio Parameter From SAS Dataset

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

Respected Advisor
Posts: 4,702

Re: DI Studio Parameter From SAS Dataset

[ Edited ]

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

 

Frequent Contributor
Posts: 105

Re: DI Studio Parameter From SAS Dataset

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

Solution
‎04-20-2018 10:16 AM
Respected Advisor
Posts: 4,702

Re: DI Studio Parameter From SAS Dataset

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

 

Frequent Contributor
Posts: 105

Re: DI Studio Parameter From SAS Dataset

@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];
☑ This topic is solved.

Need further help from the community? Please ask a new question.

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