07-24-2012 01:39 PM
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
eg:I need the data to be like this:
John California Los Angeles
instead the data is opening in excel like this
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:
%PUT "TEXT FLG B4 LOOP:" &TEXT_FLG;
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
%IF &TEXT_FLG = Y %THEN %DO;
FILENAME NCHS01 "&HLQO" DISP=SHR;
FILENAME NCHS FTP
CD = "&CD"
LRECL = &OUT_LRECL
INFILE NCHS01 SHAREBUFFERS;
Please share your thoughts.
07-24-2012 10:14 PM
Did you try the BINARY ftp option when you use FILENAME FTP ?
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.
Interaction: If you specify the BINARY option and the S370V or S370VS option,
then SAS ignores the BINARY option.
08-16-2012 01:54 PM
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.
08-16-2012 02:08 PM
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);
06-27-2013 02:10 PM
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!