@jimbarbour Just as an idea that might be worth trying (never done it myself):  What if you send these 3 set statements via the dbconinit options of the libname statement. The option is documented as being available for the ODBC engine. DBCONINIT= LIBNAME Statement Option libname myive ODBC ... dbconinit=" set hive.resultset.use.unique.column.names = FALSE; set hive.execution.engine = &Engine; set mapred.job.name = &Type._Members_Direct; SET CURRENT SQLID='myauthid' " ; Then use the libref also in the connect statement to always use the same connection definition however you need it. connect using myhive;   Update: Just realised that this was an old discussion. Still: Getting some confirmation what actually works would be nice. ... View more

@EyalGonen  Your question how SAS figures out that this is a path under SAS Content made me realize that what I wrote about a libname can't be true.  I did run the code with the libname statement but got an error stating that I'm not allowed to execute it when the environment is under lockdown. This error "masked" that SAS was anyway not searching under SAS Content. The filename statement got a new FILESRVC Access Method for SAS content but there the use of this engine defines that it's under SAS Content.   ... View more

@EyalGonen Actually.... I believe you're right and SAS Content is not really suitable for storing .sas7bdat file (for accessing them via a libname statement).  Sorry! I'm still getting up-to-speed with Viya so take whatever I say with a grain of salt.  I'll try to figure out next week if and which migration path there is for metadata bound libraries or which alternative approach could be used to functionally replace it. Would like to know myself. ... View more

@MMSTAT You're passing wrong parameter values to your macro. Compare it with the macro call I've already posted. The 2nd parameter must be the target table name (<libref>.<table name>) and the 3rd parameter may only include the parameter value (yes/no). ... View more

I believe with SAS Viya you can use this area as if it were a normal file system - but it isn't and only accessible via SAS and the SAS security layer.   ... View more

In Viya there is the SAS Content (folders) where things are no more directly stored on the file system but in a database that you only can access via the SAS security layer. Doesn't this make metadata bound libraries functionally unnecessary?  ... View more

As long as you define the libref (libname statement) before you use it you're good to go. With your code this could look like: options dlcreatedir; libname target "/home/u50158717/results"; %importXLSX(myExcel,target.mySASTable,yes);   ... View more

The SAS SQL flavour doesn't support windowing functions.  If your source data all exists in SQL server and you can connect to it via SAS then you could still use your existing code via explicit pass-through SQL and then just load the result set back to SAS to create the target table.  If that's not possible then I believe you need some SAS data step post processing to replicate this logic. Below an attempt to replicate such logic - but of course untested so might have gotten it wrong. proc sql; create table blah as .... .... order by a.POLICY_ENDORSEMENT_SK, cc.TKZ_ID; quit; data blah; set blah; by POLICY_ENDORSEMENT_SK TKZ_ID; TKZ_COUNT=0; if first.TKZ_ID then do; if POLICY_STATUS_ID = 5 THEN TKZ_COUNT=1; else if POLICY_STATUS_ID = 3 THEN TKZ_COUNT=-1; else if ENDORSEMENT_TYPE_CODE = 28 THEN TKZ_COUNT=1; end; run; ... View more

Also an elapsed time of 6 days is "excessive". Browsing through your code my first guess is that you're reading/writing from/to network drives with some really low throughput.    When it comes to performance investigation and tuning then the first thing you need to know is which steps consume the most time.  Please set below options at the beginning of your program and then share the full SAS log with us. options source2 mprint mprintnest fullstimer;   Update: Looking into the log you shared all the step execute within a fraction of a second so it's likely like others already mentioned that you're calling these steps within a macro many many time. Like others already stated changing the approach/design to process all the data in one go (by group processing) will highly likely improve performance massively.   The log you've shared also uses Proc Printto with a log line number of 427333. There is no such printto in the SAS code you shared so there is certainly something missing that you haven't told us yet. This also is supported by the fact that the sums of the real time steps in the log are nowhere near to the total elapsed time of your program (219:37:24.15). NOTE: PROCEDURE PRINTTO used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 427333 proc printto; run;     ... View more

If you don't intent to remotely spawn SAS processes via sFTP then I can't think of any reason why the SAS Metadata Server should be involved. SAS files (tables) as such are just files on a file system. To open them you need of course software that can open them and you would need to download them to the client side for opening (eg. with the SAS Universal Viewer).  What worked for me over many years for development in a SAS client/server environment (whether Grid or not) is a combination of SAS EG/Studio, WinSCP and Putty (or MobaXterm).  A scp or sFTP connection without any SAS layer involved has also the advantage that it's still working when the SAS services are down.  ... View more

So you just need to be able to create an sFTP connection to one of the grid nodes where Lustre is mounted with a user that got sufficient permissions to browse the file system and you should be good to go. No SAS involved.    Answer from chatGPT:   To establish an SFTP (Secure File Transfer Protocol) connection to a server, several components and settings must be enabled: SFTP Server: The server must have an SFTP server software or service running. This is typically part of the SSH server package, such as OpenSSH, which supports SFTP by default. SSH Access: Since SFTP operates over SSH, the SSH service must be enabled on the server. Ensure that the SSH daemon (e.g., sshd on Linux) is running. Firewall Rules: Ensure that the firewall settings on the server allow incoming connections on port 22 (the default port for SSH and SFTP). If the server is using a different port for SSH, you need to configure the firewall to allow that port. User Permissions: The user account you are using to connect must have the necessary permissions to access the directories and files you want to transfer. Ensure that the user is correctly configured on the server. SFTP Client: On the client side, you need an SFTP client application or command-line tool. Common options include command-line tools like sftp or graphical clients like FileZilla or WinSCP. Authentication: The authentication method must be set up correctly. This usually involves: Password Authentication: Ensure that the user’s password is correct and that the server allows password-based authentication (this can be disabled for security reasons). Public Key Authentication: If using public key authentication, ensure that the server has the client’s public key in the ~/.ssh/authorized_keys file and that the private key is properly configured on the client side. Configuration Files: Ensure that the server’s SSH configuration file ( /etc/ssh/sshd_config on Linux) allows SFTP connections. Look for lines like Subsystem sftp /usr/lib/openssh/sftp-server or similar, and verify that SFTP is enabled. By ensuring these components and settings are properly configured, you can successfully establish an SFTP connection to a server. ... View more

Except for the SAS binaries and some config files wouldn't most of your files/data be stored on a shared file system like gpfs, lustre, etc. that's mounted to the grid nodes? If so then where comes SAS into play for 3rd party sftp access to the shared file system? ... View more

@bhca60 One way to debug: Use option mprint and then inspect the log to see what SAS code where the macro generated.    The macro executes before the data step executes. It then resolves and generates text that in your case should be valid SAS data step syntax.  The generated syntax will get executed at the place in your data step code where you call the macro.   If you run below sample code.... %macro demo(); flag=1; %mend; options mprint; data test; set sashelp.class; if name='Louise' then do; %demo(); end; run; options nomprint; ....then the SAS log shows you what SAS syntax where has been generated and executed.   Just do the same with your code. ... View more