BookmarkSubscribeRSS Feed
jayakumarmm
Quartz | Level 8

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;

 

6 REPLIES 6
Reeza
Super User

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#p1n2i0ewaj1zian1...

 

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

jayakumarmm
Quartz | Level 8

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;

 

Reeza
Super User

 

 

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. 

 

jayakumarmm
Quartz | Level 8

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.

 

 

 

 

jayakumarmm
Quartz | Level 8

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

Reeza
Super User

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. 

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 3727 views
  • 1 like
  • 2 in conversation