CODEWITHSUNDEEP
sassas-macro
souyiro2004
souyiro2004

Reputation: 23

SAS Macro and arrays for tables names

i am new into SAS, i am looking at this code: 

    libname FINAL 'C:\PERM';
    %LET PERM_DB = C:\PERM_DB.ACCDB;
    %LET PASS   = 1234;
    %LET EXTRA1 = _A;


            %MACRO PERM(tablename);

            PROC IMPORT OUT= FINAL.&tablename&EXTRA1 DATATABLE= "&tableACCESS&EXTRA1" 
            DBMS=ACCESS REPLACE;
            DATABASE="&PERM_DB"; 
            DBPWD="&PASS"; SCANMEMO=YES; USEDATE=NO; SCANTIME=YES; RUN;

            %MEND PERM;

            %PERM(table1);
            %PERM(table2);
            %PERM(table3);
            %PERM(table4);
            %PERM(table5);
            %PERM(table6);
            %PERM(table7);
            %PERM(table8);
            %PERM(table9);
            %PERM(table10);

i was thinking instead of repeating so many times the macro, to create an array and a loop to call it.

    Data test;
    array perm{*} table1, table2,table3,table4,table5,table6,table7,table8,table9,table10;

    max = DIM(perm);

    DO i=1 to max;
    %PERM(perm(i));
    END;
run;

But it does not work, i get this error in the log:

> > 1      FINAL.perm(1)_A
> >                   -
> >                   22
> >                     --
> >                     22
> >                     202  ERROR 22-7: Invalid option name 1.
>  
> > ERROR 22-322: Syntax error, expecting one of the following: ;,  DATAFILE, DATATABLE, DBMS, DEBUG,
>                FILE, OUT, REPLACE, TABLE, _DEBUG_.
>  
> > ERROR 202-322: The option or parameter is not recognized and will be  ignored.

I am testing it with the value 1 in the array.

Any help or advice would be great.

The output should be something like:

FINAL.table1_A

but i am getting

FINAL.perm(1)_A

Thank You.

Upvotes: 2

Views: 382

Answers (1)

Joe
Joe

Reputation: 63424

There's a handful of ways to deal with this. Search for "Data driven programming", for example. I'll show two: my preferred one, and the one a lot of people would suggest.

First, the popular solution would be to do a macro loop. Properly, I would write a macro with min and max parameters, like this:

%macro perm(tablename);
  %put &tablename;
%mend perm;

%macro import_data(min=1,max=);
  %do _i = &min. %to &max.;
    %perm(table&_i.);
  %end;
%mend import_data;

%import_data(min=1,max=10);

Obviously with your real %perm not my example one. It loops over the %perm calls from &min. to &max..

Second, truly data driven I would say is best in many cases. This assumes you have a table somewhere with one row per table you want to import - though you could equally do the above solution with this.

Let's say you have an excel file, for example, and it has some sheets you want to import. You can use dictionary.tables to get a list of these sheets and automatically import every sheet.

libname imports excel "c:\temp\test_import.xlsx";

proc sql;
  select cats('%perm(',memname,')')
    into :calllist separated by ' '
    from dictionary.tables
    where libname='IMPORTS';
quit;

&calllist.

(this assumes %perm knows how to use the sheetname to import the data.) If you're importing from another source, you may have access to similar metadata - either via SAS's connection via dictionary.tables or local metadata like in Oracle, for example.

Upvotes: 1

Related Questions