topic Re: Efficiency Analysis: Run analysis by region and year in Mathematical Optimization, Discrete-Event Simulation, and OR

Efficiency Analysis: Run analysis by region and year

shawnchen0321 — Fri, 18 Mar 2022 09:57:16 GMT

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.

Re: Efficiency Analysis: Run analysis by region and year

PaigeMiller — Fri, 18 Mar 2022 10:45:43 GMT

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-EXECUTE/m-p/745391

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.

Re: Efficiency Analysis: Run analysis by region and year

PaigeMiller — Fri, 18 Mar 2022 12:13:32 GMT

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;

Re: Efficiency Analysis: Run analysis by region and year

RobPratt — Fri, 18 Mar 2022 13:44:23 GMT

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.

Re: Efficiency Analysis: Run analysis by region and year

shawnchen0321 — Sat, 19 Mar 2022 02:05:12 GMT

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.

Re: Efficiency Analysis: Run analysis by region and year

shawnchen0321 — Sat, 19 Mar 2022 02:13:57 GMT

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.

Re: Efficiency Analysis: Run analysis by region and year

shawnchen0321 — Tue, 22 Mar 2022 10:39:02 GMT

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.

Re: Efficiency Analysis: Run analysis by region and year

PaigeMiller — Tue, 22 Mar 2022 11:33:43 GMT

"... 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.

Re: Efficiency Analysis: Run analysis by region and year

shawnchen0321 — Tue, 22 Mar 2022 11:48:49 GMT

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.

Re: Efficiency Analysis: Run analysis by region and year

PaigeMiller — Tue, 22 Mar 2022 11:57:59 GMT

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.

Re: Efficiency Analysis: Run analysis by region and year

shawnchen0321 — Tue, 22 Mar 2022 12:34:53 GMT

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!

Re: Efficiency Analysis: Run analysis by region and year

PaigeMiller — Tue, 22 Mar 2022 12:48:44 GMT

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

Re: Efficiency Analysis: Run analysis by region and year

shawnchen0321 — Tue, 22 Mar 2022 13:03:56 GMT

It can work. Very appreciate.

Re: Efficiency Analysis: Run analysis by region and year

RobPratt — Tue, 22 Mar 2022 14:01:00 GMT

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;

Re: Efficiency Analysis: Run analysis by region and year

shawnchen0321 — Tue, 22 Mar 2022 14:37:37 GMT

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.

Re: Efficiency Analysis: Run analysis by region and year

RobPratt — Tue, 22 Mar 2022 19:22:35 GMT

That error message means that you have not started a CAS session. You will also need to convert the SAS data sets to CAS tables, as shown in the three DATA steps I provided.

For a successful run, the resulting efficiency_data table will have two extra columns for the BY variables (year and region):

Obs	garage_name	year	region	staff	showroom	alpha_sales	beta_sales	profit	garage
1	Winchest	2020	A	7	8.0	10	12	8.5	1
2	Andover	2020	A	6	6.0	20	30	9.0	2
3	Basingst	2020	B	2	3.0	40	40	2.0	3
4	Poole	2020	B	14	9.0	20	25	10.0	4
5	Winchest	2021	A	10	9.0	10	10	11.0	5
6	Andover	2021	A	24	15.0	15	13	25.0	6
7	Basingst	2021	B	6	7.0	50	40	8.5	7
8	Poole	2021	B	8	7.5	5	8	9.0	8

Re: Efficiency Analysis: Run analysis by region and year

shawnchen0321 — Wed, 23 Mar 2022 06:17:49 GMT

I got it. After I searched the related info, I found that unfortunately my school didn't provide the SAS Viya software. Thus, that's reason I can't connect to the server.

Thank you for your generous help.