BookmarkSubscribeRSS Feed
finsangel
Calcite | Level 5

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.

5 REPLIES 5
Tom
Super User Tom
Super User

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
Calcite | Level 5

Thanks a lot Tom. I'll try it.

Once again, many thanks.

Peter_C
Rhodochrosite | Level 12

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
SAS Employee

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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 2964 views
  • 1 like
  • 4 in conversation