## An Index variable problem?

Solved
Super Contributor
Posts: 338

# 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".

9333

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.

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;

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: 8,169

## 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,787

## 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

Posts: 3,167

## 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.