topic Re: How to pass list of parameters to variables in SAS Programming

How to pass list of parameters to variables

jayakumarmm — Sun, 04 Sep 2016 21:44:19 GMT

I need to pass a list of paramters as variable in SAS program. Given below is the logic

Filename = ('File1','File2',file3');

sas_dataset = ('data1','data2','data3');

Excel_table = ('table1','table2',table3');

libname xlsFile XLSX "/user/&Filename.";


options validvarname=v7;
PROC SQL;
    create table work.&SAS_dataset. as 
	(select * from xlsFile.&Excel_table.);
 quit;

Re: How to pass list of parameters to variables

Reeza — Sun, 04 Sep 2016 21:56:07 GMT

You need a macro loop and you need to retrieve each value from the list iteratively.

This example explains how to loop through a list. You can place your code within the macro block.

https://support.sas.com/documentation/cdl/en/mcrolref/67912/HTML/default/viewer.htm#p1n2i0ewaj1zian1ria5579z1zjh.htm

Give it a shot and if you have issues post the code you've tried and we can help with any debugging.

Re: How to pass list of parameters to variables

jayakumarmm — Mon, 05 Sep 2016 02:58:26 GMT

Thank you for sharing the link. I am able proceed now expect the below logic. Variables are not getting replaced with respective values.i.e. &dataset and &dataset_tera while creating teradata table with SAS dataset

Error message: data schema.Table1_&dataset ( dbcreate_table_opts='primary index(col1,col2)'
___________________
22
! fastload=yes sleep=1 tenacity=2 sessions=10 ); set WORK.&dataset_tera; run;
ERROR 22-7: Invalid option name DBCREATE_TABLE_OPTS.

%let Filename1 = FILE1 FILE2;
%let SAS_DATASET = DATA1 DATA2;
%let Excel_table = TABLE1 TABLE2;
%let SAS_DATASET_TERA = TERA1 TERA1;

options validvarname=v7;

%macro file_process_tera;
%let word_cnt = %sysfunc(countW(&Filename1));
%do  i = 1 %to &word_cnt;
%let file_name=%qscan(%bquote(&Filename1),&i);
%let dataset=%qscan(%bquote(&SAS_DATASET),&i);
%let table=%qscan(%bquote(&Excel_table),&i);
%let dataset_tera=%qscan(%bquote(&SAS_DATASET_TERA),&i);

data Schema.TABLE1_&dataset
   (  
    dbcreate_table_opts='primary index(col1,col2)' 
      fastload=yes sleep=1 tenacity=2 sessions=10 
      );
    set WORK.&dataset_tera;
run;
%end;
%mend file_process_tera;
%file_process_tera;

Re: How to pass list of parameters to variables

Reeza — Mon, 05 Sep 2016 03:01:21 GMT

Make sure to run with debugging options on:

Options SYMBOLGEN MPRINT;

I'm fairly certain your libname is the issue. You need two periods after the macro variable. One to denote the end of the macro variable and one for the XLSM extension. But that also doesn't make sense because the macro variable has two values.

Though you dont appear to use the libname so I'm not sure what's going on or why the code is there.

Run the code with the options to see the generated code. It needs to be valid SAS code.

Re: How to pass list of parameters to variables

jayakumarmm — Mon, 05 Sep 2016 03:39:23 GMT

Thank you for your repsonse. I have libname but I missed it while sharing. Error is thrown in this line

(dbcreate_table_opts='primary index(col1,col2)' fastload=yes sleep=1 tenacity=2 sessions=10);

Error message:

22: LINE and COLUMN cannot be determined.
NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
ERROR 22-7: Invalid option name DBCREATE_TABLE_OPTS.
22: LINE and COLUMN cannot be determined.
NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
ERROR 22-7: Invalid option name FASTLOAD.
22: LINE and COLUMN cannot be determined.
NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
ERROR 22-7: Invalid option name SLEEP.
22: LINE and COLUMN cannot be determined.
NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
ERROR 22-7: Invalid option name TENACITY.
22: LINE and COLUMN cannot be determined.
NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
ERROR 22-7: Invalid option name SESSIONS.

Re: How to pass list of parameters to variables

jayakumarmm — Mon, 05 Sep 2016 04:42:49 GMT

Issue is fixed now after concatenating the teradata table separately in a variable.

Re: How to pass list of parameters to variables

Reeza — Mon, 05 Sep 2016 04:43:10 GMT

I'm not familiar with that option.

What you should do is make sure you have working code and then switch it over to a macro version.