A question on writing a macro which can handle any number of parameter - Thanks

[need_sas_help]

Thnaks Reza. It is picking up the sale_1 .... sale_n dataset. then is there any problem with do loop? you can find the long created by running your debug code.

920

921

922

923 %let lib=%upcase(work);

924 %let set=%upcase(sale_);

925 proc sql;

926 select memname into : indatasets separated by ' '

927 from dictionary.tables

928 where libname="&LIB" and memname like "&set.%"

929 ;

930 quit;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.15 seconds

cpu time 0.01 seconds

　

931

932

933 %put &indatasets;

SALE_1 SALE_2 SALE_3 SALE_4 SALE_5 SALE_6 SALE_7

[Domenico]

The macro itself seems to be fine, but your libname is not library!
You should call your macro with the libname work.
Try it this way.

%fq(lib=work, set=sale_);

[need_sas_help]

Thanks Domenico. I tried that way too. For some reason the code is not picking up sale_1.... sale7 though they are in the work directory. Also Do Loop has a issue. Please get the log as below after running your suggested code:

553 %let lib=%upcase(&lib);

554 %let set=%upcase(&set);

555 proc sql;

556 select memname into : indatasets separated by ' '

557 from dictionary.tables

558 where libname="&LIB" and memname like "&set.%"

559 ;

560 quit;

561

562 %let NumSets = %sysfunc(countw(&InDataSets));

563

564 %do i = 1 %to &NumSets;

565 %let inset= %scan(&indatasets,&i);

566 proc sort data = &inset

567 out = sale_&i;

568 by part_number;

569 run;

570 %end;

571

572 Data total_sale;

573 merge

574 %do &i = 1 %to &numsets;

575 Sale_&i (in=in&i)

576 %end;

577 ; /* this ; ends the merge statement*/

578 by part_number;

579 run;

580

581 %mend;

582 %fq(lib=work, set=sale_);

NOTE: Line generated by the invoked macro "FQ".

1 proc sql; select memname into : indatasets separated by ' ' from dictionary.tables

-------

395

76

1 ! where libname="&LIB" and memname like "&set.%" ; quit;

ERROR: File WORK.PROC.DATA does not exist.

ERROR: File WORK.SQL.DATA does not exist.

NOTE: Line generated by the invoked macro "FQ".

1 proc sql; select memname into : indatasets separated by ' ' from dictionary.tables

1 ! where libname="&LIB" and memname like "&set.%" ; quit;

----

180

ERROR 395-185: Opening parenthesis for SELECT/WHEN expression is missing.

ERROR 76-322: Syntax error, statement will be ignored.

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent symbolic reference INDATASETS not resolved.

WARNING: Apparent symbolic reference INDATASETS not resolved.

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.TOTAL_SALE may be incomplete. When this step was stopped there were 0

observations and 0 variables.

NOTE: DATA statement used (Total process time):

real time 10:19.65

cpu time 1.75 seconds

　

　

ERROR: File WORK.INDATASETS.DATA does not exist.

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.SALE_1 may be incomplete. When this step was stopped there were 0

observations and 0 variables.

WARNING: Data set WORK.SALE_1 was not replaced because this step was stopped.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

　

　

ERROR: 2 is an invalid macro variable name for the index variable of the %DO loop.

ERROR: The macro FQ will stop executing.

583 /* library should be the name of your library you want to use, set is the "stem" of the data

583! sets you want to sort/ merge*/

[ballardw · Accepted solution]

Try this version of the macro:

BUT first if you run in the same session as the previous version execute: ; run;

as you likely have a data step still running

%macro fq(lib=, set=);
%let lib=%upcase(&lib);
%let set=%upcase(&set);
proc sql noprint;
   select memname into : indatasets separated by ' '
   from dictionary.tables
   where libname="&LIB" and memname like "&set.%"
   ;
quit;

%let NumSets = %sysfunc(countw(&InDataSets));

%do i = 1 %to &NumSets;
   %let inset= %scan(&indatasets,&i);
   %let inset= &lib..&inset; /* added this to look in the right library*/
   proc sort data = &inset
      out = sale_&i;
      by part_number;
   run;
%end;

Data total_sale;
   merge
   %do i = 1 %to &numsets; /* and this had &I instead of I so was incrementing incorrectly*/
      Sale_&i (in=in&i)
   %end;
   ; /* this ; ends the merge statement*/
   by part_number;
run;

%mend;

[need_sas_help]

worked. thanks ballardw. Now I can use your code in a very very large macro. thanks

[ballardw]

I will say that hard coding Part_number isn't the best, it could be set as the default for a keyword parameter for a variable name, but if you're happy that's what counts.

[Patrick]

Below code not adding something new except for a bit "robustness" eventually helping you to avoid running into issues. For example: There is a LIKE operator in the SQL used to search for tables matching your table naming pattern. The possible issue: Your table pattern has underscores - but in a SQL Like expression an underscore is a placeholder for any character so it would also match stuff you don't want.

options msglevel=I;

data

  fin_sale_1 (keep=part_number var ds1)

  fin_sale_2 (keep=part_number var ds2)

  fin_sale_3 (keep=part_number var ds3)

  fin_sales_total

  ;

  do part_number=1,9,4;

    var+1;

    ds1='fin_sale_1';

    output fin_sale_1;

  end;

  do part_number=4,6,3,20;

    var+1;

    ds2='fin_sale_2';

    output fin_sale_2;

  end;

  do part_number=9,6,4;

    var+1;

    ds3='fin_sale_3';

    output fin_sale_3;

    output fin_sales_total;

  end;

run;

%macro fq(inlib=, inds_pattern=, key=part_number, outtbl=work.total_sale);

  /* create list of tables in a library following a naming pattern */

  proc sql feedback noprint;

    select cats(libname,'.',memname) into :intbl_list separated by ' '

    from dictionary.tables

      where libname="%upcase(&inlib)" and prxmatch("/^&inds_pattern/oi",memname) >0

    order by memname

    ;

    %put intbl_list: &intbl_list;

  quit;

  /* prep SAS WORK: clean out left overs from previous runs */

  proc datasets lib=&inlib mt=(data view) noprint nowarn;

    delete __tmp_:;

    run;

  quit;

  /* create sorted work tables from selected source tables */

  %let NumSets = %sysfunc(countw(&intbl_list,%str( )));

  %do i = 1 %to &NumSets;

    proc sql feedback;

      create table __tmp_%sysfunc(putn(&i,z4.)) as

        select *

        from %scan(&intbl_list,&i,%str( ))

        order by &key

      ;

    quit;

  %end;

  /* merge all sorted work tables */

  Data &outtbl;

    merge __tmp_:;

    by &key;

    output;

  run;

  /* keep SAS WORK nice and tidy: clean out work tables no more required */

  proc datasets lib=&inlib mt=(data view) noprint nowarn;

    delete __tmp_:;

    run;

  quit;

%mend;

%fq(inlib=work, inds_pattern=fin_sale_);

[Tom]

Not sure what the real question is here.

The example program probably just needs to use PROC TRANSPOSE rather than the complex code presented.

Otherwise the issue sounds like you just need to code a %DO loop in your macro.

%macro mytest(ps);

%local i ;

%do i=1 %to &ps ;

   .... &i .....

%end;

%mend mytest ;

%mytest(6);