FTP .csv file from Mainframe(SAS) to FTP location(Windows server)

[renjithr]

Hi,

I am trying to FTP a mainframe dataset in .csv format to a ftp location.I used the extension as .csv in the mainframe sas code.The problem is when I open the file in the ftp server(file opens in excel) one record is spread over

multiple rows.

eg:I need the data to be like this:

John California Los Angeles

instead the data is opening in excel like this

john

california

los angeles

I tried to correct this by using the text-to-column option in excel,but it does not help.I think this something to do with the way I ftp data from mainframe to the ftp location.

Below is the code I ran to FTP the file:

%MACRO MCASPLT(MCA1,FNME1,F1);

DATA _NULL_;

  %LET TEXT_FLG=N;

  %PUT "TEXT FLG B4 LOOP:" &TEXT_FLG;

  SET DATAOT1.NBARI;

  IF MCA = "&MCA1" THEN DO;

  CALL SYMPUT('TEXT_FLG', 'Y');

  %PUT "TEXT FLG IN LOOP:" &TEXT_FLG;

   FILE &FNME1  DSD DLM=",";

   PUT VCA  ADMDT DISDT ADMTYPE_NM ADMSRC_NM DISP_NM AGE

       ED_IND PAT_ENC_CSN_ID MDC SCHED EXPIRED BASECLASS SEX

       LOS  BMI35 HSP_ACCOUNT_ID

       ADT_PATIENT_STAT_C PAT_CLASS  INP_ADM_DT

       ;

  END;

RUN;

%IF &TEXT_FLG  =  Y  %THEN %DO;

     %LET OUT_LRECL=3000;

     %LET CD=fff\ggg\fdfdf\TEST;

     %LET HLQO=AAAA.Y3012.XAAA.&F1..TXTOUT;

     %LET OUT_NAME=XAAA.&F1..CSV;

     FILENAME NCHS01 "&HLQO" DISP=SHR;

        FILENAME NCHS FTP

                 "&OUT_NAME"

                 &HOST_PORT_USER_PASS

                 CD = "&CD"

                 LRECL = &OUT_LRECL

                 ;

         RUN;

         DATA _NULL_;

           INFILE NCHS01 SHAREBUFFERS;

           FILE NCHS;

           INPUT;

           PUT _INFILE_;

        RUN;

   %END;

%MEND;

Please share your thoughts.

Thanks.

[Ksharp]

Did you try the BINARY  ftp option when you use FILENAME FTP ?

BINARY

is fixed-record format. Thus, all records are of size LRECL with no line delimiters.

Data is transferred in image (binary) mode.

The BINARY option overrides the value of RECFM= in the FILENAME FTP

statement, if specified, and forces a binary transfer.

Alias: RECFM=F

Interaction: If you specify the BINARY option and the S370V or S370VS option,

then SAS ignores the BINARY option.

[NYSPhil]

The problem is that ODS CSV generates a hex '15' to separate records, and this hex character does not translate well when copying data from mainframe to servers.  I add RS=NONE to my ODS statement when generating a CSV file on the mainframe.  This forces SAS to write a separate line of data for each report line.  You can then transfer your CSV file down to the server as a plain text file.

[Tom]

There are a number of ways to deal with this.

1) Without changing your SAS code you can open the file from within Excel and then tell it what character to use as a delimiter.

If you use a different extension than .csv then Excel will normally NOT try to import it without first giving you the option to specify format and control how it treats the columns.  This is what I normally do as Excel has nasty habit of removing leading zeros from character values that look like number as other worse modifications to your data.

2) I do not see ODS CSV in your sample code.  You seem to be writing the data using FILE statement with a comma delimiter.  Commas should translate normally using ASCII FTP mode mode.  Where is the '15' coming from?  Is it on every line? between every field?

3) If the issue really is that the commas are appearing as '15'x then could add a line in your data step that is pulling from the FTP server and writing to a local file to translate that character.  For example this will translate '15'x into comma.

_infile_ = translate(_infile_,',','15'x);

[Newbie_SAS]

Thank you, NYSPhil. Option RS=NONE worked magic for me.

I am using XMITIP to send ODS CSV file created on mainframe as email. Without RS option, lines were breaking incorrectly when i open the attachment.

Thanks once again!

[Peter_C]

Why not write directly into the FTP file from the first datastep?