@Markus04  Are you sure? According to the on-line docs https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/n0sc92audsisvsn1jbd0fx3nealw.htm SAS/ACCESS Interface to Postgres supports bulk loading! ... View more

Hi @eramirez  Have a look at these three papers REST at Ease with SAS®: How to Use SAS to Get Your REST The ABCs of PROC HTTP REST Just Got Easy with SAS® and PROC HTTP They should have what you are looking for and more ... View more

One added information SAS Help Center: BULKLOAD= LIBNAME Statement Option Notes: Support for Microsoft SQL Server, Spark in HDFS, and Yellowbrick was added in SAS 9.4M7. Support for Informix and for Spark in Databricks was added in SAS 9.4M9. ... View more

Hi @js5  Have a look at this Databricks Blog  https://www.databricks.com/blog/2022/03/16/how-to-speed-up-data-flow-between-databricks-and-sas.html   ... View more

If you are generating text files (*.txt), they you would be better off using Linux native tools for comparing these text file, such as $diff file1 file2 Hope this helps ... View more

Hi @Mahis  Try adding this line at the start of your SAS script libname _all_ list ; It will write to the Log all the assigned library references  at the time of execution.   Keep in mind, SAS WORK library is typically auto-assigned via specifying its root location in  sasv9.cfg file sasv9_local.cfg file -WORK <SomePath> command line option Check your deployment/execution settings are not negatively affecting where the -WORK is pointing to   Hope this helps ... View more

Try this data have; q1=1; q2=0; q3= 2; q4=3; q5=4; output; q1=0; q2=0; q3= 0; q4=0; q5=0; output; q1=0; q2=0; q3=-9; q4=0; q5=-7; output; run; /* This should work with your data set of 20 vars */ data want; set have; if (sum(of q:)=0) then flag=0; else if (max(of q:) > 0) then flag=1; else if (sum(whichn(-9, of q:),whichn(-8, of q:),whichn(-7, of q:) ) > 0) then flag=-1; run; ... View more

Personally, I think integrating DuckDB internally at the C++ level might do SAS Software lots of good, be it Viya or SAS 9.4 (one can dream) Because interrogating/inspecting Parquet metadata via DuckDB, can be as simple as issuing  summarize (select * FROM read_parquet('path-to\green_tripdata_2024-12.parquet')); ┌──────────────────────┬─────────────┬─────────────────────┬───┬──────────────────────┬───────┬─────────────────┐ │ column_name │ column_type │ min │ . │ q75 │ count │ null_percentage │ │ varchar │ varchar │ varchar │ │ varchar │ int64 │ decimal(9,2) │ ├──────────────────────┼─────────────┼─────────────────────┼───┼──────────────────────┼───────┼─────────────────┤ │ VendorID │ INTEGER │ 1 │ . │ 2 │ 53994 │ 0.00 │ │ lpep_pickup_datetime │ TIMESTAMP │ 2008-12-31 23:08:01 │ . │ 2024-12-21 20:30:4. │ 53994 │ 0.00 │ │ lpep_dropoff_datet. │ TIMESTAMP │ 2009-01-01 00:07:45 │ . │ 2024-12-21 20:50:3. │ 53994 │ 0.00 │ │ store_and_fwd_flag │ VARCHAR │ N │ . │ NULL │ 53994 │ 3.67 │ │ RatecodeID │ BIGINT │ 1 │ . │ 1 │ 53994 │ 3.67 │ │ PULocationID │ INTEGER │ 1 │ . │ 97 │ 53994 │ 0.00 │ │ DOLocationID │ INTEGER │ 1 │ . │ 229 │ 53994 │ 0.00 │ │ passenger_count │ BIGINT │ 0 │ . │ 1 │ 53994 │ 3.67 │ │ trip_distance │ DOUBLE │ 0.0 │ . │ 3.0810886161036284 │ 53994 │ 0.00 │ │ fare_amount │ DOUBLE │ -250.08 │ . │ 20.183429813078977 │ 53994 │ 0.00 │ │ extra │ DOUBLE │ -5.0 │ . │ 2.158148364635992 │ 53994 │ 0.00 │ │ mta_tax │ DOUBLE │ -0.5 │ . │ 0.5 │ 53994 │ 0.00 │ │ tip_amount │ DOUBLE │ -0.9 │ . │ 3.801633161332969 │ 53994 │ 0.00 │ │ tolls_amount │ DOUBLE │ -6.94 │ . │ 0.0 │ 53994 │ 0.00 │ │ ehail_fee │ DOUBLE │ NULL │ . │ NULL │ 53994 │ 100.00 │ │ improvement_surcha. │ DOUBLE │ -1.0 │ . │ 1.0 │ 53994 │ 0.00 │ │ total_amount │ DOUBLE │ -251.08 │ . │ 27.643610072829308 │ 53994 │ 0.00 │ │ payment_type │ BIGINT │ 1 │ . │ 2 │ 53994 │ 3.67 │ │ trip_type │ BIGINT │ 1 │ . │ 1 │ 53994 │ 3.68 │ │ congestion_surcharge │ DOUBLE │ -2.75 │ . │ 2.75 │ 53994 │ 3.67 │ ├──────────────────────┴─────────────┴─────────────────────┴───┴──────────────────────┴───────┴─────────────────┤ │ 20 rows 12 columns (6 shown) │ └───────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ Run Time (s): real 0.266 user 0.312500 sys 0.031250 ... View more

@Andrew-M  While the SAS/ACCESS engines in all their flavors try to avoid truncation due to hard limit of 32767 in Base SAS data set (*.sas7bdat), specially when dealing with extra large character columns, this could be a double-edge thing!! it could lead to over/under provisioning!! Add to it, Byte vs. Character representation -- I had that issue with Snowflake and utf-8 encoding! That's why I would try to profile my source table(s) either before (if possible) or after ingestion, and change the structure of my SAS data set to make it as lean as possible, using a custom developed SAS macro program.  You can  find it here on GitHub sasCensusApi/code/macros/etl/etl_shrinkmydata.sas at main · ahmedanadwc/sasCensusApi   Hope this helps, Ahmed ... View more

Hi @pgielen  I don't think storing users credentials in the Metadata would be a workable solution! Just think about what would you do, if and when users change their Passwords? how would you keep your metadata in sync? just my two cents ... View more

Hi @mk1804  One thing to keep in mind is Viya SWAT package functions would return/send Pandas "Dataframe" object, well it's SASDataFrame which is a subclass of pandas.DataFrame to your Python process.   Without knowing which version of the SWAT package you are using, the SWAT docs indicates pandas.DataFrame v 0.192.2 being used!    Therefore, I would highly recommend you look into techniques/ways to reduce your pandas DataFrame memory footprint.  https://www.sparkcodehub.com/pandas/advanced/memory-usage-guide   Here are additional resources that might be of use to you and other readers Getting Started with Python Integration to SAS® Viya® - Part 2 - Working with CAS Actions and CASResults Objects Getting Started with Python Integration to SAS® Viya® - Part 14 - CAS Table to DataFrame    Hope this helps, Ahmed ... View more

I'll report back once I have some working code samples. Note: I have had success with using the DuckDB CLI from SAS 9.4 via the xcmd interface and executed *.sql script that was dynamically modified by SAS. but Data Transfer between the two software was a bit cloggy, as I had to export to CSV from DuckDB and Import it in SAS to generate SAS data set(s).    But now, using JDBC/ODBC would allow for better integration specially specially with using Explicit DuckDB SQL Statements 😉 ... View more

@JuanS_OCS  Just as a bonus, I managed to successfully connect SAS 9.4 M8 to DuckDB via SAS/ACCESS Interface to ODBC on Linux (RHEL 8.1) using DuckDB's 1.3.0.0 ODBC driver for Linux  😉 ... View more

@jarno  Thank you for this informative article, I would be interested in reading about testing Community DuckDB extensions, similar to what you have done with selective Core Extensions.  I wonder, which version of DuckDB this SASIODUK engine is based on? I hope it's 1.3.0+, How will it keep up with the DuckDB future releases?   ... View more

@jjyang20  I just tried and worked! 129 OPTIONS 130 FULLSTIMER /* Writes all available system performance statistics to the SAS log */ 131 SORTEQUALS /* Controls how PROC SORT orders observations with identical BY values in the output data set */ 132 OBS=MAX /* Specifies when to stop processing observations or records */ 133 MSGLEVEL=I /* Controls the level of detail in messages that are written to the SAS log */ 134 SPOOL /* Controls whether SAS writes SAS statements to a utility data set in the WORK data library */ 135 NOSYNTAXCHECK/* Does not enable syntax check mode for statements that are submitted within a */ 136 /* non-interactive or batch SAS session. */ 137 COMAMID=TCP/* Identifies the communications access method for connecting a client and a server across */ 138 /* a network */ 139 AUTOSIGNON/* Automatically signs on to the server when the client issues a remote submit request */ 140 /* for server processing. */ 141 VALIDVARNAME=ANY /* Allows the use of column names that contain embedded spaces and special characters */ 142 NOTES 143 /*SOURCE 144 SOURCE2*/ 145 MPRINT 146 DEBUG=DBMS_SELECT/* shows only the select statements generated */ 147 sastrace=',,t,dsab' /* Display a summary of all generated SQL code, any threaded read/write operations, */ 148 /* and timing summary & details. */ 149 sastraceloc=saslog 150 nostsuffix 151 DLCREATEDIR /* Specifies to create a directory for the SAS library that is named in a LIBNAME statement */ 152 /* if the directory does not exist. */ 153 ; 69 %util_parseScaproc(p_inScaprocFileName=%str(&g_logsRoot/sca__analyze.txt) 70 , p_outDotFilePath=%str(&g_outputRoot) 71 , p_outDotFileName=analyze.dot 72 , p_outDsName=work.analyze 73 , p_includeAttrs_yn=Y, p_includeSteps_yn=Y, p_rankDir=LR) L_SCAPROCFILENAME=sca__analyze.txt L_SCAPROCFOLDERNAME=/home/anawarehousing/dotMySASProgram/logs/ MPRINT(UTIL_PARSESCAPROC): DATA work.scadata; MPRINT(UTIL_PARSESCAPROC): INFILE "/home/anawarehousing/dotMySASProgram/logs/sca__analyze.txt" LRECL=1000 LENGTH=linelength truncover END=eof; MPRINT(UTIL_PARSESCAPROC): LENGTH scaline $1000 linenum step 5; MPRINT(UTIL_PARSESCAPROC): INPUT @; MPRINT(UTIL_PARSESCAPROC): INPUT scaline $varying1000. linelength; MPRINT(UTIL_PARSESCAPROC): linenum+1; MPRINT(UTIL_PARSESCAPROC): if (_n_=1 OR index(scaline,'STEP SOURCE FOLLOWS')) then step+1 ; MPRINT(UTIL_PARSESCAPROC): RUN;     ... View more