Iteration to change macro names

[Esseny1]

I would need some advise as to how would I go about changing the two variable names under %LET ClusterGrp & %LET Finalf_ for various iterations. The first iteration would be run the entire code below with CLUSTER1 & Final1. Then change the name to CLUSTER2 & Final2 and do the whole process again till we reach CLUSTER10 & Final10. Would greatly appreciate any help on this topic

%LET ClusterGrp = CLUSTER1;
%LET Finalf_ = Final1;

/*2 place where input needs to be changed SIMULTANEOUSLY LINE16 & LINE17
FOLLOW THE GRID BELOW ON NAMING CONVENTIONS*/

/*FINAL1 GROUP WO 10 - CLUSTER1 */
/*FINAL2 GROUP WO 9  - CLUSTER2 */
/*FINAL3 GROUP WO 8  - CLUSTER3 */
/*FINAL4 GROUP WO 7  - CLUSTER4 */
/*FINAL5 GROUP WO 6  - CLUSTER5 */
/*FINAL6 GROUP WO 5  - CLUSTER6 */
/*FINAL7 GROUP WO 4  - CLUSTER7 */
/*FINAL8 GROUP WO 3  - CLUSTER8 */
/*FINAL9 GROUP WO 2  - CLUSTER9 */
/*FINAL10 GROUP WO 1  - CLUSTER10 */
/*>>>>>CHANGE HERE FOLLOW NM CONVEN ABOVE >>> */

/*FINAL OUTPUT FILE. THIS NEED TO BE CHANGED ONLY ONCE BEFORE "RUN 3" is EXECUTED*/
%LET FinalMean = DIAB001;

data WORK.inclusterdat_random9;
	set &ClusterGrp.;
run;

/*STANDARD CLUSTERING ALGORITHM*/
/*STEP 3*/
/*Measure Euclidean distance between data points*/
proc distance 
	data=WORK.inclusterdat_random9
	out=distancedat 
	method=Euclid;
	var interval(METRIC);
	id contract_id;
run;

/*STEP 4*/
/*cluster creation using Ward's minimum variance*/
proc cluster data=distancedat method=ward /*trim=1 k=3*/
	outtree=treedat noprint;
	id contract_id;
run;

/*STEP 5*/
/* replace NSTARS with numeric constant (# of star levels = 5)*/
proc tree data=treedat ncl=5 horizontal out=outclusterdat noprint;
	id contract_id;
run;

/*STEP 6*/
/*Join measure score to distinct cluster groupings*/
proc sql;
	CREATE TABLE WORK.JOIN AS 
		SELECT a.*,
			b.METRIC
		FROM work.outclusterdat a INNER JOIN WORK.inclusterdat_random9 b
			ON a.contract_id = b.contract_id
		order by CLUSTER DESC;

	/*STEP7*/
	/*Create upper bound threshold cutoff*/
	/*NOTE***Cluster output is naturally unordered so it needs to be reordered depending on measure (higher=better or lower=better)***/
	/*NAME OUT FILE TO NEW DATASET HERE */
	/*This step needs to by run multiple times to create the followig files - */
proc sql;
	CREATE TABLE &Finalf_. AS 
		SELECT DISTINCT cluster,
			MIN(METRIC) AS LOWER_BOUND
		FROM WORK.JOIN
			WHERE cluster BETWEEN 1 AND 5
				GROUP BY cluster
					ORDER BY LOWER_BOUND ASC;

[Reeza]

• Wrap the code to be repeated into a macro
• Call the macro multiple times 
  • Automate macro call using another macro or call execute to call it from a data step

Here's some good references for these topics:

UCLA introductory tutorial on macro variables and macros
https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Tutorial on converting a working program to a macro

This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage
https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Ap...

Wrap code in macro:

%macro analysis(clustergrp = , finalf_ , finalMean= );


data WORK.inclusterdat_random9;
	set &ClusterGrp.;
run;

/*STANDARD CLUSTERING ALGORITHM*/
/*STEP 3*/
/*Measure Euclidean distance between data points*/
proc distance 
	data=WORK.inclusterdat_random9
	out=distancedat 
	method=Euclid;
	var interval(METRIC);
	id contract_id;
run;

/*STEP 4*/
/*cluster creation using Ward's minimum variance*/
proc cluster data=distancedat method=ward /*trim=1 k=3*/
	outtree=treedat noprint;
	id contract_id;
run;

/*STEP 5*/
/* replace NSTARS with numeric constant (# of star levels = 5)*/
proc tree data=treedat ncl=5 horizontal out=outclusterdat noprint;
	id contract_id;
run;

/*STEP 6*/
/*Join measure score to distinct cluster groupings*/
proc sql;
	CREATE TABLE WORK.JOIN AS 
		SELECT a.*,
			b.METRIC
		FROM work.outclusterdat a INNER JOIN WORK.inclusterdat_random9 b
			ON a.contract_id = b.contract_id
		order by CLUSTER DESC;

	/*STEP7*/
	/*Create upper bound threshold cutoff*/
	/*NOTE***Cluster output is naturally unordered so it needs to be reordered depending on measure (higher=better or lower=better)***/
	/*NAME OUT FILE TO NEW DATASET HERE */
	/*This step needs to by run multiple times to create the followig files - */
proc sql;
	CREATE TABLE &Finalf_. AS 
		SELECT DISTINCT cluster,
			MIN(METRIC) AS LOWER_BOUND
		FROM WORK.JOIN
			WHERE cluster BETWEEN 1 AND 5
				GROUP BY cluster
					ORDER BY LOWER_BOUND ASC;
quit;

%mend;

Call macro multiple times:

%analysis(ClusterGrp = Cluster1, Finalf_ = Final1, finalMean = DIAB001);
%analysis(ClusterGrp = Cluster2, Finalf_ = Final2, finalMean = DIAB001);
....

---

@Esseny1 wrote:

I would need some advise as to how would I go about changing the two variable names under %LET ClusterGrp & %LET Finalf_ for various iterations. The first iteration would be run the entire code below with CLUSTER1 & Final1. Then change the name to CLUSTER2 & Final2 and do the whole process again till we reach CLUSTER10 & Final10. Would greatly appreciate any help on this topic

%LET ClusterGrp = CLUSTER1;
%LET Finalf_ = Final1;

/*2 place where input needs to be changed SIMULTANEOUSLY LINE16 & LINE17
FOLLOW THE GRID BELOW ON NAMING CONVENTIONS*/

/*FINAL1 GROUP WO 10 - CLUSTER1 */
/*FINAL2 GROUP WO 9  - CLUSTER2 */
/*FINAL3 GROUP WO 8  - CLUSTER3 */
/*FINAL4 GROUP WO 7  - CLUSTER4 */
/*FINAL5 GROUP WO 6  - CLUSTER5 */
/*FINAL6 GROUP WO 5  - CLUSTER6 */
/*FINAL7 GROUP WO 4  - CLUSTER7 */
/*FINAL8 GROUP WO 3  - CLUSTER8 */
/*FINAL9 GROUP WO 2  - CLUSTER9 */
/*FINAL10 GROUP WO 1  - CLUSTER10 */
/*>>>>>CHANGE HERE FOLLOW NM CONVEN ABOVE >>> */

/*FINAL OUTPUT FILE. THIS NEED TO BE CHANGED ONLY ONCE BEFORE "RUN 3" is EXECUTED*/
%LET FinalMean = DIAB001;

data WORK.inclusterdat_random9;
	set &ClusterGrp.;
run;

/*STANDARD CLUSTERING ALGORITHM*/
/*STEP 3*/
/*Measure Euclidean distance between data points*/
proc distance 
	data=WORK.inclusterdat_random9
	out=distancedat 
	method=Euclid;
	var interval(METRIC);
	id contract_id;
run;

/*STEP 4*/
/*cluster creation using Ward's minimum variance*/
proc cluster data=distancedat method=ward /*trim=1 k=3*/
	outtree=treedat noprint;
	id contract_id;
run;

/*STEP 5*/
/* replace NSTARS with numeric constant (# of star levels = 5)*/
proc tree data=treedat ncl=5 horizontal out=outclusterdat noprint;
	id contract_id;
run;

/*STEP 6*/
/*Join measure score to distinct cluster groupings*/
proc sql;
	CREATE TABLE WORK.JOIN AS 
		SELECT a.*,
			b.METRIC
		FROM work.outclusterdat a INNER JOIN WORK.inclusterdat_random9 b
			ON a.contract_id = b.contract_id
		order by CLUSTER DESC;

	/*STEP7*/
	/*Create upper bound threshold cutoff*/
	/*NOTE***Cluster output is naturally unordered so it needs to be reordered depending on measure (higher=better or lower=better)***/
	/*NAME OUT FILE TO NEW DATASET HERE */
	/*This step needs to by run multiple times to create the followig files - */
proc sql;
	CREATE TABLE &Finalf_. AS 
		SELECT DISTINCT cluster,
			MIN(METRIC) AS LOWER_BOUND
		FROM WORK.JOIN
			WHERE cluster BETWEEN 1 AND 5
				GROUP BY cluster
					ORDER BY LOWER_BOUND ASC;

---

[Esseny1]

Thanks Reeza ..

I am getting some error messaged on 

25 GOPTIONS ACCESSIBLE;
ERROR: All positional parameters must precede keyword parameters.

and 

84 %analysis(ClusterGrp = Cluster1, Finalf_ = Final1, finalMean = DIAB001);
_
180
WARNING: Apparent invocation of macro ANALYSIS not resolved.

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

WARNING: Apparent invocation of macro ANALYSIS not resolved.
85 %analysis(ClusterGrp = Cluster2, Finalf_ = Final2, finalMean = DIAB001);
_
180

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

1                                                          The SAS System                          10:29 Wednesday, February 9, 2022

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROCESSFLOWNAME='Cluster - Optimization';
5          %LET _CLIENTPROJECTPATH='C:\Users\up0j1eb\Desktop\SNP Simulations\Cut_point_simulation_20220203_AY003.egp';
6          %LET _CLIENTPROJECTPATHHOST='HPZ155CG0502B2G';
7          %LET _CLIENTPROJECTNAME='Cut_point_simulation_20220203_AY003.egp';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=PNG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         FILENAME EGSR TEMP;
15         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16             STYLE=HtmlBlue
17             STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SASHome94/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
18             NOGTITLE
19             NOGFOOTNOTE
20             GPATH=&sasworklocation
21             ENCODING=UTF8
22             options(rolap="on")
23         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24         
25         GOPTIONS ACCESSIBLE;
ERROR: All positional parameters must precede keyword parameters.
26         %macro analysis(clustergrp = , finalf_ , finalMean= );
27         
28         data WORK.inclusterdat_random9;
29         	set &ClusterGrp.;
30         run;
31         
32         /*STANDARD CLUSTERING ALGORITHM*/
33         /*STEP 3*/
34         /*Measure Euclidean distance between data points*/
35         proc distance
36         	data=WORK.inclusterdat_random9
37         	out=distancedat
38         	method=Euclid;
39         	var interval(METRIC);
40         	id contract_id;
41         run;
42         
43         /*STEP 4*/
44         /*cluster creation using Ward's minimum variance*/
45         proc cluster data=distancedat method=ward /*trim=1 k=3*/
46         	outtree=treedat noprint;
47         	id contract_id;
48         run;
49         
50         /*STEP 5*/
51         /* replace NSTARS with numeric constant (# of star levels = 5)*/
52         proc tree data=treedat ncl=5 horizontal out=outclusterdat noprint;
53         	id contract_id;
54         run;
55         
56         /*STEP 6*/
2                                                          The SAS System                          10:29 Wednesday, February 9, 2022

57         /*Join measure score to distinct cluster groupings*/
58         proc sql;
59         	CREATE TABLE WORK.JOIN AS
60         		SELECT a.*,
61         			b.METRIC
62         		FROM work.outclusterdat a INNER JOIN WORK.inclusterdat_random9 b
63         			ON a.contract_id = b.contract_id
64         		order by CLUSTER DESC;
65         
66         	/*STEP7*/
67         	/*Create upper bound threshold cutoff*/
68         	/*NOTE***Cluster output is naturally unordered so it needs to be reordered depending on measure (higher=better or
68       ! lower=better)***/
69         	/*NAME OUT FILE TO NEW DATASET HERE */
70         	/*This step needs to by run multiple times to create the followig files - */
71         proc sql;
72         	CREATE TABLE &Finalf_. AS
73         		SELECT DISTINCT cluster,
74         			MIN(METRIC) AS LOWER_BOUND
75         		FROM WORK.JOIN
76         			WHERE cluster BETWEEN 1 AND 5
77         				GROUP BY cluster
78         					ORDER BY LOWER_BOUND ASC;
79         quit;
80         
81         %mend;
82         /*Call macro multiple times:*/
83         
84         %analysis(ClusterGrp = Cluster1, Finalf_ = Final1, finalMean = DIAB001);
           _
           180
WARNING: Apparent invocation of macro ANALYSIS not resolved.

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

WARNING: Apparent invocation of macro ANALYSIS not resolved.
85         %analysis(ClusterGrp = Cluster2, Finalf_ = Final2, finalMean = DIAB001);
           _
           180

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

86         
87         
88         
89         
90         GOPTIONS NOACCESSIBLE;
91         %LET _CLIENTTASKLABEL=;
92         %LET _CLIENTPROCESSFLOWNAME=;
93         %LET _CLIENTPROJECTPATH=;
94         %LET _CLIENTPROJECTPATHHOST=;
95         %LET _CLIENTPROJECTNAME=;
96         %LET _SASPROGRAMFILE=;
97         %LET _SASPROGRAMFILEHOST=;
98         
99         ;*';*";*/;quit;run;
100        ODS _ALL_ CLOSE;
101        
3                                                          The SAS System                          10:29 Wednesday, February 9, 2022

102        
103        QUIT; RUN;
104        

[Esseny1]

I tried running the added code you noted above and getting the following errors now .. Can you advise as to what else be missing from the logic ? 
Thanks - 


1                                                          The SAS System                          10:29 Wednesday, February 9, 2022

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROCESSFLOWNAME='Cluster - Optimization';
5          %LET _CLIENTPROJECTPATH='C:\Users\up0j1eb\Desktop\SNP Simulations\Cut_point_simulation_20220203_AY003.egp';
6          %LET _CLIENTPROJECTPATHHOST='HPZ155CG0502B2G';
7          %LET _CLIENTPROJECTNAME='Cut_point_simulation_20220203_AY003.egp';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=PNG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         FILENAME EGSR TEMP;
15         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16             STYLE=HtmlBlue
17             STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SASHome94/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
18             NOGTITLE
19             NOGFOOTNOTE
20             GPATH=&sasworklocation
21             ENCODING=UTF8
22             options(rolap="on")
23         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24         
25         GOPTIONS ACCESSIBLE;
ERROR: All positional parameters must precede keyword parameters.
26         %macro analysis(clustergrp = , finalf_ , finalMean= );
27         
28         data WORK.inclusterdat_random9;
29         	set &ClusterGrp.;
30         run;
31         
32         /*STANDARD CLUSTERING ALGORITHM*/
33         /*STEP 3*/
34         /*Measure Euclidean distance between data points*/
35         proc distance
36         	data=WORK.inclusterdat_random9
37         	out=distancedat
38         	method=Euclid;
39         	var interval(METRIC);
40         	id contract_id;
41         run;
42         
43         /*STEP 4*/
44         /*cluster creation using Ward's minimum variance*/
45         proc cluster data=distancedat method=ward /*trim=1 k=3*/
46         	outtree=treedat noprint;
47         	id contract_id;
48         run;
49         
50         /*STEP 5*/
51         /* replace NSTARS with numeric constant (# of star levels = 5)*/
52         proc tree data=treedat ncl=5 horizontal out=outclusterdat noprint;
53         	id contract_id;
54         run;
55         
56         /*STEP 6*/
2                                                          The SAS System                          10:29 Wednesday, February 9, 2022

57         /*Join measure score to distinct cluster groupings*/
58         proc sql;
59         	CREATE TABLE WORK.JOIN AS
60         		SELECT a.*,
61         			b.METRIC
62         		FROM work.outclusterdat a INNER JOIN WORK.inclusterdat_random9 b
63         			ON a.contract_id = b.contract_id
64         		order by CLUSTER DESC;
65         
66         	/*STEP7*/
67         	/*Create upper bound threshold cutoff*/
68         	/*NOTE***Cluster output is naturally unordered so it needs to be reordered depending on measure (higher=better or
68       ! lower=better)***/
69         	/*NAME OUT FILE TO NEW DATASET HERE */
70         	/*This step needs to by run multiple times to create the followig files - */
71         /*proc sql;*/
72         /*	CREATE TABLE &Finalf_. AS */
73         /*		SELECT DISTINCT cluster,*/
74         /*			MIN(METRIC) AS LOWER_BOUND*/
75         /*		FROM WORK.JOIN*/
76         /*			WHERE cluster BETWEEN 1 AND 5*/
77         /*				GROUP BY cluster*/
78         /*					ORDER BY LOWER_BOUND ASC;*/
79         quit;
80         
81         %mend;
82         
83         /*Call macro multiple times:*/
84         
85         %analysis(ClusterGrp = Cluster1, Finalf_ = Final1, finalMean = DIAB001);
           _
           180
WARNING: Apparent invocation of macro ANALYSIS not resolved.

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

WARNING: Apparent invocation of macro ANALYSIS not resolved.
86         %analysis(ClusterGrp = Cluster2, Finalf_ = Final2, finalMean = DIAB001);
           _
           180

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

WARNING: Apparent invocation of macro ANALYSIS not resolved.
87         %analysis(ClusterGrp = Cluster3, Finalf_ = Final3, finalMean = DIAB001);
           _
           180

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

WARNING: Apparent invocation of macro ANALYSIS not resolved.
88         %analysis(ClusterGrp = Cluster4, Finalf_ = Final4, finalMean = DIAB001);
           _
           180

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

WARNING: Apparent invocation of macro ANALYSIS not resolved.
3                                                          The SAS System                          10:29 Wednesday, February 9, 2022

89         %analysis(ClusterGrp = Cluster5, Finalf_ = Final5, finalMean = DIAB001);
           _
           180

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

WARNING: Apparent invocation of macro ANALYSIS not resolved.
90         %analysis(ClusterGrp = Cluster6, Finalf_ = Final6, finalMean = DIAB001);
           _
           180

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

WARNING: Apparent invocation of macro ANALYSIS not resolved.
91         %analysis(ClusterGrp = Cluster7, Finalf_ = Final7, finalMean = DIAB001);
           _
           180

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

WARNING: Apparent invocation of macro ANALYSIS not resolved.
92         %analysis(ClusterGrp = Cluster8, Finalf_ = Final8, finalMean = DIAB001);
           _
           180

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

WARNING: Apparent invocation of macro ANALYSIS not resolved.
93         %analysis(ClusterGrp = Cluster9, Finalf_ = Final9, finalMean = DIAB001);
           _
           180

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

WARNING: Apparent invocation of macro ANALYSIS not resolved.
94         %analysis(ClusterGrp = Cluster10, Finalf_ = Final10, finalMean = DIAB001);
           _
           180

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

95         
96         
97         
98         GOPTIONS NOACCESSIBLE;
99         %LET _CLIENTTASKLABEL=;
100        %LET _CLIENTPROCESSFLOWNAME=;
101        %LET _CLIENTPROJECTPATH=;
102        %LET _CLIENTPROJECTPATHHOST=;
103        %LET _CLIENTPROJECTNAME=;
104        %LET _SASPROGRAMFILE=;
105        %LET _SASPROGRAMFILEHOST=;
106        
107        ;*';*";*/;quit;run;
108        ODS _ALL_ CLOSE;
109        
110        
111        QUIT; RUN;
4                                                          The SAS System                          10:29 Wednesday, February 9, 2022

112        

[Reeza]

Missed an equal sign after the finalf_ in the %macro line.
ERROR: All positional parameters must precede keyword parameters.
26 %macro analysis(clustergrp = , finalf_ , finalMean= );

Add the equal sign so that finalf looks like finalMean/clusterGRP.