Use SAS macro to generate multiple output files for the same number of input files

[nazmul]

Dear Everyone,

I want to run the following codes for 31 files including S1 to S31 and want to get 21 output files including O1 to O31. I highlighted the locations of both input and output files in the code. I know I should use macro to perform the job. Could you please suggest me how should I write the macro code to solve the problem?

proc nlp noprint data=S1 absgconv=1e-15 OUTEST=T1; by obs;
bounds R>=0, R<=1;
min f;
decvar R=1;
f = abs(PRC1_IBES-(BVPS1_1+(((FEPS1_1/BVPS1_1_1)-R)/(1+R))*BVPS1_1
+(((FEPS2_1/BVPS2_1_1)-R)/((1+R)**2))*BVPS1_1_1
+(((FEPS3_1/BVPS3_1_1)-R)/((1+R)**3))*BVPS2_1_1
+(((FEPS4_1/BVPS4_1_1)-R)/((1+R)**4))*BVPS3_1_1
+(((FEPS5_1/BVPS5_1_1)-R)/((1+R)**5))*BVPS4_1_1
+(((FEPS6_1/BVPS6_1_1)-R)/((1+R)**6))*BVPS5_1_1
+(((FEPS7_1/BVPS7_1_1)-R)/((1+R)**7))*BVPS6_1_1
+(((FEPS8_1/BVPS8_1_1)-R)/((1+R)**8))*BVPS7_1_1
+(((FEPS9_1/BVPS9_1_1)-R)/((1+R)**9))*BVPS8_1_1
+(((FEPS10_1/BVPS10_1_1)-R)/((1+R)**10))*BVPS9_1_1
+(((FEPS11_1/BVPS11_1_1)-R)/((1+R)**11))*BVPS10_1_1
+(((FEPS12_1/BVPS12_1_1)-R)/(R*(1+R)**11))*BVPS11_1_1));
run;

data T1; set T1;
if _TYPE_='PARMS';
rename R=rgls1_1_1;
keep obs R; run;
data O1; merge S1 (in=xx) T1(in=yy); by obs; if xx and yy; run;

I appreciate your help.

[unison · Accepted solution]

Without sample data, there's not much we can do to help with your specific need..

Here's an outline of an approach to solve your problem.

Create your input data (INSETS):

*Number of datasets;
%let num_dsets=31;
*No sample data -- create 31 INSETS;

data _null_;
	do i=1 to &num_dsets;
		num=put(i, z2.);
		call execute(cats('data INSET_', num, ';', 
			'NONSENSE = "Hello from Dataset #', num, '";', 'RAND_NUM = ranuni(', i, 
			');', 'run;'));
	end;
run;

Write macro to do what you want with each INSET:

*Write macro to do something;

%macro dosomething(dsin=, num=);
	data OUTSET_&num.;
		set &dsin.;
		OTHER_NONSENSE=cats("Hello from Macro (where inset =", &num., ")");
	run;

%mend;

Apply macro on each INSET using a data _null_ step:

*Run macro on all INSETS;

data _null_;
	do i=1 to &num_dsets;
		num=put(i, z2.);
		call execute(cats('%dosomething(dsin=INSET_', num, ',num=', num, ')'));
	end;
run;

Combine your outputs how you wish:

*Combine results;

data combined;
	set work.outset:;
run;

[nazmul]

Thank you for your kind help. Your code itself works perfectly. I followed your code to modify my one. However, it does not work. Could you please check? I highly appreciate your help.

rsubmit;
%let num_dsets=3;
data Inset_01; set F3; if obs<=2000; run;
data Inset_02; set F3; if 2000<obs<=4000; run;
data Inset_03; set F3; if 4000<obs<=6000; run;

%macro dosomething(dsin=, num=);
proc nlp noprint data=&dsin. absgconv=1e-15 OUTEST=T1; by obs;
bounds R>=0, R<=1;
min f;
decvar R=1;
f = abs(PRC1_IBES-(BVPS1_1+(((FEPS1_1/BVPS1_1_1)-R)/(1+R))*BVPS1_1
+(((FEPS2_1/BVPS2_1_1)-R)/((1+R)**2))*BVPS1_1_1
+(((FEPS3_1/BVPS3_1_1)-R)/((1+R)**3))*BVPS2_1_1
+(((FEPS4_1/BVPS4_1_1)-R)/((1+R)**4))*BVPS3_1_1
+(((FEPS5_1/BVPS5_1_1)-R)/((1+R)**5))*BVPS4_1_1
+(((FEPS6_1/BVPS6_1_1)-R)/((1+R)**6))*BVPS5_1_1
+(((FEPS7_1/BVPS7_1_1)-R)/((1+R)**7))*BVPS6_1_1
+(((FEPS8_1/BVPS8_1_1)-R)/((1+R)**8))*BVPS7_1_1
+(((FEPS9_1/BVPS9_1_1)-R)/((1+R)**9))*BVPS8_1_1
+(((FEPS10_1/BVPS10_1_1)-R)/((1+R)**10))*BVPS9_1_1
+(((FEPS11_1/BVPS11_1_1)-R)/((1+R)**11))*BVPS10_1_1
+(((FEPS12_1/BVPS12_1_1)-R)/(R*(1+R)**11))*BVPS11_1_1));
run;

data T1; set T1;
if _TYPE_='PARMS';
rename R=rgls1_1_1;
keep obs R; run;
data OUTSET_&num.; merge &dsin. (in=xx) T1(in=yy); by obs; if xx and yy; run;
%mend;

data _null_;
do i=1 to &num_dsets;
num=put(i, z2.);
call execute(cats('%dosomething(dsin=INSET_', num, ',num=', num, ')'));
end;
run;

endrsubmit;

[unison]

What doesn’t work? Can you show the log output?

[nazmul]

Dear Unison,

The codes that I made based on your codes do not work. Could you please check the log files posted here:

5126 rsubmit;
NOTE: Remote submit to WRDS commencing.
20900 %let num_dsets=3;
20901 data Inset_01; set F3; if obs<=2000; run;
20902 data Inset_02; set F3; if 2000<obs<=4000; run;
20903 data Inset_03; set F3; if 4000<obs<=6000; run;
20904
20905 %macro dosomething(dsin=, num=);
20906 proc nlp noprint data=&dsin. absgconv=1e-15 OUTEST=T1; by obs;
20907 bounds R>=0, R<=1;
20908 min f;
20909 decvar R=1;
20910 f = abs(PRC1_IBES-(BVPS1_1+(((FEPS1_1/BVPS1_1_1)-R)/(1+R))*BVPS1_1
20911 +(((FEPS2_1/BVPS2_1_1)-R)/((1+R)**2))*BVPS1_1_1
20912 +(((FEPS3_1/BVPS3_1_1)-R)/((1+R)**3))*BVPS2_1_1
20913 +(((FEPS4_1/BVPS4_1_1)-R)/((1+R)**4))*BVPS3_1_1
20914 +(((FEPS5_1/BVPS5_1_1)-R)/((1+R)**5))*BVPS4_1_1
20915 +(((FEPS6_1/BVPS6_1_1)-R)/((1+R)**6))*BVPS5_1_1
20916 +(((FEPS7_1/BVPS7_1_1)-R)/((1+R)**7))*BVPS6_1_1
20917 +(((FEPS8_1/BVPS8_1_1)-R)/((1+R)**8))*BVPS7_1_1
20918 +(((FEPS9_1/BVPS9_1_1)-R)/((1+R)**9))*BVPS8_1_1
20919 +(((FEPS10_1/BVPS10_1_1)-R)/((1+R)**10))*BVPS9_1_1
20920 +(((FEPS11_1/BVPS11_1_1)-R)/((1+R)**11))*BVPS10_1_1
20921 +(((FEPS12_1/BVPS12_1_1)-R)/(R*(1+R)**11))*BVPS11_1_1));
20922 run;
20923
20924 data T1; set T1;
20925 if _TYPE_='PARMS';
20926 rename R=rgls1_1_1;
20927 keep obs R; run;
20928 data OUTSET_&num.; merge &dsin. (in=xx) T1(in=yy); by obs; if xx and yy;
20928! run;
20929 %mend;
20930
20931 data _null_;
20932 do i=1 to &num_dsets;
20933 num=put(i, z2.);
20934 call execute(cats('%dosomething(dsin=INSET_', num, ',num=', num,
20934! ')'));
20935 end;
20936 run;
NOTE: Remote submit to WRDS complete.

[unison]

I only see NOTEs and no ERRORs. I cannot tell what is going wrong from this. What output datasets are you seeing?

I suggest pulling the proc nlp out from the macro and experimenting on that until you can get it to work for one INSET. From there, wrap in the macro command and build up from there.

[nazmul]

Thank you Unison. Your codes are perfect. I do not know why they did not work in my previous attempt. I  restart SAS and now your codes are giving me what I wanted. I highly appreciate your help.

[fdsaaaa]

%macro rtm(i=32);
proc nlp noprint data=S&i absgconv=1e-15 OUTEST=T1; by obs;
bounds R>=0, R<=1;
min f;
decvar R=1;
f = abs(PRC1_IBES-(BVPS1_1+(((FEPS1_1/BVPS1_1_1)-R)/(1+R))*BVPS1_1
+(((FEPS2_1/BVPS2_1_1)-R)/((1+R)**2))*BVPS1_1_1
+(((FEPS3_1/BVPS3_1_1)-R)/((1+R)**3))*BVPS2_1_1
+(((FEPS4_1/BVPS4_1_1)-R)/((1+R)**4))*BVPS3_1_1
+(((FEPS5_1/BVPS5_1_1)-R)/((1+R)**5))*BVPS4_1_1
+(((FEPS6_1/BVPS6_1_1)-R)/((1+R)**6))*BVPS5_1_1
+(((FEPS7_1/BVPS7_1_1)-R)/((1+R)**7))*BVPS6_1_1
+(((FEPS8_1/BVPS8_1_1)-R)/((1+R)**8))*BVPS7_1_1
+(((FEPS9_1/BVPS9_1_1)-R)/((1+R)**9))*BVPS8_1_1
+(((FEPS10_1/BVPS10_1_1)-R)/((1+R)**10))*BVPS9_1_1
+(((FEPS11_1/BVPS11_1_1)-R)/((1+R)**11))*BVPS10_1_1
+(((FEPS12_1/BVPS12_1_1)-R)/(R*(1+R)**11))*BVPS11_1_1));
run;
data T1; set T1;
if _TYPE_='PARMS';
rename R=rgls1_1_1;
keep obs R; run;
data O&i; merge S&i (in=xx) T1(in=yy); by obs; if xx and yy; run;
%mend rtm;
%rtm(i=31);

[nazmul]

Thank you for your kind help. I applied the code you mentioned, however, I did not find any SAS output. One additional information I want to share is that I am running my codes on a remote server. 

SAS log file:

5122 rsubmit;
NOTE: Remote submit to WRDS commencing.
20842 data S1; set F3; if obs<=2000; run;
20843 data S2; set F3; if 2000<obs<=4000; run;
20844 data S3; set F3; if 4000<obs<=6000; run;
20845
20846 %macro rtm(i=4);
20847 proc nlp noprint data=S&i absgconv=1e-15 OUTEST=T1; by obs;
20848 bounds R>=0, R<=1;
20849 min f;
20850 decvar R=1;
20851 f = abs(PRC1_IBES-(BVPS1_1+(((FEPS1_1/BVPS1_1_1)-R)/(1+R))*BVPS1_1
20852 +(((FEPS2_1/BVPS2_1_1)-R)/((1+R)**2))*BVPS1_1_1
20853 +(((FEPS3_1/BVPS3_1_1)-R)/((1+R)**3))*BVPS2_1_1
20854 +(((FEPS4_1/BVPS4_1_1)-R)/((1+R)**4))*BVPS3_1_1
20855 +(((FEPS5_1/BVPS5_1_1)-R)/((1+R)**5))*BVPS4_1_1
20856 +(((FEPS6_1/BVPS6_1_1)-R)/((1+R)**6))*BVPS5_1_1
20857 +(((FEPS7_1/BVPS7_1_1)-R)/((1+R)**7))*BVPS6_1_1
20858 +(((FEPS8_1/BVPS8_1_1)-R)/((1+R)**8))*BVPS7_1_1
20859 +(((FEPS9_1/BVPS9_1_1)-R)/((1+R)**9))*BVPS8_1_1
20860 +(((FEPS10_1/BVPS10_1_1)-R)/((1+R)**10))*BVPS9_1_1
20861 +(((FEPS11_1/BVPS11_1_1)-R)/((1+R)**11))*BVPS10_1_1
20862 +(((FEPS12_1/BVPS12_1_1)-R)/(R*(1+R)**11))*BVPS11_1_1));
20863 run;
20864 data T1; set T1;
20865 if _TYPE_='PARMS';
20866 rename R=rgls1_1_1;
20867 keep obs R; run;
20868 data O&i; merge S&i (in=xx) T1(in=yy); by obs; if xx and yy; run;
20869 %mend rtm;
20870 %rtm(i=3);
NOTE: Remote submit to WRDS complete.

[fdsaaaa]

include the entire log 

[nazmul]

That's the entire log file. As I mentioned earlier, nothing is happing when I run the macro. 

[fdsaaaa]

what if you run this

rsubmit;

data S1; set F3; if obs<=2000; run;

endrsubmit;

[nazmul]

Thank you for asking. My codes work for a single file (e.g. S1). I tried to put the complete log file here but the size of the log file is too long for the webpage (you know log file is usually long for PROC command). I, therefore, copied the last part of the log file here:

I appreciate your kind help.

NOTE: The above message was for the following BY group:
obs=1996
WARNING: Your program statements cannot be executed completely.
WARNING: Your program statements cannot be executed completely.
ERROR: NRRIDG Optimization cannot be completed.
WARNING: Optimization routine cannot improve the function value.
WARNING: In a total of 2550 calls an error occurred during execution of the
program statements. NLP attempted to recover by using a shorter step
size.
NOTE: The above message was for the following BY group:
obs=1997
ERROR: There are references to missing variables when the program code is
executed for _OBS_= 1
WARNING: Your program statements cannot be executed completely.
WARNING: In a total of 2551 calls an error occurred during execution of the
program statements. NLP attempted to recover by using a shorter step
size.
NOTE: The above message was for the following BY group:
obs=1998
ERROR: There are references to missing variables when the program code is
executed for _OBS_= 1
WARNING: Your program statements cannot be executed completely.
WARNING: In a total of 2552 calls an error occurred during execution of the
program statements. NLP attempted to recover by using a shorter step
size.
NOTE: The above message was for the following BY group:
obs=1999
NOTE: There were 1977 observations read from the data set WORK.S1.
NOTE: The data set WORK.S2 has 27298 observations and 7 variables.
NOTE: The PROCEDURE NLP printed pages 1-548.
NOTE: PROCEDURE NLP used (Total process time):
real time 39.59 seconds
cpu time 2.55 seconds

5567
5568 data S2; set S2;
5569 if _TYPE_='PARMS';
5570 rename R=rgls1_1_1;
5571 keep obs R; run;

NOTE: There were 27298 observations read from the data set WORK.S2.
NOTE: The data set WORK.S2 has 1429 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.00 seconds

5572 data S3; merge S1 (in=xx) S2(in=yy); by obs; if xx and yy; run;

NOTE: There were 1977 observations read from the data set WORK.S1.
NOTE: There were 1429 observations read from the data set WORK.S2.
NOTE: The data set WORK.S3 has 1429 observations and 3952 variables.
NOTE: DATA statement used (Total process time):
real time 0.15 seconds
cpu time 0.08 seconds

NOTE: Remote submit to WRDS complete.

[fdsaaaa]

you have heaps of warnings and error messages ! you need to resolve those before you consider using a macro . 

[nazmul]

Thank you for mentioning the issue.

Those warnings are very common when one tries to use optimization function for the quadratic equation in SAS and SAS provides correct optimization even with those warnings.

I restart the SAS and find that your codes are working however only for the very last file. Say if I have three input files (e.g., S1, S2, & S3), I get output only for the last file (In this case, S3). I am sorry for the inconvenience.

Could you please suggest why it may happen?