DATA Step, Macro, Functions and more

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

Reply
New Contributor
Posts: 4

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

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.

Super User
Super User
Posts: 6,499

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

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 ;

New Contributor
Posts: 4

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

Thanks a lot Tom. I'll try it.

Once again, many thanks.

Valued Guide
Posts: 2,174

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

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

SAS Employee
Posts: 15

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

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.

New Contributor
Posts: 4

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

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

Ask a Question
Discussion stats
  • 5 replies
  • 444 views
  • 1 like
  • 4 in conversation