Efficiency Analysis: Run analysis by region and year

[shawnchen0321]

I copy the code from SAS website as below. These codes are regarding "Efficiency Analysis: How to Use Data Envelopment Analysis to Compare Efficiencies of Garages".
https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/ormpex/ormpex_code_mpex22.htm

data inputs;
   input input $9.;
   datalines;
staff
showroom
pop1
pop2
alpha_enq
beta_enq
;

data outputs;
   input output $11.;
   datalines;
alpha_sales
beta_sales
profit
;

data garage_data;
   input garage_name $12. staff showroom pop1 pop2 alpha_enq beta_enq
      alpha_sales beta_sales profit;
   datalines;
Winchester  7  8   10  12 8.5 4   2    0.6  1.5
Andover     6  6   20  30 9   4.5 2.3  0.7  1.6
Basingstoke 2  3   40  40 2   1.5 0.8  0.25 0.5
Poole       14 9   20  25 10  6   2.6  0.86 1.9
Woking      10 9   10  10 11  5   2.4  1    2
Newbury     24 15  15  13 25  19  8    2.6  4.5
Portsmouth  6  7   50  40 8.5 3   2.5  0.9  1.6
Alresford   8  7.5 5   8  9   4   2.1  0.85 2
Salisbury   5  5   10  10 5   2.5 2    0.65 0.9
Guildford   8  10  30  35 9.5 4.5 2.05 0.75 1.7
Alton       7  8   7   8  3   2   1.9  0.7  0.5
Weybridge   5  6.5 9   12 8   4.5 1.8  0.63 1.4
Dorchester  6  7.5 10  10 7.5 4   1.5  0.45 1.45
Bridport    11 8   8   10 10  6   2.2  0.65 2.2
Weymouth    4  5   10  10 7.5 3.5 1.8  0.62 1.6
Portland    3  3.5 3   2  2   1.5 0.9  0.35 0.5
Chichester  5  5.5 8   10 7   3.5 1.2  0.45 1.3
Petersfield 21 12  6   8  15  8   6    0.25 2.9
Petworth    6  5.5 2   2  8   5   1.5  0.55 1.55
Midhurst    3  3.6 3   3  2.5 1.5 0.8  0.2  0.45
Reading     30 29  120 80 35  20  7    2.5  8
Southampton 25 16  110 80 27  12  6.5  3.5  5.4
Bournemouth 19 10  90  12 25  13  5.5  3.1  4.5
Henley      7  6   5   7  8.5 4.5 1.2  0.48 2
Maidenhead  12 8   7   10 12  7   4.5  2    2.3
Fareham     4  6   1   1  7.5 3.5 1.1  0.48 1.7
Romsey      2  2.5 1   1  2.5 1   0.4  0.1  0.55
Ringwood    2  3.5 2   2  1.9 1.2 0.3  0.09 0.4
;

proc optmodel;
   set <str> INPUTS;
   read data inputs into INPUTS=[input];

   set <str> OUTPUTS;
   read data outputs into OUTPUTS=[output];

   set <num> GARAGES;
   str garage_name {GARAGES};
   num input  {INPUTS, GARAGES};
   num output {OUTPUTS, GARAGES};
   read data garage_data into GARAGES=[_N_] garage_name
      {i in INPUTS}  <input[i,_N_]=col(i)>
      {i in OUTPUTS} <output[i,_N_]=col(i)>;

   num k;
   num efficiency_number {GARAGES};
   num weight_sol {GARAGES, GARAGES};

   var Weight {GARAGES} >= 0;
   var Inefficiency >= 0;

   max Objective = Inefficiency;

   con Input_con {i in INPUTS}:
      sum {j in GARAGES} input[i,j] * Weight[j] <= input[i,k];

   con Output_con {i in OUTPUTS}:
      sum {j in GARAGES} output[i,j] * Weight[j] >= output[i,k] * Inefficiency;

   do k = GARAGES;
      solve;
      efficiency_number[k] = 1 / Inefficiency.sol;
      for {j in GARAGES}
         weight_sol[k,j] = (if Weight[j].sol > 1e-6 then Weight[j].sol else .);
   end;

   set EFFICIENT_GARAGES = {j in GARAGES: efficiency_number[j] >= 1};
   set INEFFICIENT_GARAGES = GARAGES diff EFFICIENT_GARAGES;

   print garage_name efficiency_number;
   create data efficiency_data from [garage] garage_name efficiency_number;

quit;

I have a question: if the garage data has region and year data, I hope the model was constructed by region and year. According to different regions and years, I have more detailed efficiency values.

How to modify the current code to achieve my hope? I really appreciate any help you can provide.

[PaigeMiller]

Since OPTMODEL does not have a BY statement, you would have to use either a macro or CALL EXECUTE (or both).

An example is shown here: https://communities.sas.com/t5/SAS-Programming/CALL-SYMPUT-within-a-macro-generated-using-CALL-EXECU...

There are many other examples here in the SAS Communities.

Since the data you do show does not have either region or year, its hard to provide more specific information.

[shawnchen0321]

Very appreciate your help, expert!

I have modified the current dataset and inputted the year and region variables.

Do you mind directly changing the code and using the dataset I provided below as an example?

data inputs;
   input input $8.;
   datalines;
staff
showroom
;

data outputs;
   input output $10.;
   datalines;
beta_sales
profit
;

data garage_data;
   input garage_name $ year region $ staff showroom alpha_sales beta_sales profit;
   datalines;
Winchester  2020 A 7  8   10  12 8.5 4   2    0.6  1.5
Andover 2020 A    6  6   20  30 9   4.5 2.3  0.7  1.6
Basingstoke 2020 B 2  3   40  40 2   1.5 0.8  0.25 0.5
Poole 2020 B 14 9   20  25 10  6   2.6  0.86 1.9
Winchester  2021 A    10 9   10  10 11  5   2.4  1    2
Andover  2021 A  24 15  15  13 25  19  8    2.6  4.5
Basingstoke 2021 B  6  7   50  40 8.5 3   2.5  0.9  1.6
Poole 2021 B  8  7.5 5   8  9   4   2.1  0.85 2
;

proc optmodel;
   set <str> INPUTS;
   read data inputs into INPUTS=[input];

   set <str> OUTPUTS;
   read data outputs into OUTPUTS=[output];

   set <num> GARAGES;
   str garage_name {GARAGES};
   num input  {INPUTS, GARAGES};
   num output {OUTPUTS, GARAGES};
   read data garage_data into GARAGES=[_N_] garage_name
      {i in INPUTS}  <input[i,_N_]=col(i)>
      {i in OUTPUTS} <output[i,_N_]=col(i)>;

   num k;
   num efficiency_number {GARAGES};
   num weight_sol {GARAGES, GARAGES};

   var Weight {GARAGES} >= 0;
   var Inefficiency >= 0;

   max Objective = Inefficiency;

   con Input_con {i in INPUTS}:
      sum {j in GARAGES} input[i,j] * Weight[j] <= input[i,k];

   con Output_con {i in OUTPUTS}:
      sum {j in GARAGES} output[i,j] * Weight[j] >= output[i,k] * Inefficiency;

   do k = GARAGES;
      solve;
      efficiency_number[k] = 1 / Inefficiency.sol;
      for {j in GARAGES}
         weight_sol[k,j] = (if Weight[j].sol > 1e-6 then Weight[j].sol else .);
   end;

   print garage_name efficiency_number;
   create data efficiency_data from [garage] garage_name efficiency_number;
quit;

Many thanks for considering my request.

[PaigeMiller]

Here's a simple example of using a macro and CALL EXECUTE. 

NOTE: you would NEVER do this for PROCs that have a BY statement (like PROC MEANS), you would just use the BY statement. Since PROC OPTMODEL does not have a BY statement, you would need to replace the PROC MEANS in the COMPUTE macro below with the proper PROC OPTMODEL code.

/* Create data set for this analysis, properly sorted */
proc sort data=sashelp.cars out=cars;
    by make TYPE;
run;
/* Create data set to allow looping over Make and Type */
data cars1;
    set cars(keep=make type);
    by make type;
    if first.type;
run;

/* Calculation macro */
%macro compute(make=,type=);
    proc means data=cars(where=(make="&make" and type="&type"));
        var msrp;
    run;
%mend;

/* Perform looping over all Make and Type */
data _null_;
    set cars1;
    call execute(cats('%nrstr(%compute(make=',make,',type=',type,'))'));
run;

[shawnchen0321]

Hi

I have tried your recommendation and modified some codes, but I still can't accomplish my hope.

Could you help me to find out the problem?

data inputs;
   input input $8.;
   datalines;
staff
showroom
;

data outputs;
   input output $10.;
   datalines;
beta_sales
profit
;

data garage_data;
   input garage_name $ year region $ staff showroom alpha_sales beta_sales profit;
   datalines;
Winchester  2020 A 7  8   10  12 8.5 4   2    0.6  1.5
Andover 2020 A    6  6   20  30 9   4.5 2.3  0.7  1.6
Basingstoke 2020 B 2  3   40  40 2   1.5 0.8  0.25 0.5
Poole 2020 B 14 9   20  25 10  6   2.6  0.86 1.9
Winchester  2021 A    10 9   10  10 11  5   2.4  1    2
Andover  2021 A  24 15  15  13 25  19  8    2.6  4.5
Basingstoke 2021 B  6  7   50  40 8.5 3   2.5  0.9  1.6
Poole 2021 B  8  7.5 5   8  9   4   2.1  0.85 2
;

proc sort data=garage_data;
    by year region;
run;

data garage_data1;
    set garage_data(keep=year region);
    by year region;
    if first.region;
run;

%macro compute(year=,region=);
proc optmodel ;
   set <str> INPUTS;
   read data inputs into INPUTS=[input];

   set <str> OUTPUTS;
   read data outputs into OUTPUTS=[output];

   set <num> GARAGES ;
   str garage_name {GARAGES};
   num input  {INPUTS, GARAGES};
   num output {OUTPUTS, GARAGES};
   read data garage_data(where=(year="&year" and region="&region")) into GARAGES=[_N_] garage_name
      {i in INPUTS}  <input[i,_N_]=col(i)>
      {i in OUTPUTS} <output[i,_N_]=col(i)>;

   num k;
   num efficiency_number {GARAGES};
   num weight_sol {GARAGES, GARAGES};

   var Weight {GARAGES} >= 0;
   var Inefficiency >= 0;

   max Objective = Inefficiency;

   con Input_con {i in INPUTS}:
      sum {j in GARAGES} input[i,j] * Weight[j] <= input[i,k];

   con Output_con {i in OUTPUTS}:
      sum {j in GARAGES} output[i,j] * Weight[j] >= output[i,k] * Inefficiency;

   do k = GARAGES;
      solve;
      efficiency_number[k] = 1 / Inefficiency.sol;
      for {j in GARAGES}
         weight_sol[k,j] = (if Weight[j].sol > 1e-6 then Weight[j].sol else .);
   end;

   print garage_name efficiency_number;
   create data efficiency_data from [garage] garage_name efficiency_number ;
quit;
%mend;

data _null_;
    set garage_data1;
    call execute(cats('%nrstr(%compute(year=',year,',region=',region,'))'));
run;

Thank you in advance.

[PaigeMiller]

"... but I still can't accomplish my hope."

It is always useless to say something like this, without providing details.

Is there an error(s) in the LOG? If so, SHOW US the log. Is the output wrong? If so, SHOW US the incorrect output and explain why it is wrong.

[shawnchen0321]

Sorry...

The log mentioned these information as follows:

NOTE: CALL EXECUTE generated line.
1 + %compute(year=2020,region=A)
NOTE: There were 2 observations read from the data set WORK.INPUTS.
NOTE: There were 2 observations read from the data set WORK.OUTPUTS.
ERROR: WHERE clause operator requires compatible variables.
ERROR: 在 5 行 103 欄，'GARAGES' 符號沒有值。 /* It means 'GARAGES' symbol has no value at line 5, column 103. */
ERROR: 在 1 行 192 欄，'GARAGES' 符號沒有值。
ERROR: 在 1 行 192 欄，'GARAGES' 符號沒有值。
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE OPTMODEL used (Total process time):
real time 0.00 秒
cpu time 0.00 秒

2 + %compute(year=2020,region=B)

NOTE: There were 2 observations read from the data set WORK.INPUTS.
NOTE: There were 2 observations read from the data set WORK.OUTPUTS.
ERROR: WHERE clause operator requires compatible variables.
ERROR: 在 5 行 103 欄，'GARAGES' 符號沒有值。
ERROR: 在 1 行 192 欄，'GARAGES' 符號沒有值。
ERROR: 在 1 行 192 欄，'GARAGES' 符號沒有值。
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE OPTMODEL used (Total process time):
real time 0.01 秒
cpu time 0.01 秒

I'm sorry for causing trouble for you.

[PaigeMiller]

This is not a macro error. When you create code that is to be included in a macro, you first have to have code that works (without macro language). If you don't have working code, then the macro will never work either. So, your task is to first make this code work with no macro language whatsoever, for the case where YEAR=2020 and REGION=A.

Hint:

ERROR: WHERE clause operator requires compatible variables.

So you use WHERE in your code, this is the part that isn't working. Your homework assignment is the get this WHERE statement to work in the case that has no macro language in the code.

[shawnchen0321]

The code provided by the SAS official website can only run the efficiency analysis according to a single year. It can run the way I want, but my current data is panel data, so I have to separate different years and different industries for efficiency analysis.

I am not very familiar with SAS, so I tried to copy and adjust your suggestions and codes, and try to put them into the code provided by the SAS official website, but after adding "where=YEAR=2020 and REGION=A", the SAS can't work.

I may need to prioritise manual grouping for repeat function by year and industry, thank you again!

[PaigeMiller · Accepted solution]

The comment I made was that you have to find out how to make the WHERE clause operate correctly. Once you do that, your macro should work (unless there are other errors I am not aware of)

Try this

where=(year=&year and region="&region")

The variable YEAR is probably numeric and so the double-quotes are wrong

[shawnchen0321]

It can work. Very appreciate.

[RobPratt]

PaigeMiller correctly noted that PROC OPTMODEL does not have a BY statement, but two alternatives are illustrated in this SAS Usage Note:

42332 - BY group processing with PROC OPTMODEL and the runOptmodel action (sas.com)

The first approach is to introduce an explicit outer loop over the BY groups.  The second (and simpler) approach is to use the groupBy parameter in the runOptmodel action in SAS Optimization.

[shawnchen0321]

Initially I thought that using "by" as the same as proc reg could solve my problem, but unfortunately it doesn't. Cause I am an amateur in SAS, I need help from experts. I have learned a lot from experts.

Thanks a lot.

[RobPratt]

Here is the runOptmodel groupBy approach, with very few changes from your PROC OPTMODEL code.  Note the NOGROUPBY option in the READ DATA statements for the two tables that do not contain the BY variables.

data sascas1.inputs;
   set inputs;
run;
data sascas1.outputs;
   set outputs;
run;
data sascas1.garage_data;
   set garage_data;
   garage = _N_;
run;

proc cas noqueue;
   source pgm;
   put _BY_LINE_;
   set <str> INPUTS;
   read data inputs nogroupby into INPUTS=[input];

   set <str> OUTPUTS;
   read data outputs nogroupby into OUTPUTS=[output];

   set <num> GARAGES;
   str garage_name {GARAGES};
   num input  {INPUTS, GARAGES};
   num output {OUTPUTS, GARAGES};
   read data garage_data into GARAGES=[garage] garage_name
      {i in INPUTS}  <input[i,garage]=col(i)>
      {i in OUTPUTS} <output[i,garage]=col(i)>;

   num k;
   num efficiency_number {GARAGES};
   num weight_sol {GARAGES, GARAGES};

   var Weight {GARAGES} >= 0;
   var Inefficiency >= 0;

   max Objective = Inefficiency;

   con Input_con {i in INPUTS}:
      sum {j in GARAGES} input[i,j] * Weight[j] <= input[i,k];

   con Output_con {i in OUTPUTS}:
      sum {j in GARAGES} output[i,j] * Weight[j] >= output[i,k] * Inefficiency;

   do k = GARAGES;
      solve;
      efficiency_number[k] = 1 / Inefficiency.sol;
      for {j in GARAGES}
         weight_sol[k,j] = (if Weight[j].sol > 1e-6 then Weight[j].sol else .);
   end;

   print garage_name efficiency_number;
   create data efficiency_data from [garage] garage_name efficiency_number;
   endsource;

   loadActionSet 'optimization';
   action optimization.runOptmodel / code=pgm groupBy={'year','region'};
   run;
quit;

[shawnchen0321]

First, big thanks for you help.

Then, I used code as follows.

data inputs;
   input input $8.;
   datalines;
staff
showroom
;

data outputs;
   input output $10.;
   datalines;
beta_sales
profit
;

data garage_data;
   input garage_name $ year region $ staff showroom alpha_sales beta_sales profit;
   datalines;
Winchester  2020 A 7  8   10  12 8.5 4   2    0.6  1.5
Andover 2020 A    6  6   20  30 9   4.5 2.3  0.7  1.6
Basingstoke 2020 B 2  3   40  40 2   1.5 0.8  0.25 0.5
Poole 2020 B 14 9   20  25 10  6   2.6  0.86 1.9
Winchester  2021 A    10 9   10  10 11  5   2.4  1    2
Andover  2021 A  24 15  15  13 25  19  8    2.6  4.5
Basingstoke 2021 B  6  7   50  40 8.5 3   2.5  0.9  1.6
Poole 2021 B  8  7.5 5   8  9   4   2.1  0.85 2
;

proc cas noqueue;
   source pgm;
   put _BY_LINE_;
   set <str> INPUTS;
   read data inputs nogroupby into INPUTS=[input];

   set <str> OUTPUTS;
   read data outputs nogroupby into OUTPUTS=[output];

   set <num> GARAGES;
   str garage_name {GARAGES};
   num input  {INPUTS, GARAGES};
   num output {OUTPUTS, GARAGES};
   read data garage_data into GARAGES=[garage] garage_name
      {i in INPUTS}  <input[i,garage]=col(i)>
      {i in OUTPUTS} <output[i,garage]=col(i)>;

   num k;
   num efficiency_number {GARAGES};
   num weight_sol {GARAGES, GARAGES};

   var Weight {GARAGES} >= 0;
   var Inefficiency >= 0;

   max Objective = Inefficiency;

   con Input_con {i in INPUTS}:
      sum {j in GARAGES} input[i,j] * Weight[j] <= input[i,k];

   con Output_con {i in OUTPUTS}:
      sum {j in GARAGES} output[i,j] * Weight[j] >= output[i,k] * Inefficiency;

   do k = GARAGES;
      solve;
      efficiency_number[k] = 1 / Inefficiency.sol;
      for {j in GARAGES}
         weight_sol[k,j] = (if Weight[j].sol > 1e-6 then Weight[j].sol else .);
   end;

   print garage_name efficiency_number;
   create data efficiency_data from [garage] garage_name efficiency_number;
   endsource;

   loadActionSet 'optimization';
   action optimization.runOptmodel / code=pgm groupBy={'year','region'};
   run;
quit;

But I received the errors from log, like these:

ERROR: There is no server connection to execute the action 'loadactionset'.
ERROR: Execution halted

1) How do I solve the problem about server connection?

2) If I used the code as you mentioned, could it output the result according to year and region separately?

Thanks.