How to get a variable array changing thorugh iteration?

[erauan]

First of all: sorry for, maybe, not expressing properly in the title. I'm an intern newbie in SAS so I can't describe it accurately.

First of all let me explain what I have so far: in my department we provide potential customers data to 4 different telemarketing companies, so they can call them in order to hire our services. So I have this program in which these 4 companies (let's call them "K", "T", "U" and "L") are assigned a percentage through an array of 4 numeric values in between 0 and 1. The percentages are a constant value defined at the very beggining of the program. Example: T = 0'35, K = 0'2, U = 0'2 and L = 0'25.

What I need: I've been asked to do that process dinamically (?). Each one of those 4 companies have a different percentage for each one of our 24 different surge of calls defined. That percentage is not directly given but calculated by me. So I have a set of data in which I have 6 columns: Surge number, T percentage, K percentage, U percentage, L percentage, and total percentage (just to make sure it's alway 1); and I have 24 different rows, each one assigned a surge number from 1 to 24 and with the percentages assigned to those companies in that specific surge.

Surge - Percentage

I wanted to get an array formed by each one of those 4 percentages which changes dinamically through an iterative array from 1 to the number of surges -24 in this case-. After getting it, into that iteration, execute the first program with the array achieved.

Sorry if the explanation is not precise enough, I tried my best .

[ballardw]

It really helps to provide an example of your starting data, preferably as data step as no one is going to take time to type your data from a picture.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Then given some example starting data show what the desired output from the example data should be.

If this process is in any way to combine percentages then you must provide more information such as the denominator involved as otherwise any result is likely to be off. If the number of calls (?) differs between surges you cannot get the average percentage by averaging the rates. You have to sum for all of the surges and then calculate the percentages from the combined data.

[erauan]

Of course, I just didn't know how to do it.

The data which I started with were the number of potential costumers which were assigned to each one of the 4 providers in each surge.

data WORK.QUERY_FOR_ASIGNA_DATOSS_0000;
 infile datalines dsd truncover;
 input SURGE_NUMBER:32. SURGE_K:32. SURGE_T:32. SURGE_U:32. SURGE_L:32. SURGE_TOTAL:32.;
datalines;
1 623 832 890 200 2545
2 623 832 610 200 2265
3 623 832 610 200 2265
4 623 832 925 200 2580
5 573 752 1000 200 2525
;;;;

For each surge I added up all of them, getting the sum of each surge, and then I made a simple division to get each percentage. I don't think there's any problem with that so far.

Here's the code I used:

%_eg_conditional_dropds(EGTASK.QUERY_FOR_ASIGNA_DATOSS_0001);

PROC SQL;
   CREATE TABLE EGTASK.QUERY_FOR_ASIGNA_DATOSS_0001 AS 
   SELECT /* SURGE_NUMBER */
            (MONOTONIC()) AS SURGE_NUMBER, 
          /* PERCENTAGE_K */
            (t1.SURGE_K/t1.SURGE_TOTAL) AS PERCENTAGE_K, 
          /* PERCENTAGE_T */
            (t1.SURGE_T/t1.SURGE_TOTAL) AS PERCENTAGE_T, 
          /* PERCENTAGE_U */
            (t1.SURGE_U/t1.SURGE_TOTAL) AS PERCENTAGE_U, 
          /* PERCENTAGE_L */
            (t1.SURGE_L/t1.SURGE_TOTAL) AS PERCENTAGE_L, 
          /* TOTAL_PERCENTAGE */
            
            ((t1.SURGE_K/t1.SURGE_TOTAL)+(t1.SURGE_T/t1.SURGE_TOTAL)+(t1.SURGE_L/t1.SURGE_TOTAL)+(t1.SURGE_U/t1.SURGE_TOTAL)) 
            AS TOTAL_PERCENTAGE
      FROM EGTASK.QUERY_FOR_ASIGNA_DATOSS_0000 t1;
QUIT;

And here is an example of the results:

data WORK.QUERY_FOR_ASIGNA_DATOSS_0001;
  infile datalines dsd truncover;
  input SURGE_NUMBER:32. PERCENTAGE_K:32. PERCENTAGE_T:32. PERCENTAGE_U:32. PERCENTAGE_L:32. TOTAL_PERCENTAGE:32.;
datalines;
1 0.2447937132 0.3269155206 0.3497053045 0.0785854617 1
2 0.2750551876 0.3673289183 0.2693156733 0.0883002208 1
3 0.2750551876 0.3673289183 0.2693156733 0.0883002208 1
4 0.2414728682 0.3224806202 0.3585271318 0.0775193798 1
5 0.2269306931 0.2978217822 0.396039604 0.0792079208 1
;;;;

That's all I have so far -not much tbh-. There we find the surge number, the percentage assigned to each provider and the sum of all percentages to make sure it's always 1.

This is the code I wanted to use later which we know for sure it works the way it is done:

DATA ASIGNA_DATOS;

SET WORK.VARIABLES;
ARRAY COD{4} $ 1 ('K','T','U','L');
ARRAY PORCENTAJE{4} (0.266, 0.327, 0.137, 0.27);
DO CONTADOR=1 TO N_REGISTROS;
	NUM_OLEADA=CEIL(CONTADOR/N_REGISTROS*N_OLEADAS);
	AUX=CONTADOR/N_REGISTROS*N_OLEADAS-NUM_OLEADA+1;
	IF AUX<PORCENTAJE[1] THEN COD_PROVEEDOR=COD[1];
	ELSE IF AUX-PORCENTAJE[1]<PORCENTAJE[2] THEN COD_PROVEEDOR=COD[2];
	ELSE IF AUX-PORCENTAJE[1]-PORCENTAJE[2]<=PORCENTAJE[3] THEN COD_PROVEEDOR=COD[3];
	ELSE IF AUX-PORCENTAJE[1]-PORCENTAJE[2]-PORCENTAJE[3]<=PORCENTAJE[4] THEN COD_PROVEEDOR=COD[4];
OUTPUT;
END;
RUN;

Where OLEADA = SURGE, CONTADOR = COUNTER, PORCENTAJE = PERCENTAGE. 

You may see how it works (I had a very interesting time decyphering how it worked). It divides the whole number of registers (roughly 70k) into 4 groups as there are 4 providers. Then, in each group, it starts assigning registers to providers (using the codes "K", "T", "U" and "L") until each percentage is filled, and then it switches to the following provider. It does the same for each one of the 4 groups.

As you can see the percentages are given at the beggining of the program. I need to input my set of four percentage per surge instead of that prefixed array, repeating the process for each one of the surges.

[ballardw]

Are you wanting to do a calculation using each observation of percentages using the data from your WORK.QUERY_FOR_ASIGNA_DATOSS_0001 data set?

If that is the case then perhaps you are looking for a JOIN with your WORK.VARIABLES data set and then the ARRAY PORCENTAJE would reference the variables PERCENTAGE_K PERCENTAGE_T PERCENTAGE_U PERCENTAGE_L

If you want a single value across all of the surges to use in the ARRAY PORCENTAJE then you would have to summarize the data from the first set without considering the surge at all with something like:

( sum(t1.SURGE_K) / sum(t1.SURGE_TOTAL) ) AS PERCENTAGE_K, 

Then join with the Work.variables data set and reference the variables

Either would look something like

proc sql;
   create table work.combined as
   select a.*,b.*
   from work.variables as a, work.percentagedata
   ;
quit;

DATA ASIGNA_DATOS;

SET WORK.combined;
ARRAY COD{4} $ 1 ('K','T','U','L');
ARRAY PORCENTAJE{4} PERCENTAGE_K PERCENTAGE_T PERCENTAGE_U PERCENTAGE_L;
DO CONTADOR=1 TO N_REGISTROS;
	NUM_OLEADA=CEIL(CONTADOR/N_REGISTROS*N_OLEADAS);
	AUX=CONTADOR/N_REGISTROS*N_OLEADAS-NUM_OLEADA+1;
	IF AUX<PORCENTAJE[1] THEN COD_PROVEEDOR=COD[1];
	ELSE IF AUX-PORCENTAJE[1]<PORCENTAJE[2] THEN COD_PROVEEDOR=COD[2];
	ELSE IF AUX-PORCENTAJE[1]-PORCENTAJE[2]<=PORCENTAJE[3] THEN COD_PROVEEDOR=COD[3];
	ELSE IF AUX-PORCENTAJE[1]-PORCENTAJE[2]-PORCENTAJE[3]<=PORCENTAJE[4] THEN COD_PROVEEDOR=COD[4];
OUTPUT;
END;
RUN;

The difference would be whether the percentagedata set would be the overall summary or if you need the by surge set.

[erauan]

I see what you did there and I appreciate, but it's not what I was exactly looking for. I am not looking forward to get an overall percentage of each provider but to work with the percentage given for each provider in each surge.

You see: I have this SAS program in which I input an array of 4 percentages and it does certain calculations and operations based on that array. I would like to recycle that program, but repeating the process for each one of my 24 surges. Each surge would provide a different set of percentages, so I need a "variable" array in which in the nth iteration it would take the percentage from the nth row (in this case row = surge).

I don't even know if it's possible here in SAS as arrays are different from other programming languages.

P.S.: sorry for the late reply. I was busy these last days and I barely visited office.

[Reeza]

*I think* this is a lookup with a temporary array that you're trying to do. But as @ballardw has mentioned, this would be infinitely easier if you could provide data as text with a clear indication of what you have, what you want and the logic to get there. Someone can help with the code then. 

---

@erauan wrote:

First of all: sorry for, maybe, not expressing properly in the title. I'm an intern newbie in SAS so I can't describe it accurately.

First of all let me explain what I have so far: in my department we provide potential customers data to 4 different telemarketing companies, so they can call them in order to hire our services. So I have this program in which these 4 companies (let's call them "K", "T", "U" and "L") are assigned a percentage through an array of 4 numeric values in between 0 and 1. The percentages are a constant value defined at the very beggining of the program. Example: T = 0'35, K = 0'2, U = 0'2 and L = 0'25.

What I need: I've been asked to do that process dinamically (?). Each one of those 4 companies have a different percentage for each one of our 24 different surge of calls defined. That percentage is not directly given but calculated by me. So I have a set of data in which I have 6 columns: Surge number, T percentage, K percentage, U percentage, L percentage, and total percentage (just to make sure it's alway 1); and I have 24 different rows, each one assigned a surge number from 1 to 24 and with the percentages assigned to those companies in that specific surge.

Surge - Percentage

I wanted to get an array formed by each one of those 4 percentages which changes dinamically through an iterative array from 1 to the number of surges -24 in this case-. After getting it, into that iteration, execute the first program with the array achieved.

Sorry if the explanation is not precise enough, I tried my best .

---