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
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;
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;
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;
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;
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
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
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
Hi Haikuo,
This exclusivley SQL code is great!
Thank you very much.
Best regards
Mirisage
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.