06-27-2013 09:46 AM
I am quite new on SAS. Now I have a problem of importing excel into SAS. I have a worksheet in which I have multiple ranges of rating coeffs. Now I was supposed to import these ranges of table one by one. But now I want to have a macro function which can help import these tables in a row. What I have done right now is a macro function like this:
%macro import_table(name_of_table, range_limit1, range_limit2);
proc import datafile= "C:\...\myfile\rating_table.xlsm"
out = table_&name_of_table dbms=excelcs replace;
This macro function works well. As example %import_table(rate1,A2,BI8) can import the this table successfully. In the meantime, I also have imported a data sheet into SAS in which we have information about different range limits for each rating table. I have imported something like this(named abcd),
indice name_of_table limit1 limit2
1 aa A2 BI8
2 bb A10 BI50
3 cc A52 BI 240
So this data sheet includes 22 rows. What I want is to create a macro function to import automatically all these rating tables in a row. I wanted to use the data sheet abcd to get range limits and table name for each table, then use these three values in the macro function import_table.
So, I'd like to have something like,
do i=1 to 22;
import_table(name_of_table, limit1, limit2);
Is it possible to have something like that? I tried a lot but I am not able to achieve....
Many thanks for your help.
06-27-2013 10:32 AM
You want to use the data to generate the macro calls.
There are many ways to do this, here are a copy
1) Use CALL EXECUTE;
call execute( cats( '%import_table(',catx(',',name_of_table,limit1,limit2),')' );
2) Write the code to a file and %INCLUDE it.
filename code temp;
put '%import_table(' name_of_table '.' limit1 ',' limit2 ')' ;
%include code / source2 ;
06-28-2013 04:24 AM
If you define those ranges with names in the excel workbook, you should be able to proc copy from the workbook straight into WORK library with code like:
libname wkbk "your\workbook.xlsx" access= readonly;
proc copy in= wkbk out= WORK mt= data ;
select &range_list ;
Just make sure that the rangenames comply with SAS rules for sas dataset names and also beware that the names are case-sensitive in that SELECT statemenr.
SAS could fill that range_list for you, but you probanly don't need that extra code when you already know what to expect.
06-28-2013 10:16 AM
Try something like this:
%let dsid = %sysfunc(open(abcd,i));
%do %while (%sysfunc(fetch(&dsid))=0);
%let name_of_table = %sysfunc(getvarc(&dsid,%sysfunc(varnum(&dsid,name_of_table))));
%let limit1 = %sysfunc(getvarc(&dsid,%sysfunc(varnum(&dsid,limit1))));
%let limit2 = %sysfunc(getvarc(&dsid,%sysfunc(varnum(&dsid,limit2))));
%import_table(&name_of_table, &limit1, &limit2);
Dont forget to add different checks like for existance of the dataset etc.