topic Re: how to use SAS to transfer an Excel file (xlsx) from a windows server (source) to unix server (d in SAS Enterprise Guide

how to use SAS to transfer an Excel file (xlsx) from a windows server (source) to unix server (dest)

alepage — Thu, 11 Nov 2021 21:31:29 GMT

Hello,

Before, we were using IBM Control-M to transfer an excel file from a windows server (the source) to a Unix server (destination) and for some reason we don't know the file is being corrupted.

So I wonder if there is a nice way, not to read , just transfer an excel file from a windows server to unix server using SAS EG:

Original site validation data
Current version: 9.04.01M7P080520

CPU A: Model name='' model number='' serial=''.
Expiration: 29JUN2022.
Grace Period: 15 days (ending 14JUL2022).
Warning Period: 19 days (ending 02AUG2022).
System birthday: 22JUL2021.
Operating System: LIN X64 .
Product expiration dates:
---Base SAS Software 29JUN2022 (CPU A)
---SAS/STAT 29JUN2022 (CPU A)
---SAS/GRAPH 29JUN2022 (CPU A)
---SAS Integration Technologies 29JUN2022 (CPU A)
---SAS/Secure 168-bit 29JUN2022 (CPU A)
---SAS Enterprise Guide 29JUN2022 (CPU A)
---SAS/ACCESS Interface to Oracle 29JUN2022 (CPU A)
---SAS/ACCESS Interface to PC Files 29JUN2022 (CPU A)
---SAS/ACCESS Interface to ODBC 29JUN2022 (CPU A)
---SAS Workspace Server for Local Access 29JUN2022 (CPU A)
---High Performance Suite 29JUN2022 (CPU A)
2 The SAS System 08:52 Thursday, November 11, 2021

---SAS Add-in for Microsoft Excel 29JUN2022 (CPU A)
---SAS Add-in for Microsoft Outlook 29JUN2022 (CPU A)
---SAS Add-in for Microsoft PowerPoint 29JUN2022 (CPU A)
---SAS Add-in for Microsoft Word

Re: how to use SAS to transfer an Excel file (xlsx) from a windows server (source) to unix server (d

Reeza — Fri, 12 Nov 2021 01:24:43 GMT

https://blogs.sas.com/content/sasdummy/2020/05/19/copy-files-in-sas-eg/

Is the Copy Files task an option?

Re: how to use SAS to transfer an Excel file (xlsx) from a windows server (source) to unix server (d

SASKiwi — Fri, 12 Nov 2021 01:37:37 GMT

EG isn't a good tool for scheduling file transfers between servers. How is Control-M doing the transfers? FTP perhaps? If you ensure FTP is using a binary transfer then it is unlikely your spreadsheet will be corrupted.

Re: how to use SAS to transfer an Excel file (xlsx) from a windows server (source) to unix server (d

alepage — Fri, 12 Nov 2021 02:16:06 GMT

That exactly what we are using. But for some reason we don't know, the field that contains a date and time format value is converted to a date field only during the transfer. We don't know why.

Re: how to use SAS to transfer an Excel file (xlsx) from a windows server (source) to unix server (d

alepage — Fri, 12 Nov 2021 02:17:19 GMT

yes copy files task could be an option just one time just to see the code generated. However, I would like to develop a SAS program that will do the job. I wonder if a Unix command could also be usefull.

My file is located on P:\transfert . How can I know, what's the real address of that. What's about using a Unix command. Could someone provide an example if my windows server is P:\temp\transfer

Re: how to use SAS to transfer an Excel file (xlsx) from a windows server (source) to unix server (d

Tom — Fri, 12 Nov 2021 02:55:19 GMT

@alepage wrote:
yes copy files task could be an option just one time just to see the code generated. However, I would like to develop a SAS program that will do the job. I wonder if a Unix command could also be usefull.

My file is located on P:\transfert . How can I know, what's the real address of that. What's about using a Unix command. Could someone provide an example if my windows server is P:\temp\transfer

Well I doubt that your Windows PC has a physical drive that is using drive letter P:. So most likely that drive letter is mapped to some network share. Have your computer support mount that shared drive on the Unix host somewhere. Then just read the file directly using the path on the Unix host that points to the same place that your Windows machine is using as the P: drive.

So if P: is mapped to \\hostname\sharename then have them mount \\hostname\sharename somewhere in the Unix filesystem. Let's say they mounted it at /data/hostname/sharename/. Then your SAS program can just be something like:

libname myfile xlsx '/data/hostname/sharename/transfert/myfile.xlsx';
proc copy inlib=myfile outlib=work;
run;

Re: how to use SAS to transfer an Excel file (xlsx) from a windows server (source) to unix server (d

alepage — Fri, 12 Nov 2021 03:23:10 GMT

I have use the file transfer task which works

Now, I can I convert that into a sas code to make it work

%let _egcopySource = \\...\test.xlsx;
%let _egcopyTarget =/folder1/Temp;

Remember that I am trying to transfer an xlsx file.

Re: how to use SAS to transfer an Excel file (xlsx) from a windows server (source) to unix server (d

AlanC — Fri, 12 Nov 2021 04:50:42 GMT

That doesn't make a lot of sense. How are you reading the file back in? It doesn't sound like an issue with the file transfer but the way the file is being read on the dest side.

Re: how to use SAS to transfer an Excel file (xlsx) from a windows server (source) to unix server (d

Kurt_Bremser — Fri, 12 Nov 2021 06:28:43 GMT

@alepage wrote:
That exactly what we are using. But for some reason we don't know, the field that contains a date and time format value is converted to a date field only during the transfer. We don't know why.

This cannot be an FTP issue. Xlsx files are zip-compressed archives, so ftp would have to unpack, change the value, and re-compress the file. Which it can't do. If ftp changed anything, the whole file would be corrupted and unusable.

How did you check the file on the UNIX side after the transfer?

PS the "change" might come from the guessing of PROC IMPORT. That's why you should not use Excel files when consistency in a process is expected.

Re: how to use SAS to transfer an Excel file (xlsx) from a windows server (source) to unix server (d

alepage — Mon, 15 Nov 2021 15:57:54 GMT

We have made some change into Control-M, i.e. changing append for overwrite and the issue seems to be solve.

Thanks everyone for your help.

However, it will be nice to get some SAS code to transfer a file from a Windows server to a Unix server, it could be interesting.

Regards