BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mirisage
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

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

7 REPLIES 7
Linlin
Lapis Lazuli | Level 10

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
Opal | Level 21

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
Rhodochrosite | Level 12

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
Super User

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
Obsidian | Level 7

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
Onyx | Level 15

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
Obsidian | Level 7

Hi Haikuo,

This exclusivley SQL code is great!

Thank you very much.

Best regards

Mirisage

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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