topic Re: An Index variable problem? in SAS Procedures

An Index variable problem?

Mirisage — Tue, 11 Sep 2012 20:51:56 GMT

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

Re: An Index variable problem?

Linlin — Tue, 11 Sep 2012 21:06:10 GMT

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;

Re: An Index variable problem?

art297 — Tue, 11 Sep 2012 21:09:41 GMT

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;

Re: An Index variable problem?

MikeZdeb — Tue, 11 Sep 2012 22:57:28 GMT

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;

Re: An Index variable problem?

Ksharp — Wed, 12 Sep 2012 02:33:22 GMT

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

Re: An Index variable problem?

Mirisage — Sat, 15 Sep 2012 18:35:05 GMT

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

Re: An Index variable problem?

Haikuo — Sat, 15 Sep 2012 23:07:12 GMT

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

Re: An Index variable problem?

Mirisage — Sun, 16 Sep 2012 17:14:34 GMT

Hi Haikuo,

This exclusivley SQL code is great!

Thank you very much.

Best regards

Mirisage