Nr of days between dates selection with specific condition of calculation

[Jariny]

Hello,

I would like to ask you help me with one excercise.

I want to calculate nr of days between given Dates selection (date when some transaction happened) for each customer id applying additional conditions:

1. Check if the difference is higher than 90 days, if yes, put only 90

2. If customer continues with the transactions then start counting from beggining and flag this a s a new section

3. Check the last day for which this calculation is valid 31.12.2015

Example of the output is attached.

Can you please write the SAS code which would give me desire output?

Thanks a lot

Jariny

[RW9 · Accepted solution]

Hi,

Firstly, and most important, post test data in the form of a datastep, see this post if you need help:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

I will not touch attached files.

Secondly, this is a Q&A board, we are not here to do your homework/work for you.  Some suggestions on the questions you have posted:

I want to calculate nr of days between given Dates selection

-- If your dates are numeric SAS dates, then you can get the difference in days by substracting from the latest one the earliest one:

  days=end_date-start_date;

Or you could use the function INTNX.

1. Check if the difference is higher than 90 days, if yes, put only 90

--From the above, this is a simple logic check:

  if days > 90 then days=90;

Once you get better at SAS you can combine the above two checks using ifn:

  days=ifn(end_date-start_date>90,90,end_date-start_date);

2. If customer continues with the transactions then start counting from beggining and flag this a s a new section

-- I do not know what you mean here - this is why test data and examples are really required.  I would guess, from keeping things over to the next row you would want to look at either lag() function, or retain a variable.

3. Check the last day for which this calculation is valid 31.12.2015

-- How do you know what is the last valid day?  Could be done by by groups if your data is grouped, or end of file if its last one etc.  Without further examples hard to say.

[ballardw]

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.