Import different ranges of an excel worksheet in SAS(macro function)

[finsangel]

Hi everyone,

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;

               sheet="different_tables$"

              range= "&range_limit1:&range_limit2";

              run;

         %mend;

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

    4

    ...

    22

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,

%macro import_all();

          do i=1 to 22;

                import_table(name_of_table, limit1, limit2);

           end;

%mend;

Is it possible to have something like that? I tried a lot but I am not able to achieve....

Many thanks for your help.

[Tom]

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;

data _null_;

set abcd;

call execute( cats( '%import_table(',catx(',',name_of_table,limit1,limit2),')' );

run;

2) Write the code to a file and %INCLUDE it.

filename code temp;

data _null_;

  file code;

set abcd;

put '%import_table(' name_of_table '.' limit1 ',' limit2 ')' ;

run;

%include code / source2 ;

[finsangel]

Thanks a lot Tom. I'll try it.

Once again, many thanks.

[Peter_C]

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 ;

Run ;

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.

Good luck

pp

[Vladislaff]

Try something like this:

%macro import_all();

%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);

%end; 

%mend;

Dont forget to add different checks like for existance of the dataset etc.

[finsangel]

Thanks a lot everyone, good to learn a lot of ways to do it