BookmarkSubscribeRSS Feed
Augusto
Obsidian | Level 7

 

 

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

3 REPLIES 3
DCL
Obsidian | Level 7 DCL
Obsidian | Level 7

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?

Augusto
Obsidian | Level 7
Sorry, i meant say 180 WANT_mmdd. That is equal to 6 daily generated WANT file.

Yes. I need to know for this period (6 months) how many times the client has already had in revolving, so i really need to consolidate all the WANT% and then set revolving.
But please consider the WANT% are large tables, i must use the best performance to this issue.
DCL
Obsidian | Level 7 DCL
Obsidian | Level 7

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!

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 954 views
  • 0 likes
  • 2 in conversation