topic Re: SFTP server - import csv file in SAS Enterprise Guide

SFTP server - import csv file

Pato485 — Fri, 17 Mar 2023 16:13:56 GMT

Hello, how can i import csv file from table from database to sftp server? For example i have this information and need to import csv file there:

Username: Pandas

Host: 37.9.169.9

Password: XXX

Thanks.

Re: SFTP server - import csv file

Tom — Fri, 17 Mar 2023 16:23:39 GMT

Your description seems to have left out a number of steps in the data flow.

Are you saying you have a CSV file that can be accessed via SFTP? Can you access the file using normal SFTP commands or tools?

So the first step is get a fileref created using the SFTP engine.

filename CSV sftp ..... ;

You can test if it is working by trying to read the file.

data _null_;
  infile csv obs=5;
  input;
  list;
run;

Once that works then to make a dataset just write a data step that reads the file.

So something like this.

data want;
  infile csv dsd firstobs=2 truncover;
  length var1 8 var2 $30 .... varlast 8 ;
  informat var10 date.;
  format var10 date9.;
  input var1 -- varliast;
run;

If you want to ask SAS to make a guess at how to read the file by using PROC IMPORT then you might need to first copy the file to a local file. I am not sure if PROC IMPORT can handle reading a file that is using the SFTP engine.

Re: SFTP server - import csv file

Pato485 — Fri, 17 Mar 2023 16:33:19 GMT

thanks for information, i dont have csv file, i have table in database and need to transform it to csv and send it to to sftp server.

Re: SFTP server - import csv file

Tom — Fri, 17 Mar 2023 17:16:53 GMT

So the goal is to EXPORT data to the SFTP server, not IMPORT data from the SFTP server.

But I am also not clear what you mean by a "table" in a "database". Are you talking about a SAS dataset? Or are you connecting your SAS session to some external database system (such as Oracle or Redshift) and you want to extract the data from that database and create a CSV file version of the data?

Example 3 in the documentation shows how to write a file to an SFTP server. https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lestmtsglobal/p0xln1fiwsr340n1xxf4mkmfxp6f.htm

To create a CSV from a dataset you could use a simple data step, especially simple if you don't need to write a header line.

data _null_;
  set have;
  file csv dsd ;
  put (_all_) (+0);
run;

Or you could use PROC EXPORT to write a CSV file.

If the data is coming from an external database and you have made a libref that points to the database schema you can treat a "table" in that database as if it was a SAS dataset.

Re: SFTP server - import csv file

Pato485 — Tue, 21 Mar 2023 08:17:20 GMT

so how can i program it if i have a table work.DUP and need to import it as csv to sftp server with this info: Username: Pandas

Host: 37.9.169.9

Password: XXX

I tried, but not working

Re: SFTP server - import csv file

Kurt_Bremser — Tue, 21 Mar 2023 10:28:02 GMT

Please post the complete (all code and messages) log of the code you used by copy/pasting it into a window opened with this button: