Version 7.1 Looking for some help, my only co-worker left a few weeks ago and I have to have this data pulled for billing our vendors. Admittedly, I probably don’t know as much as I should for this analyst role but it was forced on me after we lost someone and I had been learning from my only team member previously. Using SQL in SAS. I’m trying to pull the last 4 years of usage history from a dataset containing 28 million rows. The problem is I can’t get it to run in less than 12 hours before I get kicked off the VPN. Last month the table had 27 million and it ran just fine but this month no luck. There is a lot more to this code but this is the part that takes the longest. %datacon; %let billmo = 10; %let billyr = 2021; Proc sql; Create table prior4yr Select * From usage_history Where year(datepart(first_login)) > sum((&billyr.,-4) or (year(datepart(first_login)) = sum(&billyr., -4) and month(datepart(first_login)) > &billmo.); Quit; Is there a way to rewrite this so it doesn’t have to look through all the 28 million rows in usage_history? If it’s helpful at all, I pull this data every month so I do have an older copy of my prior4yr table saved and I really only need to pull in data from the last 2 months to add to prior4yr.
... View more