PROC OPTMODEL does not provide a BY statement to obtain separate analyses for groups of observations defined by one or more grouping variables. The following example illustrates how BY group processing can be done with PROC OPTMODEL and the runOptmodel action in SAS^® Viya^®.

The following statements create a data set from Lewis and Taylor (1967) containing the ages (in months), heights (in inches), and weights (in pounds) for a group of school children.

      data htwt;
         input sex $ age:3.1 height weight @@;
         datalines;
      f 143 56.3  85.0 f 155 62.3 105.0 f 153 63.3 108.0 f 161 59.0  92.0
      f 191 62.5 112.5 f 171 62.5 112.0 f 185 59.0 104.0 f 142 56.5  69.0
      f 160 62.0  94.5 f 140 53.8  68.5 f 139 61.5 104.0 f 178 61.5 103.5
      f 157 64.5 123.5 f 149 58.3  93.0 f 143 51.3  50.5 f 145 58.8  89.0
      f 191 65.3 107.0 f 150 59.5  78.5 f 147 61.3 115.0 f 180 63.3 114.0
      f 141 61.8  85.0 f 140 53.5  81.0 f 164 58.0  83.5 f 176 61.3 112.0
      f 185 63.3 101.0 f 166 61.5 103.5 f 175 60.8  93.5 f 180 59.0 112.0
      f 210 65.5 140.0 f 146 56.3  83.5 f 170 64.3  90.0 f 162 58.0  84.0
      f 149 64.3 110.5 f 139 57.5  96.0 f 186 57.8  95.0 f 197 61.5 121.0
      f 169 62.3  99.5 f 177 61.8 142.5 f 185 65.3 118.0 f 182 58.3 104.5
      f 173 62.8 102.5 f 166 59.3  89.5 f 168 61.5  95.0 f 169 62.0  98.5
      f 150 61.3  94.0 f 184 62.3 108.0 f 139 52.8  63.5 f 147 59.8  84.5
      f 144 59.5  93.5 f 177 61.3 112.0 f 178 63.5 148.5 f 197 64.8 112.0
      f 146 60.0 109.0 f 145 59.0  91.5 f 147 55.8  75.0 f 145 57.8  84.0
      f 155 61.3 107.0 f 167 62.3  92.5 f 183 64.3 109.5 f 143 55.5  84.0
      f 183 64.5 102.5 f 185 60.0 106.0 f 148 56.3  77.0 f 147 58.3 111.5
      f 154 60.0 114.0 f 156 54.5  75.0 f 144 55.8  73.5 f 154 62.8  93.5
      f 152 60.5 105.0 f 191 63.3 113.5 f 190 66.8 140.0 f 140 60.0  77.0
      f 148 60.5  84.5 f 189 64.3 113.5 f 143 58.3  77.5 f 178 66.5 117.5
      f 164 65.3  98.0 f 157 60.5 112.0 f 147 59.5 101.0 f 148 59.0  95.0
      f 177 61.3  81.0 f 171 61.5  91.0 f 172 64.8 142.0 f 190 56.8  98.5
      f 183 66.5 112.0 f 143 61.5 116.5 f 179 63.0  98.5 f 186 57.0  83.5
      f 182 65.5 133.0 f 182 62.0  91.5 f 142 56.0  72.5 f 165 61.3 106.5
      f 165 55.5  67.0 f 154 61.0 122.5 f 150 54.5  74.0 f 155 66.0 144.5
      f 163 56.5  84.0 f 141 56.0  72.5 f 147 51.5  64.0 f 210 62.0 116.0
      f 171 63.0  84.0 f 167 61.0  93.5 f 182 64.0 111.5 f 144 61.0  92.0
      f 193 59.8 115.0 f 141 61.3  85.0 f 164 63.3 108.0 f 186 63.5 108.0
      f 169 61.5  85.0 f 175 60.3  86.0 f 180 61.3 110.5 m 165 64.8  98.0
      m 157 60.5 105.0 m 144 57.3  76.5 m 150 59.5  84.0 m 150 60.8 128.0
      m 139 60.5  87.0 m 189 67.0 128.0 m 183 64.8 111.0 m 147 50.5  79.0
      m 146 57.5  90.0 m 160 60.5  84.0 m 156 61.8 112.0 m 173 61.3  93.0
      m 151 66.3 117.0 m 141 53.3  84.0 m 150 59.0  99.5 m 164 57.8  95.0
      m 153 60.0  84.0 m 206 68.3 134.0 m 250 67.5 171.5 m 176 63.8  98.5
      m 176 65.0 118.5 m 140 59.5  94.5 m 185 66.0 105.0 m 180 61.8 104.0
      m 146 57.3  83.0 m 183 66.0 105.5 m 140 56.5  84.0 m 151 58.3  86.0
      m 151 61.0  81.0 m 144 62.8  94.0 m 160 59.3  78.5 m 178 67.3 119.5
      m 193 66.3 133.0 m 162 64.5 119.0 m 164 60.5  95.0 m 186 66.0 112.0
      m 143 57.5  75.0 m 175 64.0  92.0 m 175 68.0 112.0 m 175 63.5  98.5
      m 173 69.0 112.5 m 170 63.8 112.5 m 174 66.0 108.0 m 164 63.5 108.0
      m 144 59.5  88.0 m 156 66.3 106.0 m 149 57.0  92.0 m 144 60.0 117.5
      m 147 57.0  84.0 m 188 67.3 112.0 m 169 62.0 100.0 m 172 65.0 112.0
      m 150 59.5  84.0 m 193 67.8 127.5 m 157 58.0  80.5 m 168 60.0  93.5
      m 140 58.5  86.5 m 156 58.3  92.5 m 156 61.5 108.5 m 158 65.0 121.0
      m 184 66.5 112.0 m 156 68.5 114.0 m 144 57.0  84.0 m 176 61.5  81.0
      m 168 66.5 111.5 m 149 52.5  81.0 m 142 55.0  70.0 m 188 71.0 140.0
      m 203 66.5 117.0 m 142 58.8  84.0 m 189 66.3 112.0 m 188 65.8 150.5
      m 200 71.0 147.0 m 152 59.5 105.0 m 174 69.8 119.5 m 166 62.5  84.0
      m 145 56.5  91.0 m 143 57.5 101.0 m 163 65.3 117.5 m 166 67.3 121.0
      m 182 67.0 133.0 m 173 66.0 112.0 m 155 61.8  91.5 m 162 60.0 105.0
      m 177 63.0 111.0 m 177 60.5 112.0 m 175 65.5 114.0 m 166 62.0  91.0
      m 150 59.0  98.0 m 150 61.8 118.0 m 188 63.3 115.5 m 163 66.0 112.0
      m 171 61.8 112.0 m 162 63.0  91.0 m 141 57.5  85.0 m 174 63.0 112.0
      m 142 56.0  87.5 m 148 60.5 118.0 m 140 56.8  83.5 m 160 64.0 116.0
      m 144 60.0  89.0 m 206 69.5 171.5 m 159 63.3 112.0 m 149 56.3  72.0
      m 193 72.0 150.0 m 194 65.3 134.5 m 152 60.8  97.0 m 146 55.0  71.5
      m 139 55.0  73.5 m 186 66.5 112.0 m 161 56.8  75.0 m 153 64.8 128.0
      m 196 64.5  98.0 m 164 58.0  84.0 m 159 62.8  99.0 m 178 63.8 112.0
      m 153 57.8  79.5 m 155 57.3  80.5 m 178 63.5 102.5 m 142 55.0  76.0
      m 164 66.5 112.0 m 189 65.0 114.0 m 164 61.5 140.0 m 167 62.0 107.5
      m 151 59.3  87.0
      ;

The PROC REG statements below perform separate regression analyses for each level of SEX. The ODS OUTPUT statement saves the parameter estimates in data set REGparms. Predicted values are saved by the OUTPUT statement in data set REGpred.

      proc reg data=htwt;
         by sex;
         model weight=height age;
         ods output ParameterEstimates=REGparms;
         output out=REGpred p=pred;
         run; quit;

These statements display the model parameter estimates for each SEX level.

      proc print data=REGparms noobs;
         var sex variable estimate;
         run;

sex Variable Estimate f Intercept -150.59698 f height 3.60378 f age 1.90703 m Intercept -113.71346 m height 2.68075 m age 3.08167

These statements display the first ten predicted values.

      proc print data=REGpred(obs=10) noobs;
         var weight pred;
         run;

weight pred 85.0 79.566 105.0 103.477 108.0 106.700 92.0 92.729 112.5 111.063 112.0 107.249 104.0 97.306 69.0 80.096 94.5 103.350 68.5 69.985

There are two methods to perform the same regression analyses in PROC OPTMODEL. One method uses a DO loop, and the other method uses the COFOR statement available beginning with SAS/OR^® 13.1 software in SAS 9.4 TS1M1. Both methods will be shown.

BY groups that are defined using multiple BY variables would require nested DO loops. An easier way to handle this situation is to create a single variable in a DATA step which takes on one value for each combination of values in the multiple BY variables. For instance, if BY groups are defined by GENDER and SMOKING (Yes, No), create a single variable that has four possible levels: 1 for Male, Yes; 2 for Male, No; 3 for Female, Yes; 4 for Female, No. You can then use the method shown below for a single BY variable.

In the following statements, the single variable (SEX) defining the BY groups is first stored in a macro variable (BYVAR) for use in the OPTMODEL code. A section of code is shown for each method. Use only the section for the method that you choose.

      %let byvar = sex;
      proc optmodel;

         /* Declare the index set and parameters */
         set OBS;
         str sex {OBS};
         num age {OBS};
         num height {OBS};
         num weight {OBS};

         /* Read the index set and parameters from the input data set */
         read data htwt into OBS=[_N_] sex age height weight;

         /* Collect values of BY variable into an index set */
         set BYSET = setof {i in OBS} &byvar.[i];
         put BYSET=;
         str by;

         /* Declare variables and objective*/
         var Beta {0..2};
         set OBS_BY = {i in OBS: &byvar.[i] = by};
         impvar Estimate {i in OBS_BY} = Beta[0] + Beta[1] * height[i] + Beta[2] * age[i];
         min SSE = sum {i in OBS_BY} (Estimate[i] - weight[i])^2;

         /* Declare parameters to store solution values */
         num Beta_sol {BYSET, 0..2};
         num Estimate_sol {OBS};

DO loop method:

         /* Loop over values of the BY variable, calling the QP solver once for each level. 
            Note that OBS_BY is automatically updated when the BY variable changes. */
         do by = BYSET;
            put by=;
            solve with QP;
            print Beta;

COFOR statement method, available beginning in SAS/OR 13.1 in SAS 9.4 TS1M1:

         cofor {b in BYSET} do;
            by = b;
            put by=;
            solve with QP;
            print Beta;

The following code is used for either method:

            /* Store solution in numeric parameters */
            for {j in 0..2} Beta_sol[by,j] = Beta[j].sol;
            for {i in OBS_BY} Estimate_sol[i] = Estimate[i].sol;

         end;

         /* Save the solutions for all values of the BY variable simultaneously */
         create data OPTMparms from [by j] Beta_sol;
         create data OPTMpred (drop=i) from [i] weight Estimate_sol;
         quit;

These statements display the OPTMODEL solution for the model parameter estimates for each SEX level. Note that these match the parameter estimates from PROC REG above.

      proc print data=OPTMparms noobs;
         run;

by j Beta_sol f 0 -150.597 f 1 3.604 f 2 1.907 m 0 -113.713 m 1 2.681 m 2 3.082

 

These statements display the first ten predicted values from the OPTMODEL solution. Note that these match the predicted values from PROC REG above.

      proc print data=OPTMpred(obs=10) noobs;
         run;

weight Estimate_sol 85.0 79.566 105.0 103.477 108.0 106.700 92.0 92.729 112.5 111.063 112.0 107.249 104.0 97.306 69.0 80.096 94.5 103.350 68.5 69.985

The BY group analysis can also be done using the runOptmodel action. The following statements start a SAS^® Cloud Analytic Services (CAS) session and create a CAS library to hold a copy of the input data set.

      cas c; 
      libname sascas1 cas; 

      data sascas1.htwt;
         set htwt;
         id = _N_;
         run;
      
      proc cas;
         source pgm; 
         put _BY_LINE_;

         /* Declare the index set and parameters */
         set OBS;
         str sex {OBS};
         num age {OBS};
         num height {OBS};
         num weight {OBS};
      
         /* Read the index set and parameters from the input data set */
         read data htwt into OBS=[id] age height weight;
      
         /* Declare variables and objective */
         var Beta {0..2};
         impvar Estimate {i in OBS} = Beta[0] + Beta[1] * height[i] + Beta[2] * age[i];
         min SSE = sum {i in OBS} (Estimate[i] - weight[i])^2;
      
         /* Call the QP solver */
         solve;
         print Beta;
      
         /* Write solutions to data tables */
         create data OPTMparms from [j] Beta_sol=Beta;
         create data OPTMpred from [i] weight Estimate_sol=Estimate;
         endsource;
      
         action optimization.runOptmodel result=r / printlevel=0 code=pgm groupBy={'sex'};
         run;
         quit;

The following statements display the results (not shown) which match those above though the order of the observations might differ. The CAS statement terminates the CAS session.

      proc print data=sascas1.OPTMparms noobs;
         run;
      proc print data=sascas1.OPTMpred(obs=10) noobs;
         run;
      cas c terminate;

Reference

Lewis, T., and Taylor, L. R. (1967). Introduction to Experimental Ecology. New York: Academic Press.

Operating System and Release Information

Product Family	Product	System	SAS Release
			Reported	Fixed*
SAS System	SAS/OR	z/OS
		OpenVMS VAX
		Microsoft® Windows® for 64-Bit Itanium-based Systems
		Microsoft Windows Server 2003 Datacenter 64-bit Edition
		Microsoft Windows Server 2003 Enterprise 64-bit Edition
		Microsoft Windows XP 64-bit Edition
		Microsoft® Windows® for x64
		OS/2
		Microsoft Windows 95/98
		Microsoft Windows 2000 Advanced Server
		Microsoft Windows 2000 Datacenter Server
		Microsoft Windows 2000 Server
		Microsoft Windows 2000 Professional
		Microsoft Windows NT Workstation
		Microsoft Windows Server 2003 Datacenter Edition
		Microsoft Windows Server 2003 Enterprise Edition
		Microsoft Windows Server 2003 Standard Edition
		Microsoft Windows Server 2003 for x64
		Microsoft Windows Server 2008
		Microsoft Windows Server 2008 for x64
		Microsoft Windows XP Professional
		Windows 7 Enterprise 32 bit
		Windows 7 Enterprise x64
		Windows 7 Home Premium 32 bit
		Windows 7 Home Premium x64
		Windows 7 Professional 32 bit
		Windows 7 Professional x64
		Windows 7 Ultimate 32 bit
		Windows 7 Ultimate x64
		Windows Millennium Edition (Me)
		Windows Vista
		Windows Vista for x64
		64-bit Enabled AIX
		64-bit Enabled HP-UX
		64-bit Enabled Solaris
		ABI+ for Intel Architecture
		AIX
		HP-UX
		HP-UX IPF
		IRIX
		Linux
		Linux for x64
		Linux on Itanium
		OpenVMS Alpha
		OpenVMS on HP Integrity
		Solaris
		Solaris for x64
		Tru64 UNIX

* For software releases that are not yet generally available, the Fixed Release is the software release in which the problem is planned to be fixed.

---

Type:	Usage Note
Priority:
Topic:	SAS Reference ==> Procedures ==> OPTMODEL Analytics ==> Mathematical Optimization

Date Modified:	2021-06-04 14:45:39
Date Created:	2011-02-08 16:05:49