BookmarkSubscribeRSS Feed
InnovativeMinds
Fluorite | Level 6

Hi All

 

Require help in building the SAS logic. 

 

Below is an example data and using the first 5 columns as inputs, needs to produce last 3 columns.

 

For both 15th Dec and 16th Dec, only two transactions should be considered for the logic.
so the amount would be 200+50 = 250
 
for 15th Dec transaction:
last 7 to 15 days means only first 4 rows fall into that category.
Out of those 4 two are eliminated because their return dates (14th Dec and 12th Dec) fall into the category of return date <= transactiondate-2.

Thanks for the help in advance.

 

AccountNoTransactionDateAmountReturnFlagReturnDateLast7days_to_15Days_CountsLast7days_to_15Days_AmountLast7days_to_15Days_Average
25-Dec-1750.    
26-Dec-17100114-Dec-17   
27-Dec-17150112-Dec-17   
28-Dec-17200116-Dec-17   
215-Dec-17250116-Dec-172250125
216-Dec-17300117-Dec-172250125
4 REPLIES 4
MarkWik
Quartz | Level 8

@InnovativeMinds Asking for code favor is not bad or wrong? But what have you tried or at least attempted something ?

InnovativeMinds
Fluorite | Level 6

No, it's not bad asking for help. Do you think users come here without trying? 

CharlotteCain
Quartz | Level 8

All that mark was asking is to show what you have tried and get help to fix or modify your solution. That's all and it didn't sound rude at all. I have been told that too in the past and more in a rude manner by many but i took that as an affirmative. And yes, many users do get here taking advantage of other's time. Unfortunately that happens all the time.

Reeza
Super User

Do you only have a single record per day? 

If so, PROC EXPAND with a CONVERT statement will do what you want. You may need to add a record for every day, but you can do that easily with PROC TIMESERIES.

 

If you need further help, please provide the code you've attempted thus far and your data, preferably as a data step. 

 

This is a commonly asked question, so if you don't have SAS/ETS search for moving statistics via an array or DoW loop and you'll find many answers. 

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 872 views
  • 0 likes
  • 4 in conversation