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