You have three variables identifying three provider types, and four variables identifying four service types. So imagine you read in each of your current observations, and making twelve observations from it (corresponding to 3 rows and 4 columns). The resulting data set, NEED, could have these 3 variables:
- Provider_type (with values "Doctor", "Nurse", "Assistant_Physician") (i.e. the var name of the indicated provider variable)
- Service_type ("Consultation .... Procedure") - var name of the indicated service variable
- MY_FREQ (the sum of values of the indicated provider and service
So you would 12 times as many observations as you start with. But you could now do a crosstab of provider_type by service_type, and tell the sas procedure to weight each record by MY_FREQ.
Something like
proc tabulate data=need noseps order=data;
freq my_freq;
tables
provide_type all='Total' ,
service_type * N * f=12.0;
run;
So now, you need to determine how to create data set NEED. Consider using two arrays:
- An array for rows (variables doctor, nurse, assistant_physician
- An array for cols (consultation ... procedure)
You can do nested loops, loop through the column array inside a loop over the row array. Set MY_FREQ to the sum of values for the row variable and the col variable, and then OUTPUT.