An Index variable problem?

[Mirisage]

Hi SAS Forum,

There are three accoutns in my example data set, 111, 222 and 333.

They repeateldly occur in different months.

     DATA HAVE;

     INPUT YEAR_MONTH acct_NO DWO_BALANCE AUTHORIZED_LIMIT;

     CARDS;

     201201 111 0    20000

     201201 222 1000 5000

     201201 333 0    8000

     201202 111 1500 15000

     201202 222 1000 5000

     201202 333 0    8000

     201203 111 1500 15000

     201203 222 1000 3000

     201203 333 0    8000

     201204 111 1500 10000

     201204 222 1000 4000

     201204 333 1800 8000

     ;

     RUN;

     /*Question:

     I need to calculate the average of "AUTHORIZED_LIMIT" of all accounts when each acct_NO first shows up a

greater than zero value for  "DWO_BALANCE".

Answer:

9333

How this answer came?

acct_NO 111 first hits a greater than zero DWO_BALANCE in 201202. At that time its

AUTHORIZED_LIMIT is 15000.

acct_NO 222 first hits a greater than zero DWO_BALANCE in 201201. At that time its

AUTHORIZED_LIMIT is 5000.

acct_NO 333 first hits a greater than zero DWO_BALANCE in 201204. At that time its

AUTHORIZED_LIMIT is 8000.

Therefre the average is $9333.

My effort:

     PROC SORT DATA=HAVE OUT=TT;

         BY  acct_NO  YEAR_MONTH;

     RUN;

DATA WANT_1;

     SET TT;

IF  DWO_BALANCE > 0 THEN DWO_COUNT =1;

ELSE DWO_COUNT =0;

RUN;

PROC SORT DATA=WANT_1 OUT=WW;

         BY  acct_NO  YEAR_MONTH   DWO_COUNT;

     RUN;

DATA WANT;

SET WW;

BY acct_NO  YEAR_MONTH   DWO_COUNT;

IF FIRST.DWO_COUNT;

.......

......

Actually my imagination skills are poor to understand how to move forward from here.

Could anyone please help me?

Thanks

Mirisage

[Linlin · Accepted solution]

one way:

DATA HAVE;

     INPUT YEAR_MONTH acct_NO DWO_BALANCE AUTHORIZED_LIMIT;

     CARDS;

     201201 111 0    20000

     201201 222 1000 5000

     201201 333 0    8000

     201202 111 1500 15000

     201202 222 1000 5000

     201202 333 0    8000

     201203 111 1500 15000

     201203 222 1000 3000

     201203 333 0    8000

     201204 111 1500 10000

     201204 222 1000 4000

     201204 333 1800 8000

     ;

     RUN;

proc sort data=have(where=(dwo_balance>0)) out=temp;

by acct_NO year_month;

run;

data temp1;

   set temp;

   by acct_NO;

   if first.acct_NO;

  run;

  proc sql;

    select mean(authorized_limit) as mean

         from temp1;

   quit;

[art297]

Or you could do the calculation during the datastep as well.  e.g.:

PROC SORT DATA=HAVE (where=(DWO_BALANCE ne 0)) OUT=TT ;

    BY  acct_NO  YEAR_MONTH;

RUN;

DATA WANT (keep=average);

     SET TT end=eof;

     by acct_no;

     IF first.acct_no;

     numerator+AUTHORIZED_LIMIT;

     denominator+1;

     if eof then do;

       average=numerator/denominator;

       output;

    end;

run;

[MikeZdeb]

hi ... another idea ...

proc summary data=have (where=(dwo_balance)) nway;

class acct_no;

output out=new (keep=authorized_limit) idgroup(min(year_month) out(authorized_limit)=);

run;

proc means data=new mean;

run;

[Ksharp]

Here is one way.

DATA HAVE;
     INPUT YEAR_MONTH acct_NO DWO_BALANCE AUTHORIZED_LIMIT;
     CARDS;
     201201 111 0    20000
     201201 222 1000 5000
     201201 333 0    8000
     201202 111 1500 15000
     201202 222 1000 5000
     201202 333 0    8000
     201203 111 1500 15000
     201203 222 1000 3000
     201203 333 0    8000
     201204 111 1500 10000
     201204 222 1000 4000
     201204 333 1800 8000
     ;
     RUN;
data _null_;
 set have;
 array a{3} _temporary_ ;
 array v{3} _temporary_ ;
 if DWO_BALANCE gt 0 and acct_NO not in a then do;
                                         n+1;
                                         v{n}=AUTHORIZED_LIMIT;
                                         a{n}=acct_NO;
                                       end;

 if n=3 then do;
                mean=mean(of v{*});
                put 'Mean :  ' mean; stop;
              end;
run;

Ksharp

[Mirisage]

Hi Linlin, Art, MikeZdeb and Ksharp,

Thank you every each one of you for this help.

Each method is working nicely.

Such a diverse range of clever methods!
Regards
Mirisage

[Haikuo]

The more, then merrier. Here is a pure SQL approach:

DATA HAVE;

     INPUT YEAR_MONTH acct_NO DWO_BALANCE AUTHORIZED_LIMIT;

     CARDS;

     201201 111 0    20000

     201201 222 1000 5000

     201201 333 0    8000

     201202 111 1500 15000

     201202 222 1000 5000

     201202 333 0    8000

     201203 111 1500 15000

     201203 222 1000 3000

     201203 333 0    8000

     201204 111 1500 10000

     201204 222 1000 4000

     201204 333 1800 8000

     ;

     RUN;

     proc sql;

      select mean(AUTHORIZED_LIMIT) as AVG from

       (select AUTHORIZED_LIMIT from have where DWO_BALANCE ne 0

              group by acct_NO having YEAR_MONTH=min(YEAR_MONTH));

    quit;

Haikuo

[Mirisage]

Hi Haikuo,

This exclusivley SQL code is great!

Thank you very much.

Best regards

Mirisage