topic Re: A question on writing a macro which can handle any number of parameter - Thanks in SAS Programming

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

need_sas_help — Thu, 16 Oct 2014 21:24:19 GMT

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.

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

ShirleyInChchNZ — Thu, 16 Oct 2014 22:17:06 GMT

%* 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);

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

ballardw — Thu, 16 Oct 2014 22:24:14 GMT

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.

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

need_sas_help — Fri, 17 Oct 2014 18:32:40 GMT

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.

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

need_sas_help — Fri, 17 Oct 2014 18:40:21 GMT

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

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

Tom — Fri, 17 Oct 2014 18:49:49 GMT

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);

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

need_sas_help — Fri, 24 Oct 2014 20:17:56 GMT

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.

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

Reeza — Fri, 24 Oct 2014 20:44:35 GMT

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.

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

Tom — Fri, 24 Oct 2014 20:47:07 GMT

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 ' '

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

Patrick — Sat, 25 Oct 2014 08:27:02 GMT

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_);

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

need_sas_help — Mon, 27 Oct 2014 20:14:31 GMT

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.

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

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

need_sas_help — Mon, 27 Oct 2014 20:15:45 GMT

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

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

Tom — Mon, 27 Oct 2014 20:35:12 GMT

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 .

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

need_sas_help — Mon, 27 Oct 2014 21:02:44 GMT

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.

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.

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

Tom — Mon, 27 Oct 2014 21:05:31 GMT

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.

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

Reeza — Mon, 27 Oct 2014 21:07:12 GMT

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;

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

Domenico — Tue, 28 Oct 2014 15:29:51 GMT

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_);

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

need_sas_help — Tue, 28 Oct 2014 18:29:31 GMT

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.

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

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

need_sas_help — Tue, 28 Oct 2014 18:33:06 GMT

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

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.

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*/

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

need_sas_help — Tue, 28 Oct 2014 20:06:56 GMT

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