It looks like you are reading this data from an external database. Do you know what it is? You can most likely speed this up by converting it to an SQL Passthru query that uses database-specific SQL. Also your WHERE statement should be just a DATETIME selection and not include functions. Something like this:
%let Bill_Date = %sysfunc(intnx(MONTH, %sysfunc(today()), -1, BEGINNING), date9.);
%put Bill_Date = &Bill_Date;
proc sql;
create table want as
select *
from have
where first_login > "&Bill_Date.:00:00:00"dt;
quit;
A lot of times, these large, exponential increases in time are due to network latency. The dataset is on a NAS drive or SAN, and you are trying to pull the data to your local machine across VPN. Do as suggested and force the processing to the server or some place local to the dataset. Ping the server where the dataset is located and you will probably find your culprit. 28M recs is not that much but the time indicates a lot of back and forth over a network.
You are connecting to a remote database through vpn.
Try to minimise data transfer. Therefore the query must be executed as sql pass through as suggested by @SASKiwi
From your code, it looks as if your dataset is in WORK (single level name). Is your VPN purely on your connection to a SAS server, or do you actually run SAS on your PC and connect to the data through the VPN?
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Check out this tutorial series to learn how to build your own steps in SAS Studio.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.