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

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

finsangel — Thu, 27 Jun 2013 13:46:20 GMT

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

...

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.

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

Tom — Thu, 27 Jun 2013 14:32:11 GMT

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 ;

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

finsangel — Thu, 27 Jun 2013 14:47:37 GMT

Thanks a lot Tom. I'll try it.

Once again, many thanks.

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

Peter_C — Fri, 28 Jun 2013 08:24:49 GMT

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

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

Vladislaff — Fri, 28 Jun 2013 14:16:24 GMT

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.

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

finsangel — Fri, 28 Jun 2013 15:18:53 GMT

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