About MikeR_Indy_

MikeR_Indy_ · ‎01-28-2014

That's a fantastic idea. It hadn't occurred to me. This is what i've got . . . We will most likely add 30 new variables, but ultimately this is what i'm looking for. Thanks! data DSLC; set work.callcount; retain date date2 date3 date4 date5 date6 DSLC; by ANI CallDate; if first.ani then do; DSLC = .; DSLC2 = .; DSLC3 = .; date=CallDate; date2=CallDate; date3=Calldate; date4=CallDate; date5=calldate; date6=calldate; end; DSLC=CallDate-date; DSLC2=DATE-DATE2; DSLC3=DATE-DATE3; DSLC4=DATE-DATE4; DSLC5=DATE-DATE5; DSLC6=DATE-DATE6; date=CallDate; date2=calldate-DSLC; date3=calldate-DSLC2; date4=CALLDATE-DSLC3; date5=CALLDATE-DSLC4; DATE6=CALLDATE-DSLC5; run;

MikeR_Indy_ · ‎01-28-2014

I'm a newer user to the SAS world so there very well may be an easy way to do this, but at the moment i'm stuck. I need to calculate the number of days between my records. Currently I've got some code worked out that will solve for the Days to Next Contact (DTNC) and Days Since Last Contact (DSLC). That code is below. What I need is code that will solve for additional contacts in either direction. Stated in another way, given contacts 1 through 5, I need to be able to look a the third contact and say "it was W days earlier that contact 1 happened, X days earlier that contact two happened, Y days later that contact 4 happened ,and Z days later that contact 5 happened." My metrics DSLC and DTNC take care of contacts 2 and 4, but i'm lost trying to solve for 1 and 5. Any help would be GREATLY appreciated. Thanks, -Mike The date I need to reference is "CALLDATE". I create the variable DATE for the purpose of calculation. proc sort /*CallCount*/ data= work.callcount; by ANI CallDate; run; data DSLC; /*from WEEKS - creates DSLC*/ set work.callcount; retain date; by ANI; if first.ani then do; DSLC = .; date=CallDate; end; DSLC=CallDate-date; date=CallDate; run; data DSLC2; /*clears DSLC if only 1 call on record*/ set work.DSLC; by ANI; if count=1 then DSLC=.; run; data DTNC1; /*Creates counter for DTNC2*/ set work.DSLC2; retain order; by ANI; if first.ani then do; order = count+1; end; order=order-1; run; proc sort /*DTNC*/ data=work.DTNC1; by ani order; run; data DTNC2; /*Creates DTNC - From DTNC1*/ set work.DTNC1; retain day; by ANI; if first.ani then do; DTNC = .; day=CallDate; end; DTNC=day-CallDate; day=CallDate; run; proc sql; /*creates DTNC3 - From DTNC2*/ create table DTNC3 as Select ANI, CustNumber, Guide, AcctType, CallDate, CallTime, COUNT, DSLC, DTNC From work.DTNC2; quit; data MASTER; /*Creates MASTER - From DTNC3 - Clears DTNC if only 1 call on record*/ set work.DTNC3; by ANI; if count=1 then DTNC=.; run;

MikeR_Indy_ · ‎09-30-2013

I need to solve for the amount available on the account. We only record the balance in a snapshot . . . The BEGINNING BALANCE table gives me a moment in time where i know the account balance. If someone starts with 500 on their account a transaction of 0 is fine. Without the Beginning balance i'd see that as a free transaction. I'll try the query you suggested. Thanks Reeza.

MikeR_Indy_ · ‎09-30-2013

Sure . . . BEGINNING_BALANCE account_Number Date Available_Balance Currency_type 1234567 09SEP2013 0.00 DL 2345678 09SEP2013 0.00 DL SMALL_FIN Account_Number Date trans_amt Currency_type Relation_Num 2345678 11SEP2013 100.00 DL 1 2345678 11SEP2013 0.00 DL 2 ELIGIBLE_Account Account_Number Date Relation_Num 2345678 11SEP2013 1 2345678 11SEP2013 2 So given the above data, i'd want my results to look like this: Account Number date Currency_type Relation Number Net_Balance 2345678 11SEP2013 DL 2 0.00 Since account 1234567 isn't on the ELIGIBLE_ACCOUNT table it isn't included in the results. Since relation 1 on account 2345678 has a trans_amt > 0.00 it isn't included but relation 2 is. Does that help?

MikeR_Indy_ · ‎09-30-2013

Let me start by saying I'm new. Very new. I have three data sets: BEGINNING_BALANCE has account_number, date, available_Balance, and currency_type. SMALL_FIN has account_number, date, currency_type, relation_number, and transaction_amt. ELIGIBLE_ACCOUNT has account_number, date, and relation_number. I need to know if an eligible transaction occurred without a fee. In order to solve for this, I need to take the available_balance from the BEGINNING_BALANCE table and then add all eligible transactions to date listed on the SMALL_FIN table to get the actual amount available when the transaction occurred. This is further complicated because it's possible for one account to have multiple transactions on the same day. So i have to make sure that my results show the total amount available on the account for each unique account number, date, and relation number combination. I need to make sure that the relation numbers from ELIGIBLE_ACCOUNT match SMALL_FIN. I know logically what needs to happen but when i try to work it out in SQL i get awful results. I've gotten far enough to create these three tables from larger disparate data sources but now i'm stuck. Any help would be really appreciated. Please let me know if i need to clarify anything or if I've left out details you may need. Again, thanks for any help, I really appreciate it. C

Online Status	Offline
Date Last Visited	‎09-01-2015 07:11 AM

Re: Solving for days between records

Solving for days between records

Re: Using Proc SQL to sum based on three conditions

Re: Using Proc SQL to sum based on three conditions

Using Proc SQL to sum based on three conditions

Re: Solving for days between records

Solving for days between records

Re: Using Proc SQL to sum based on three conditions

Re: Using Proc SQL to sum based on three conditions

Using Proc SQL to sum based on three conditions