I managed to refactor the workflow and reduce the upload time down to 40 minutes. If it becomes necessary again, I will look into implementing your suggestion. Thanks! ... View more

I will investigate this further, thanks! The permanent library from which the export is supposed to happen is a network share. It being the limiting factor would be interesting as creating the same dataset on the same network location, with CHAR compression enabled, only takes ~15 minutes. Our SAS runs on EC2 so the work library resides on EBS. Checking with a small subset of data does not show significant performance differences vs exporting to the network share on which the dataset is stored. I could also export to FSx and see if that helps. Additionally, the export runs through a view because I need to re-format date and datetime variables into a format understood by spark. Could this be causing the high computational demand? ... View more

The problem with writing handcrafted export code is that I need to do this for close to 100 datasets. proc export and/or ds2csv offer the advantage of being universally usable without the need of knowing the dataset. ... View more

I tried the .gzip route as we do not have SAS/CONNECT licensed. While it reduced the size of the generated file to ~500 MB, it also took almost 10 hours. Given that uploading and importing the file to databricks only take approx. 20 minutes each, this is not a favourable exchange. ... View more

SAS is running on a server. It fetches the data from a remote server and exports the .csv into SAS' workdir (I am using filename temp). I do not think that network bandwidth is the issue, as the download speed goes up-and-down, indicative of network transfer waiting for something. The reason I am using %ds2csv instead of proc export is the fact that the latter does not apply sufficient quoting for values with line breaks in them.  ... View more

The CONTENTS Procedure Data Set Name xxxxxxxx.yyyyy_yyyyyyy_v3 Observations 142213132 Member Type DATA Variables 39 Engine V9 Indexes 0 Created 11/10/2025 12:19:06 Observation Length 802 Last Modified 11/10/2025 12:19:06 Deleted Observations 0 Protection Compressed CHAR Data Set Type Reuse Space NO Label Point to Observations YES Data Representation WINDOWS_64 Sorted NO Encoding utf-8 Unicode (UTF-8) Engine/Host Dependent Information Data Set Page Size 65536 Number of Data Set Pages 801994 Number of Data Set Repairs 0 ExtendObsCounter YES Filename xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx\yyyyy_yyyyyyy_v3.sas7bdat Release Created 9.0401M7 Host Created X64_DSRV19 Owner Name aaaaaaaaaaaaaaa File Size 49GB File Size (bytes) 52559544320 Alphabetic List of Variables and Attributes # Variable Type Len Format Label 39 a Char 2 28 b Char 5 29 c Char 5 30 d Char 5 31 e Char 5 32 f Char 5 25 g Char 5 33 h Char 5 26 i Char 5 21 j Char 5 24 k Char 5 22 l Char 5 23 m Char 5 27 n Char 5 11 COMMENT Char 100 COMMENT 12 CONTROLs Char 2 CONTROLs 36 COUNT Num 8 Frequency Count 14 Cells per Well Num 8 3 EXPERIMENT NAME Char 50 EXPERIMENT NAME 10 EXPERT COMMENT Char 100 EXPERT COMMENT 2 FILE NAME Char 50 FILE NAME 8 FlowJo VERSION Char 10 FlowJo VERSION 38 Frequency Num 8 34 o Char 5 9 PANEL ID Char 20 PANEL ID 4 PATIENT ID Char 20 PATIENT ID 7 PROTOCOL ID Char 50 PROTOCOL ID 16 RFSTDT Num 4 E8601DA. 15 SAMPLE DATE Num 4 E8601DA. 6 SAMPLE ID Char 20 SAMPLE ID 5 SAMPLE TYPE Char 20 SAMPLE TYPE 17 Day Num 3 35 p Char 5 13 Visit Char 20 18 basename Char 100 20 q Char 10 19 r Char 10 37 count_sum_carstate Num 8 1 source Char 100 ... View more

The overwhelming majority of updates are inserts. Updates are far and between, and deletes are even rarer than that. Regarding the size, as an example, the latest update would be approx. 2M new rows. Storage in SQL is done for sharing with others, yes. Adding new licenses is not an option unfortunately. ... View more

Group=MEMORY SORTSIZE=2147483648 Specifies the amount of memory that is available to the SORT procedure. SUMSIZE=0 Specifies a limit on the amount of memory that is available for data summarization procedures when class variables are active. MAXMEMQUERY=0 Specifies the maximum amount of memory that is allocated for procedures. MEMBLKSZ=16777216 Specifies the memory block size for Windows memory-based libraries. MEMMAXSZ=2147483648 Specifies the maximum amount of memory to allocate for using memory-based libraries. LOADMEMSIZE=0 Specifies a suggested amount of memory that is needed for executable programs loaded by SAS. MEMSIZE=4294967296 Specifies the limit on the amount of virtual memory that can be used during a SAS session. REALMEMSIZE=0 Specifies the amount of real memory SAS can expect to allocate. We can probably request to have a bit more memory if needed, I believe the VM has 64 GB. I do not know the delta, I would have to compare it with SQL. Alternatively, I could compare it with the dataset stored in the permanent lib which is currently getting copied to MSSQL, but I would need to generate the update to another lib or rename it. Deletes are very infrequent but cannot be excluded entirely. Sorting by unique key is not implemented yet but could probably be done without too much effort. The dataset is partially sorted already due to several merge statements. Unique key would add up to 195 characters at the moment, I guess some sort of hash might help reduce this. I am currently storing the final dataset on disk. The current process is to generate the final dataset in SAS, store in in a permanent "staging" library, and subsequently copy it to MSSQL. ... View more

I did engage both SAS and Databricks tech support, but I am exploring alternatives in parallel. ... View more

The job copies data from SQL server bound as catalog to another schema. We are testing direct upload via ODBC in parallel, but unfortunately the performance is insufficient. ... View more