https://communities.sas.com/t5/SAS-Programming/Macro-variable-using-iteration-to-change-macro-names/m-p/795249#M255054 /*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 >>> */ %LET ClusterGrp = CLUSTER1; %LET Finalf_ = Final1; /*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; Wed, 09 Feb 2022 17:50:42 GMT Esseny1 2022-02-09T17:50:42Z https://communities.sas.com/t5/SAS-Programming/Macro-variable-using-iteration-to-change-macro-names/m-p/795246#M255051 Hello - I have to automate the iteration on the code below so that once the loop is done running for CLUSTER1 & Final1 the code changes the variable names in the two noted below to CLUSTER2 & Final2 till it reaches CLUSTER10 & Final10. Much appreciated for any insights *************** %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 >>> */ %LET ClusterGrp = CLUSTER1; %LET Finalf_ = Final1; /*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; Wed, 09 Feb 2022 17:49:11 GMT https://communities.sas.com/t5/SAS-Programming/Macro-variable-using-iteration-to-change-macro-names/m-p/795246#M255051 Esseny1 2022-02-09T17:49:11Z https://communities.sas.com/t5/SAS-Programming/Macro-variable-using-iteration-to-change-macro-names/m-p/795249#M255054 /*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 >>> */ %LET ClusterGrp = CLUSTER1; %LET Finalf_ = Final1; /*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; Wed, 09 Feb 2022 17:50:42 GMT https://communities.sas.com/t5/SAS-Programming/Macro-variable-using-iteration-to-change-macro-names/m-p/795249#M255054 Esseny1 2022-02-09T17:50:42Z