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.
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;
%* 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);
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.
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.
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
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.
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.
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
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 ' '
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
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 .
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.
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.
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: