topic Re: Parameterization in SAS DI in SAS Data Management

Parameterization in SAS DI

kandasamy — Mon, 11 Jun 2012 16:40:31 GMT

Hi All,

My Requirement is, I need to Parameterize the location of the source files in a parameter file and need to read the file location from the Parameterized file in the SAS DI.

Please let me know how to create a parameter file and read the parameter file in the SAS DI.

I am using SAS DI 4,2

Please guide me.

Thanks!

Regards

Samy

Re: Parameterization in SAS DI

LinusH — Tue, 12 Jun 2012 06:30:18 GMT

http://support.sas.com/documentation/cdl/en/etlug/62233/HTML/default/viewer.htm#p1cqcd3acjwls2n0z9lmzv2dvo5w.htm

Re: Parameterization in SAS DI

shivas — Tue, 12 Jun 2012 09:01:42 GMT

Hi Samy,

How about a creating a global macro parameters and call that in Pre-Process Code in SAS DI.

Thanks,

Shiva

Re: Parameterization in SAS DI

kandasamy — Fri, 15 Jun 2012 12:24:39 GMT

Hi Shiva,

Thanks for your reply.

My understand is If we are creating the global macro variable we can use in the Same job.

But my requirement is we are having the source files and the physical file path for the source files will be different for Dev and Production box. So I am think to create a parameter file where I can defined the filename path and I can calling it in the Job.

Could you please guide me on this.

Regards

Kandasamy

Re: Parameterization in SAS DI

LinusH — Fri, 15 Jun 2012 12:34:15 GMT

http://support.sas.com/documentation/cdl/en/etlug/62233/HTML/default/viewer.htm#p1cqcd3acjwls2n0z9lmzv2dvo5w.htm

Re: Parameterization in SAS DI

shivas — Fri, 15 Jun 2012 12:58:12 GMT

Hi Samy,

You can just give a try...what I am proposing is make the below code into macro and add in autocall library and invoke this macro in the preprocess code in SAS DI.

/*input file

source,path

customer,"F:\ALM_DATA23Aug\customer.csv"

Account,"F:\ALM_DATA23Aug\Account.csv"

data WORK.path ;

infile 'F:\ALM_DATA23Aug\Path.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;

informat source $8. ;

informat path $31. ;

format source $8. ;

format path $31. ;

input

source $

path $

;

run;

proc sql;

create table work.parameter as

select distinct source, path

from path;

quit;

data _NULL_ ;

set work.parameter;

call execute('%global ' || trim(source));

call execute('%let ' || trim(source) || '=' || trim(path));

run;

%put &customer &account;

Thanks,

Shiva

Re: Parameterization in SAS DI

kandasamy — Fri, 15 Jun 2012 17:42:13 GMT

Hi Shiva

Thank you so much for your help

We have included the code given by you by making necessary changes according to our enovironment in File Reader Transformation precode and in actual source file properties we have given as &Client (like &customer or &account) instead giving the entire physical path of the file in SAS DI job, but we are facing the below error as

'ERROR : Expecting a variable name after %LET'

we have also done changes in code as by removing the call execute command in the below statement

%let trim(source) = trim(path) instead of below statement

/* call execute('%let ' || trim(source) || '=' || trim(path)); */

but we got the error in SAS as below

Physical file does not exist /opt/xxx/xxxxx/yyyy/&Client , so its not substituting the parameter value

Please help us to sort out this issue

Thanks

Kandasamy

Re: Parameterization in SAS DI

shivas — Sat, 16 Jun 2012 12:01:58 GMT

Hi,

First step: Created macro TEST and saved in autocall library(parameter file).

%macro test;

LIBNAME ALM BASE "path";

data _NULL_ ;

set ALM.parameter;

call execute('%global ' || trim(source));

call execute('%let ' || trim(source) || '=' || trim(path));

run;

%mend test;

Step two: create job and call macro test in preprocess code of the job;

/*---- Start of Pre-Process Code ----*/

%test

/*---- End of Pre-Process Code ----*/

Step three: please see below job and parameter calling in the job.

Hope this is clear and helps you...or the way i have understand the problem is wrong please let me know with some sample data.

Thanks,

Shiva

Re: Parameterization in SAS DI

Patrick — Sun, 17 Jun 2012 08:39:26 GMT

I would assume (and hope) that the general folder structures look the same in all environments. What will be different are things like Lev1, Lev2 and the like.

I prefer to create a couple of macro variables (eg: &Lev) in the usermods autoexec which I then use within my DI jobs. This needs only an initial setup in the autoexec but no further coding and changes in the DI jobs - just use the macro variables there.

Doing it this way makes it also very easy to move folder structures (should this be necessary) as it only needs a central amendment of autoexec code.

Re: Parameterization in SAS DI

kandasamy — Mon, 18 Jun 2012 18:14:58 GMT

Thank you so much Shiva 🙂

It worked, i did the following

1) Just changed the code as strip instead of trim

2) and i given the parameter with double quotes as you specified in the picture

The code looks like below

/*********************************** Start of SAS Code ***********************************/

data WORK.path;

infile '/sasdata/env3/lev1/data/BRM/Paremeter_Check.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;

informat source $8. ;

informat path $100. ;

format source $8. ;

format path $100. ;

input source $ path $;

run;

proc sql;

create table work.parameter as select distinct source,path from path; quit; data _NULL_ ;

set work.parameter;

call execute ('%global ' || strip(source));

call execute ('%LET ' || strip(source) || '=' || strip(path));

run;

%put &Client &Policy;

/*********************************** End of SAS Code ***********************************/

and in where condition i have given "&Client" Thank you so much

Re: Parameterization in SAS DI

kandasamy — Mon, 18 Jun 2012 18:17:25 GMT

Thank you so much patrick

The copied in code in the above post, which resolves the issue.