Help using Base SAS procedures

An Index variable problem?

Accepted Solution Solved
Reply
Super Contributor
Posts: 338
Accepted Solution

An Index variable problem?

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


Accepted Solutions
Solution
‎09-11-2012 05:06 PM
Super Contributor
Posts: 1,636

Re: An Index variable problem?

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;

View solution in original post


All Replies
Solution
‎09-11-2012 05:06 PM
Super Contributor
Posts: 1,636

Re: An Index variable problem?

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;

PROC Star
Posts: 7,471

Re: An Index variable problem?

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;

Valued Guide
Posts: 765

Re: An Index variable problem?

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;

Super User
Posts: 10,023

Re: An Index variable problem?

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

Super Contributor
Posts: 338

Re: An Index variable problem?

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

Respected Advisor
Posts: 3,156

Re: An Index variable problem?

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

Super Contributor
Posts: 338

Re: An Index variable problem?

Hi Haikuo,

This exclusivley SQL code is great!

Thank you very much.

Best regards

Mirisage

🔒 This topic is solved and locked.

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

Discussion stats
  • 7 replies
  • 327 views
  • 6 likes
  • 6 in conversation