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

[need_sas_help]

I have a macro as below where I am using parameter 1 to 6 [%fq (1) - %fq (6)]. But in real life I am not sure how many I am going to have and it can be any number.
I had hard-coded the marging statement [merge sale_1(in=a) sale_2(in=2) sale_3(in=3) sale_4(in=4) sale_5(in=5) sale_6(in=6)] upto to sales_6 dataset.
But it real world the number of dataset can be anything can be from sale_1 - sales_15. But always will keep the sequence 1,2,3,4,5,6,7,8,.........

%macro fq (ps);

Proc sort datat = fin_sale_&ps
          out = sale_&ps;
      by part_number;
run;

data total_sale;
merge sale_1(in=a) sale_2(in=2) sale_3(in=3) sale_4(in=4) sale_5(in=5) sale_6(in=6);
by part_number;
run;

%mend;
%fq (1);
%fq (2);
%fq (3);
%fq (4);
%fq (5);
%fq (6);

Is there anyway I can write such a macro where it can handle any number of datasets (sequestial) whatever is availabe in the source forlder.
Thank you so much for helpig me.

[ShirleyInChchNZ]

%* try this out - its not clear where you get saleNbr from;

%macro fq (ps=,saleNbr=);

Proc sort data = fin_sale_&ps
          out = sale_&ps;
      by part_number;
run;

data total_sale;
/*merge sale_1(in=a) sale_2(in=2) sale_3(in=3) sale_4(in=4) sale_5(in=5) sale_6(in=6);*/
merge %do i = 1 to &saleNbr.; sale_&i.(in=a&i.); %end;
   ; %* this semicolon ends the merge statement;
by part_number;
run;

%mend;

%* note differing saleNbr value;
%fq (ps=1,saleNbr=6);
%fq (ps=2,saleNbr=6);
%fq (ps=3,saleNbr=15);
%fq (ps=4,saleNbr=6);
%fq (ps=5,saleNbr=6);
%fq (ps=6,saleNbr=16);

[need_sas_help]

ShirleyInChchNZ

Thanks for this apporach. Problem is, I can not difine the vale for ps OR saleNbr. so I can not say: ps=1,saleNbr=6,  as I do not even know the maximum value for them.

[ballardw]

Something like this might work:

%macro fq(lib=, set=);
%let lib=%upcase(&lib);
%let set=%upcase(&set);
proc sql;
   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);
   proc sort data = &inset
      out = sale_&i;
      by part_number;
   run;
%end;

Data total_sale;
   merge
   %do &i = 1 %to &numsets;
      Sale_&i (in=in&i)
   %end;
   ; /* this ; ends the merge statement*/
   by part_number;
run;

%mend;
%fq(lib=library, set=fin_sale_); /* library should be the name of your library you want to use, set is the "stem" of the data sets you want to sort/ merge*/

I am hoping that by "folder" you meant SAS library.

[need_sas_help]

Looks good. But as an example I have provided just a very simple and small part of a giant macro. I will try to impliment the idea you have provided. Not sure if it will work though. Thnaks a lot

[need_sas_help]

Hi Ballaedw;

That was a brilliant solution. Actually I can use it for some many different macros. But I am having a lot of EROOR. Also I can not specify : indatasets separated by ' '. As I may have: Sale_1, Sale_2, Sale_3...........Sale_n. The pattern is always same but cannot hard code the program name as I do not know how many dataset will be like that in the directory. Sometimes the number of the datasets (same Pattern) can be 7 other time it can be 70. Need your help. If  I can use your code correctly it only solve my present issue but also will solve many other issues like this. Hope to hear from you.

PLEASE FIND THE ERROR AS BELOW:

343
344  %macro fq(lib=, set=);
345  %let lib=%upcase(&lib);
346  %let set=%upcase(&set);
347  proc sql;
348     select memname into : sale_1 sale_2
349     from dictionary.tables
350     where libname="&LIB" and memname like "&set.%"
351     ;
352  quit;
353
354  %let NumSets = %sysfunc(countw(&InDataSets));
355
356  %do i = 1 %to &NumSets;
357     %let inset= %scan(&indatasets,&i);
358     proc sort data = &inset
359        out = sale_&i;
360        by part_number;
361     run;
362  %end;
363
364  Data total_sale;
365     merge
366     %do &i = 1 %to &numsets;
367        Sale_&i (in=in&i)
368     %end;
369     ; /* this ; ends the merge statement*/
370     by part_number;
371  run;
372
373  %mend;
374  %fq(lib=WORK, set=fin_sale_); /* library should be the name of your library you want to use,
374! set is the "stem" of the data sets you want to sort/ merge*/
NOTE: Line generated by the invoked macro "FQ".
1     proc sql;    select memname into : sale_1 sale_2    from dictionary.tables    where
                          -------
                          395
                          76
1  ! 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 : sale_1 sale_2    from dictionary.tables    where
1  ! 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.
WARNING: Data set WORK.TOTAL_SALE was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           17:05.27
      cpu time            1.17 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.

THANK YOU SO MUCH FROM THE BOTTOM OF MY HEART.

[Reeza]

Tom, You modified his solution. Try it exactly how it is, rather than modified, it should deal with a generic amount of data sets but puts them into a list and then loops through the list. It doesn't matter if it's 70 or 7.

[need_sas_help]

Thanks Reeza. I tried but it gives an error. I just replied to Tom's message. You can find the detail there. Would you be kind enough to look at the detail?

Thanks again

[Tom]

Fix the PROC SQL step that is generating the member names of the actual datasets.  The proposed solution was to use the SEPARATED BY clause so that all of the names are stored in a single macro variable.

select memname into : sale_1 sale_2

becomes

select memname into : InDataSets separated by ' '

[need_sas_help]

Thanks Tom. But when I use the code exactly as same I find the following error:    Don't know how to fix it.

the details are as below:

　

718 %fq (7);

NOTE: Input data set is already sorted; it has been copied to the output data set.

NOTE: There were 56804 observations read from the data set WORK.SALE_7.

NOTE: The data set WORK.SALE_7 has 56804 observations and 14 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.51 seconds

cpu time 0.01 seconds

　

　

719

720 %macro fq(lib=, set=);

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

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

723 proc sql;

724 select memname into : indatasets separated by ' '

725 from dictionary.tables

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

727 ;

728 quit;

729

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

731

732 %do i = 1 %to &NumSets;

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

734 proc sort data = &inset

735 out = sale_&i;

736 by part_number;

737 run;

738 %end;

739

740 Data total_sale;

741 merge

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

743 Sale_&i (in=in&i)

744 %end;

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

746 by part_number;

747 run;

748

749 %mend;

750 %fq(lib=library, set=fin_sale_); /* library should be the name of your library you want to

750! use, set is the "stem" of the data sets you want to sort/ merge*/

NOTE: No rows were selected.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.07 seconds

cpu time 0.00 seconds

　

WARNING: Apparent symbolic reference INDATASETS not resolved.

WARNING: Apparent symbolic reference INDATASETS not resolved.

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

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    ERROR

ERROR: The macro FQ will stop executing.                                                                                                   ------------------------------------------------> ERROR    ERROR

[Tom]

So this line in your LOG is telling you that no actual dataset matched the selection criteria you provided.

NOTE: No rows were selected.

The other errors are just a result of the failure to locate any datasets.

Do you actually have any datasets with names like LIBRARY.FIN_SALE_ ?

The only dataset that you are showing in your log is named WORK.SALE_7 .

[need_sas_help]

Thank you so much Tom. Yes,  Sale_1.......sale_7 datasets are available. FIN_SALE_ is the dataset that we want to sorted was in SET parameter. Later I used SALE_ instead of  FIN_SALE_.But doesn't matter what I do, it's not working. Please look at the log beloe again:

1154

1155 %macro fq(lib=, set=);

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

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

1158 proc sql;

1159 select memname into : indatasets separated by ' '

1160 from dictionary.tables

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

1162 ;

1163 quit;

1164

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

1166

1167 %do i = 1 %to &NumSets;

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

1169 proc sort data = &inset

1170 out = sale_&i;

1171 by part_number;

1172 run;

1173 %end;

1174

1175 Data total_sale;

1176 merge

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

1178 Sale_&i (in=in&i)

1179 %end;

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

1181 by part_number;

1182 run;

1183

1184 %mend;

1185 %fq(lib=library, set=sale_); /* library should be the name of your library you want to use,

1185! set is the "stem" of the data sets you want to sort/ merge*/

NOTE: No rows were selected.                                 ---------------------------------- No dataset could read

NOTE: PROCEDURE SQL used (Total process time):

real time 0.15 seconds

cpu time 0.01 seconds

　

WARNING: Apparent symbolic reference INDATASETS not resolved.

WARNING: Apparent symbolic reference INDATASETS not resolved.

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

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.09 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.

　

　

　

You are s much helpful Tom.

　

[Tom]

Have you even defined a libname of LIBRARY any where in your program?  In your previous log the only dataset was in the WORK library and not the LIBRARY library.

[need_sas_help]

Thank you so much Tom. No, Work library was not the issue as by mistakely I had pasted a typo. Please take a look at the below. For sine reason it is not picking up the variables sale_1.......sale_7, Also the Do Loop Macro may have a issue:

The Log is below:

　

NOTE: There were 56804 observations read from the data set WORK.SALE_7.

NOTE: The data set WORK.SALE_7 has 56804 observations and 14 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.90 seconds

cpu time 0.10 seconds

　

516

517

518

519 %macro fq(lib=, set=);

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

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

522 proc sql;

523 select memname into : indatasets separated by ' '

524 from dictionary.tables

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

526 ;

527 quit;

528

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

530

531 %do i = 1 %to &NumSets;

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

533 proc sort data = &inset

534 out = sale_&i;

535 by part_number;

536 run;

537 %end;

538

539 Data total_sale;

540 merge

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

542 Sale_&i (in=in&i)

543 %end;

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

545 by part_number;

546 run;

547

548 %mend;

549 %fq(lib=work, set=fin_sale_); /* library should be the name of your library you want to use,

549! set is the "stem" of the data sets you want to sort/ merge*/

NOTE: No rows were selected.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.35 seconds

cpu time 0.00 seconds

　

WARNING: Apparent symbolic reference INDATASETS not resolved.

WARNING: Apparent symbolic reference INDATASETS not resolved.

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.12 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.

Thanks a again

[Reeza]

Consider this practice in macro debugging. Remove the macro loop and see if the first part works on it's own. Test each section piece by piece and then move on from there.

IE for the first step, does this work:

%let lib=%upcase(work);

%let set=%upcase(sales);

proc sql;

select memname into : indatasets separated by ' '

from dictionary.tables

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

;

quit;

%put &indatasets;