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.
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 ;
Thanks a lot Tom. I'll try it.
Once again, many thanks.
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
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.
Thanks a lot everyone, good to learn a lot of ways to do it
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.