SAS: how to find out subsequent second highest entry for each variable column?

Question

I have a data set given in CSV format as follows:

Policy_Number,var1,var2,var3,Exposure
1,B,H,J,191
2,B,F,Unknown,174
3,C,Unknown,I,153
4,B,G,L,192
5,Unknown,E,Unknown,184
6,D,E,K,113
7,C,Unknown,I,140
8,A,H,I,133
9,C,F,I,194
10,Unknown,G,Unknown,105
11,B,H,L,172
12,A,Unknown,I,198
13,D,E,K,155
14,Unknown,G,K,177
15,B,H,Unknown,100
16,D,Unknown,J,176
17,B,E,I,112
18,Unknown,E,J,192
19,C,Unknown,K,146
20,C,G,Unknown,187

i want to find out the highest and Second highest 'Exposure' values for each Variable that is Var1, Var2, Var3.

subsequently the output file must look like:

VariableName  VariableValue  Max_Exposure  Exposure_Percentage
   Var1            A             198
   Var1            A             133
   Var1            B             192
   Var1            B             191
   Var1            C             194
   Var1            C             187
   Var1            D             176
   Var1            D             155
   Var1          Unknown         192           
   Var1          Unknown         184
   Var2            E             192
   Var2            E             184
   Var2            F             194
   Var2            F             174
   Var2            G             192
   Var2            G             187
   Var2            H             191
   Var2            H             172
   Var2          Unknown         198
   Var2          Unknown         176
   Var3            I             198
   Var3            I             194
   Var3            J             192
   Var3            J             191
   Var3            K             177
   Var3            K             155
   Var3            L             192
   Var3            L             187
   Var3          Unknown         184
   Var3          Unknown         194

Where Exposure_Percentage is the percentage of the exposure of each VariableValue.

i am doing it with a very raw approach. i am creating 3 tables and finding out the Maximum and second maximum exposure for each table and then i am merging those 3 tables. but if the VariableName are increased that is more columns are introduced in the data then my approach will fail. the code for the same be like as follows:

data try2;
infile 'complex.csv' dsd dlm = ',' FIRSTOBS = 2;
Length Policy_Number Var1 $ 10 Var2 $ 10 Var3 $ 10 Exposure 3;
input Policy_Number $ Var1 $ Var2 $ Var3 $ Exposure;
run;

proc sort data = try2;
by Var3 descending Exposure;
run;
data need2;
set try2;
by Var1;
if first.var1 then n=0;
n+1;
if n=1 then output;
if n=2 then output;
run;

Please show me possible solution to such problems. Thanks in advance.

Answer 1

I've used proc summary to calculate the highest 2 exposures, utilising the idgroup function. This puts the values in 2 columns rather than 2 rows, so a subsequent data step is required to transform the data into the required format. The initial layout does make it easier to calculate the percentage within the data step however.

/* input csv data */
data try2;
infile datalines dsd;
Length Policy_Number Var1 $ 10 Var2 $ 10 Var3 $ 10 Exposure 3;
input Policy_Number $ Var1 $ Var2 $ Var3 $ Exposure;
datalines;
1,B,H,J,191
2,B,F,Unknown,174
3,C,Unknown,I,153
4,B,G,L,192
5,Unknown,E,Unknown,184
6,D,E,K,113
7,C,Unknown,I,140
8,A,H,I,133
9,C,F,I,194
10,Unknown,G,Unknown,105
11,B,H,L,172
12,A,Unknown,I,198
13,D,E,K,155
14,Unknown,G,K,177
15,B,H,Unknown,100
16,D,Unknown,J,176
17,B,E,I,112
18,Unknown,E,J,192
19,C,Unknown,K,146
20,C,G,Unknown,187
;
run;

/* calculate highest 2 exposures for each variable */
proc summary data=try2;
class var: ;
ways 1;
output out=want (drop=_:) idgroup(max(exposure) out[2] (exposure)=) / autoname;
run;

/* merge Var fields into 1 and output 1 row per exposure */
data want1;
set want;
_expsum = sum(of exposure:); /* sum exposures to calculate percentages later on */
array vars var:; /* create array of Var variables */
array exp exposure:; /* create array of Exposure variables */
do i=1 to dim(vars); /* loop through Var variables and keep non-missing values */
    if not missing(vars{i}) then do;
        variablename=vname(vars{i});
        variablevalue=vars{i};
    end;
end;
do j=1 to dim(exp); /* loop through Exposures, output each one and calculate percentage of total */
    max_exposure = exp{j};
    exposure_percentage = exp{j}/_expsum;
    output;
end;
format exposure_percentage percent10.1;
drop var1--var3 exposure_1 exposure_2 _expsum i j; /* drop unwanted variables */
run;

/* sort data in required order */
proc sort data=want1;
by variablename variablevalue descending max_exposure;
run;

Answer 2

So firstly define how many variables there are:

%let num_vars=3;

Start the datastep and define arrays to hold the variable names from the first observation and also the values per line.

data IN(keep=VARIABLENAME VARIABLEVALUE EXPOSURE);
  array VAR(&num_vars) $10.;
  array VAL(&num_vars) $10.;

    retain VAR1-VAR&num_vars.;
    length VARIABLENAME VARIABLEVALUE $8 EXPOSURE 8.;

  infile cards dsd ;
    if _n_ = 1 then do;
        input;
        do i = 1 to &num_vars.;
            VAR(i) = scan(_infile_,i+1,",");
        end;
    end;

This is the input step which reads in the variables and then stores them in an array before outputting them below:

  input POLICY_NUMBER @ ;
  do i = 1 to &num_vars;
    VARIABLENAME=VAR(i);
    input VAL(i) $ @;
  end;
  input EXPOSURE;

  do i = 1 to &num_vars;
    VARIABLENAME=VAR(i);
    VARIABLEVALUE=VAL(i);
    output;
  end;

cards;
Policy_Number,var1,var2,var3,Exposure
1,B,H,J,191
2,B,F,Unknown,174
3,C,Unknown,I,153
4,B,G,L,192
5,Unknown,E,Unknown,184
6,D,E,K,113
7,C,Unknown,I,140
8,A,H,I,133
9,C,F,I,194
10,Unknown,G,Unknown,105
11,B,H,L,172
12,A,Unknown,I,198
13,D,E,K,155
14,Unknown,G,K,177
15,B,H,Unknown,100
16,D,Unknown,J,176
17,B,E,I,112
18,Unknown,E,J,192
19,C,Unknown,K,146
20,C,G,Unknown,187
;
run;

Sort the variables in the order you want:

proc sort data=IN; by VARIABLENAME VARIABLEVALUE descending EXPOSURE ; run;

Keep the first 2 instances of max values by using a CNT variable.

data GET_MAX(drop=CNT);
set IN;
by VARIABLENAME VARIABLEVALUE descending EXPOSURE;
  if first.VARIABLEVALUE then call missing(of CNT);

  CNT+1;
  if CNT in (1,2);
run;

Then calculate your percentage:

proc sql;
create table OUT as
  select VARIABLENAME
            , VARIABLEVALUE
            , EXPOSURE
            , (EXPOSURE/sum(EXPOSURE))*100 as EXPOSURE_PERCENTAGE format=8.2
    from
        GET_MAX
    group by 1,2
;quit;

For using proc summary to get a sum:

proc summary data=GET_MAX nway;
var EXPOSURE    ;
class VARIABLENAME VARIABLEVALUE;
output out=work.TEST(drop=_TYPE_ _FREQ_ )
sum=EXPOSURE_SM ;
run;