topic Re: please - help with sas code in SAS Procedures

please - help with sas code

Augusto — Thu, 15 Oct 2015 11:04:15 GMT

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

Re: please - help with sas code

DCL — Thu, 15 Oct 2015 05:29:19 GMT

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?

Re: please - help with sas code

Augusto — Thu, 15 Oct 2015 11:01:06 GMT

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.

Re: please - help with sas code

DCL — Fri, 16 Oct 2015 18:46:24 GMT

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!