SFTP server - import csv file

Pato485 · Posted 03-17-2023 12:13 PM

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.

Tom · Posted 03-17-2023 12:23 PM

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.

Pato485 · Posted 03-17-2023 12:33 PM

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.

Tom · Posted 03-17-2023 01:16 PM

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.ht...

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.

Pato485 · Posted 03-21-2023 04:17 AM

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