Parameterization in SAS DI

[kandasamy]

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

[LinusH]

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

[shivas]

Hi Samy,

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

Thanks,

Shiva

[kandasamy]

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

[LinusH]

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

[shivas]

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

[kandasamy]

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

[shivas · Accepted solution]

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

[kandasamy]

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

[Patrick]

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.

[kandasamy]

Thank you so much patrick

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