Hello everyone,
I have a table (HAVE) that needs to be joinned with others 180 (WANT_YYMMDD) sas data sets. The WANT_YYMMDD is generated daily, so i need to join with the last 6 months WANTs data sets and then to make a sum of the following results:
if total_pay / total_bill - 1 le 0.99 then revolving = 1; else revolving = 0;
The idea is to count how many times the client has already had in revolving in the last six months.
i imagine to do it in a loop.
small example.
data want;
merge
have (in = _a)
want_yymmdd (in = b);
by id;
if total_pay / total_bill - 1 le 0.99 then revolving = 1; else revolving = 0;
run;
-- this small example is missing the sum of the variable revolving
Observation: Its likely that there is one or more month with 27 or less WANT_YYMMDD data set
Thanks alot
Not sure if I got your issue:
You want to join with 240 WANT_mmdd datasets OR you want to merge last 6 months daily generated WANT files?
columns in HAVE and WANT%?
Do you want to consolidate all the total_pay and total_bill from all the WANT% datasets and then set 'revolving' based on that?
I am still not sure on the structure of Have and want datasets. Assuming that Have will have the client_name and want_mmdd contains client_name, bill and pay value, here is a dataset to consolidate the pay and bill values.
%let cur_month=%sysfunc(month(%sysfunc(today())));
data result;
retain pay_idx 1 bill_idx 2 ;
array agg[3,2] 8 _temporary_; /* As an example i took 3 clients.*/
if _n_=1 then do;
dcl hash clients();
clients.defineKey('client_name');
clients.defineData('client_idx');
clients.defineDone();
do until(clientsEof);
set have end=clientsEof;
client_idx+1;
clients.add();
end;
end;
%do month=%sysevalf(&cur_month.-6) %to &cur_month.;
%do date=1 %to 31;
%if %sysfunc(exist(work.want_%sysfunc(putn(&month.,z2.))%sysfunc(putn(&date.,z2.)))) %then %do;
do until (eof);
set want_%sysfunc(putn(&month.,z2.))%sysfunc(putn(&date.,z2.)) end=eof;
clients.find();
agg[client_idx,pay_idx]=sum(agg[client_idx,pay_idx],pay);
agg[client_idx,bill_idx]=sum(agg[client_idx,bill_idx],bill);
end;
%end;
%end;
%end;
/*use client_idx to query the summarized value for each client */
agg_bill=agg[client_idx,bill_idx];
agg_pay=agg[client_idx,pay_idx];
/* Calculate here */
run;
Not sure if this helps!
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.