DATA Step, Macro, Functions and more

Count of variables that is before another variable per unique account number

Reply
New Contributor
Posts: 3

Count of variables that is before another variable per unique account number

Hi all.

 

I have the following dataset:

 

Account_no   Date    Status

A123   15-Mar-16     Canceled

A123   15-Mar-16     Declined

A123   07-Feb-17      Declined

A123   10-Apr-17      Finalized

A134   20-Apr-16      Canceled

A134   04-May-17     Canceled

A134   05-Oct-17      Declined

A134   06-Mar-18     Finalized

 

I would like to calculate the amount of applications, per account_no , made 1 day before the application,1 week before the application and 1 year before the application ,also the amount of canceled applications 1 day before the application,1 week before the application and 1 year before the application .

 

I hope this makes sense.

 

Thank you for your help .

 

PROC Star
Posts: 1,580

Re: Count of variables that is before another variable per unique account number

Posted in reply to AnsoneHuman

HI @AnsoneHuman Can you please post a sample of your expected output for your input sample and a few words explaining the logic in deriving the result you want? Thank you

New Contributor
Posts: 3

Re: Count of variables that is before another variable per unique account number

Posted in reply to novinosrin
Account_noDateStatusCount Same DayCount 1Day beforeCount 1Week beforeCount 1Month beforeCount 1Year beforeCount Cancelled Same DayCount Cancelled 1Day beforeCount Cancelled 1Week beforeCount Cancelled 1Month beforeCount Cancelled 1Year before
A12315-Mar-16Canceled00000 000 
A12315-Mar-16Declined1000010000
A12307-Feb-17Declined0000200001
A12310-Apr-17Finalized0001300001
A13420-Apr-16Canceled0000000000
A13404-May-17Canceled0000100001
A13405-Oct-17Declined0000200002
A13406-Mar-18Finalized0000200001

 

With Same day -I mean on the same day

With 1Day before- I mean in the last day

With 1Month before -I mean in the last month

With 1Year before-I mean in the last year

 

I hope this is what you are looking for

New Contributor
Posts: 3

Re: Count of variables that is before another variable per unique account number

Posted in reply to AnsoneHuman

Was anyone able to solve this problem?

Ask a Question
Discussion stats
  • 3 replies
  • 68 views
  • 0 likes
  • 2 in conversation