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?
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: