Summing up amount variable above a set unit floor.

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 76
Accepted Solution

Summing up amount variable above a set unit floor.

D/All,

I'm slightly confused on the logic to get summation of a variable above a set unit floor. I have attached a sample excel file in which i have shown two input datasets;

1st dataset is the targets data and 2nd dataset is the corresponding agents loan bookings.

3rd grid on the sheet is the expected output.

Let me explain:

The first agent booked 10 cases and his target is 11 which is 91% hence all amounts booked above 6th case (amount sorted in ascending order)  and further seperated into Prod 1 & 2.

Second case is same..

Third agent has booked a total of 27 cases which is 104% of this target hence every thing will be counted for his case, further divided into Prod 1 & 2.

Hope i could explain properly.. Requesting experts to guide me on this.

Many thanks...

Rgds, Anil


Accepted Solutions
Solution
‎12-29-2013 11:04 AM
PROC Star
Posts: 7,364

Re: Summing up amount variable above a set unit floor.

: I don't know if your datasets 1 and 2 are as shown in your example, or if some of dataset2 has to be created from the values that are there.

There are numerous ways to accomplish what you need.  The following is a brute force data step approach and assumes that most of the dataset 2 variables have to be created:

data dataset1;

  informat Channel $6.;

  informat Segment $4.;

  informat AgentCode $5.;

  infile cards dlm='09'x dsd;

  input Channel Segment AgentCode Target Floor;

  cards;

Chnl 1 3MOB A0001 11 6

Chnl 1 6MOB A0002 22 13

Chnl 1 9MOB A0003 26 15

;

data dataset2;

  informat Code $5.;

  informat Prod $6.;

  informat Amount comma7.;

  infile cards dlm='09'x dsd;

  input Code Unit Prod Amount;

  cards;

A0001 1 PROD 1 104,236

A0001 2 PROD 1 78,315

A0001 3 PROD 2 106,306

A0001 4 PROD 2 64,354

A0001 5 PROD 1 96,883

A0001 6 PROD 2 66,499

A0001 7 PROD 1 107,417

A0001 8 PROD 1 59,651

A0001 9 PROD 1 98,532

A0001 10 PROD 1 64,956

A0002 1 PROD 1 102,611

A0002 2 PROD 1 104,848

A0002 3 PROD 1 56,427

A0002 4 PROD 2 107,909

A0002 5 PROD 1 73,739

A0002 6 PROD 2 73,739

A0002 7 PROD 1 81,565

A0002 8 PROD 1 102,164

A0002 9 PROD 1 74,849

A0002 10 PROD 1 106,864

A0002 11 PROD 1 92,700

A0002 12 PROD 1 104,236

A0002 13 PROD 1 78,315

A0002 14 PROD 1 106,306

A0002 15 PROD 1 64,354

A0002 16 PROD 2 96,883

A0002 17 PROD 2 66,499

A0002 18 PROD 2 107,417

A0003 1 PROD 1 59,651

A0003 2 PROD 1 98,532

A0003 3 PROD 2 64,956

A0003 4 PROD 2 97,861

A0003 5 PROD 1 90,512

A0003 6 PROD 1 102,611

A0003 7 PROD 1 104,848

A0003 8 PROD 1 56,427

A0003 9 PROD 2 107,909

A0003 10 PROD 1 73,739

A0003 11 PROD 2 73,739

A0003 12 PROD 1 81,565

A0003 13 PROD 1 102,164

A0003 14 PROD 2 66,499

A0003 15 PROD 1 107,417

A0003 16 PROD 1 59,651

A0003 17 PROD 1 78,315

A0003 18 PROD 1 106,306

A0003 19 PROD 1 64,354

A0003 20 PROD 1 104,848

A0003 21 PROD 1 102,164

A0003 22 PROD 1 74,849

A0003 23 PROD 1 106,864

A0003 24 PROD 1 92,700

A0003 25 PROD 1 104,236

A0003 26 PROD 1 78,315

A0003 27 PROD 1 106,306

;

proc sort data=dataset2;

  by Code amount;

run;

data dataset2;

  merge dataset1 (rename=(AgentCode=Code)) dataset2;

  by Code;

  if first.Code then Rank=1;

  else Rank+1;

run;

data dataset2;

  do until (last.Code);

    set dataset2;

    by Code;

    if first.Code then Counter=1;

    else Counter+1;

    if last.Code then Target_Pct=100*Counter/Target;

  end;

  do until (last.Code);

    set dataset2;

    by Code;

    if Target_Pct lt 100 then do;

      if Rank le floor then Count_OK="Not OK";

      else Count_OK="OK";

    end;

    else Count_OK="OK";

    output;

  end;

run;

data dataset3 (drop=Channel Segment Unit Amount Prod Rank Counter Count_OK);

  set dataset2;

  by Code;

  if first.Code then do;

    PROD_1_Unit=0;

    PROD_1_Amount=0;

    PROD_2_Unit=0;

    PROD_2_Amount=0;

  end;

  if Count_OK eq "OK" and Prod eq "PROD 1" then do;

    PROD_1_Unit+1;

    PROD_1_Amount+Amount;

  end;

  else if Count_OK eq "OK" and Prod eq "PROD 2" then do;

    PROD_2_Unit+1;

    PROD_2_Amount+Amount;

  end;

  if last.Code then output;

run;

View solution in original post


All Replies
Solution
‎12-29-2013 11:04 AM
PROC Star
Posts: 7,364

Re: Summing up amount variable above a set unit floor.

: I don't know if your datasets 1 and 2 are as shown in your example, or if some of dataset2 has to be created from the values that are there.

There are numerous ways to accomplish what you need.  The following is a brute force data step approach and assumes that most of the dataset 2 variables have to be created:

data dataset1;

  informat Channel $6.;

  informat Segment $4.;

  informat AgentCode $5.;

  infile cards dlm='09'x dsd;

  input Channel Segment AgentCode Target Floor;

  cards;

Chnl 1 3MOB A0001 11 6

Chnl 1 6MOB A0002 22 13

Chnl 1 9MOB A0003 26 15

;

data dataset2;

  informat Code $5.;

  informat Prod $6.;

  informat Amount comma7.;

  infile cards dlm='09'x dsd;

  input Code Unit Prod Amount;

  cards;

A0001 1 PROD 1 104,236

A0001 2 PROD 1 78,315

A0001 3 PROD 2 106,306

A0001 4 PROD 2 64,354

A0001 5 PROD 1 96,883

A0001 6 PROD 2 66,499

A0001 7 PROD 1 107,417

A0001 8 PROD 1 59,651

A0001 9 PROD 1 98,532

A0001 10 PROD 1 64,956

A0002 1 PROD 1 102,611

A0002 2 PROD 1 104,848

A0002 3 PROD 1 56,427

A0002 4 PROD 2 107,909

A0002 5 PROD 1 73,739

A0002 6 PROD 2 73,739

A0002 7 PROD 1 81,565

A0002 8 PROD 1 102,164

A0002 9 PROD 1 74,849

A0002 10 PROD 1 106,864

A0002 11 PROD 1 92,700

A0002 12 PROD 1 104,236

A0002 13 PROD 1 78,315

A0002 14 PROD 1 106,306

A0002 15 PROD 1 64,354

A0002 16 PROD 2 96,883

A0002 17 PROD 2 66,499

A0002 18 PROD 2 107,417

A0003 1 PROD 1 59,651

A0003 2 PROD 1 98,532

A0003 3 PROD 2 64,956

A0003 4 PROD 2 97,861

A0003 5 PROD 1 90,512

A0003 6 PROD 1 102,611

A0003 7 PROD 1 104,848

A0003 8 PROD 1 56,427

A0003 9 PROD 2 107,909

A0003 10 PROD 1 73,739

A0003 11 PROD 2 73,739

A0003 12 PROD 1 81,565

A0003 13 PROD 1 102,164

A0003 14 PROD 2 66,499

A0003 15 PROD 1 107,417

A0003 16 PROD 1 59,651

A0003 17 PROD 1 78,315

A0003 18 PROD 1 106,306

A0003 19 PROD 1 64,354

A0003 20 PROD 1 104,848

A0003 21 PROD 1 102,164

A0003 22 PROD 1 74,849

A0003 23 PROD 1 106,864

A0003 24 PROD 1 92,700

A0003 25 PROD 1 104,236

A0003 26 PROD 1 78,315

A0003 27 PROD 1 106,306

;

proc sort data=dataset2;

  by Code amount;

run;

data dataset2;

  merge dataset1 (rename=(AgentCode=Code)) dataset2;

  by Code;

  if first.Code then Rank=1;

  else Rank+1;

run;

data dataset2;

  do until (last.Code);

    set dataset2;

    by Code;

    if first.Code then Counter=1;

    else Counter+1;

    if last.Code then Target_Pct=100*Counter/Target;

  end;

  do until (last.Code);

    set dataset2;

    by Code;

    if Target_Pct lt 100 then do;

      if Rank le floor then Count_OK="Not OK";

      else Count_OK="OK";

    end;

    else Count_OK="OK";

    output;

  end;

run;

data dataset3 (drop=Channel Segment Unit Amount Prod Rank Counter Count_OK);

  set dataset2;

  by Code;

  if first.Code then do;

    PROD_1_Unit=0;

    PROD_1_Amount=0;

    PROD_2_Unit=0;

    PROD_2_Amount=0;

  end;

  if Count_OK eq "OK" and Prod eq "PROD 1" then do;

    PROD_1_Unit+1;

    PROD_1_Amount+Amount;

  end;

  else if Count_OK eq "OK" and Prod eq "PROD 2" then do;

    PROD_2_Unit+1;

    PROD_2_Amount+Amount;

  end;

  if last.Code then output;

run;

Frequent Contributor
Posts: 76

Re: Summing up amount variable above a set unit floor.

Thanks a lot Arthur.. This is exactly what I was looking for... 

Trusted Advisor
Posts: 1,131

Re: Summing up amount variable above a set unit floor.

Alternatively using the proc means and proc transpose

data have;

  infile cards missover ;

  input Code $5.    @7 Unit    @10 Prod & $6.    @16 Amount comma8.    @24 Rank    @27 Count &$;

cards;

A0001    1  PROD 1    104,236    8     Ok

A0001    2     PROD 1    78,315    5     Not Ok

A0001    3     PROD 2    106,306    9     Ok

A0001    4  PROD 2    64,354    2     Not Ok

A0001    5     PROD 1    96,883    6     Not Ok

A0001    6     PROD 2    66,499    4     Not Ok

A0001    7     PROD 1    107,417    10 Ok

A0001    8     PROD 1    59,651    1     Not Ok

A0001    9     PROD 1    98,532    7     Ok

A0001    10 PROD 1    64,956    3     Not Ok

A0002    1     PROD 1    102,611    12 Not Ok

A0002    2     PROD 1    104,848    14 Ok

A0002    3     PROD 1    56,427    1     Not Ok

A0002    4     PROD 2    107,909    18 Ok

A0002    5     PROD 1    73,739    4     Not Ok

A0002    6     PROD 2    73,739    5     Not Ok

A0002    7     PROD 1    81,565    8     Not Ok

A0002    8     PROD 1    102,164    11 Not Ok

A0002    9     PROD 1    74,849    6     Not Ok

A0002    10 PROD 1    106,864    16 Ok

A0002    11 PROD 1    92,700    9     Not Ok

A0002    12 PROD 1    104,236    13 Not Ok

A0002    13 PROD 1    78,315    7     Not Ok

A0002    14 PROD 1    106,306    15 Ok

A0002    15 PROD 1    64,354    2     Not Ok

A0002    16 PROD 2    96,883    10 Not Ok

A0002    17 PROD 2    66,499    3     Not Ok

A0002    18 PROD 2    107,417    17 Ok  

A0003    1     PROD 1    59,651    2     Ok

A0003    2     PROD 1    98,532    16 Ok

A0003    3     PROD 2    64,956    5     Ok

A0003    4     PROD 2    97,861    15 Ok

A0003    5     PROD 1    90,512    13 Ok

A0003    6     PROD 1    102,611    19 Ok

A0003    7     PROD 1    104,848    21 Ok

A0003    8     PROD 1    56,427    1     Ok

A0003    9     PROD 2    107,909    27 Ok

A0003    10 PROD 1    73,739    7     Ok

A0003    11 PROD 2    73,739    8     Ok

A0003    12 PROD 1    81,565    12 Ok

A0003    13 PROD 1    102,164    17 Ok

A0003    14 PROD 2    66,499    6     Ok

A0003    15 PROD 1    107,417    26 Ok

A0003    16 PROD 1    59,651    3     Ok

A0003    17 PROD 1    78,315    10 Ok

A0003    18 PROD 1    106,306    23 Ok

A0003    19 PROD 1    64,354    4     Ok

A0003    20 PROD 1    104,848    22 Ok

A0003    21 PROD 1    102,164    18 Ok

A0003    22 PROD 1    74,849    9     Ok

A0003    23 PROD 1    106,864    25 Ok

A0003    24 PROD 1    92,700    14 Ok

A0003    25 PROD 1    104,236    20 Ok

A0003    26 PROD 1    78,315    11 Ok

A0003    27 PROD 1    106,306    24 Ok

;

data have2(rename=(agentcode=code));

infile cards missover  ;

  input Channel &$6. @7 Segment$ @12 AgentCode$ Target Floor;

  cards;

Chnl 1 3MOB A0001 11 6

Chnl 1 6MOB A0002 22 13

Chnl 1 9MOB A0003 26 15

;

proc means data=have(where=(Count in ( 'Ok')) sum nway ;

var Amount;

class Code Prod Count ;

output out=sums sum=/autoname;

run;

PROC TRANSPOSE DATA=sums

  OUT=trans1

  PREFIX=Freq_

  NAME=Source

  LABEL=Label

;

  BY Code;

  ID Prod;

  VAR _FREQ_;

run;

PROC TRANSPOSE DATA=sums

  OUT=trans2

  PREFIX=amount_

  NAME=Source

  LABEL=Label

;

  BY Code;

  ID Prod;

  VAR Amount_Sum;

run;

PROC FORMAT

  LIB=WORK

;

    VALUE code

      LOW -< 100 = "Since less than 100% hence highest amounts above floor will only be considered"

      100 - HIGH = "Since 100% achievement hence everything will be included";

RUN;

data trans1_2;

  merge trans1 trans2 have2;

  by code;

  totalunits=sum(Freq_PROD_1,Freq_PROD_2,floor);

  target_perct=totalunits/target*100;

  comment=put(target_perct,code.);

  format amount_PROD_1 amount_PROD_2 comma8.;

run;

proc print;

run;

Thanks,

Jag

Thanks,
Jag
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 249 views
  • 3 likes
  • 3 in conversation