Export SAS DataSet to UNIX as a text file .... delimited by '~ | ~ '

I am trying to export a SAS dataset to a UNIX folder as a delimited text file as "~ | ~".

Here is the code I'm using....
PROC EXPORT DATA=Exp_TXT
         OUTFILE="/fbrms01/dev/projects/tadis003/Export_txt_OF_New.txt"
         DBMS=DLM REPLACE;
     DELIMITER="~|~";
     PUTNAMES=YES;
RUN;

      

Here is the result I get on UNIX ..... Missing part of the delimiter in the data, but getting the full delimiter in variable names ....

Num~|~Name~|~Age
1~A~10
2~B~11
3~C~12

      

Any idea why I am getting the delimiter part only in the data?

Thanks Sam.

+2


source to share


3 answers


I guess it PROC EXPORT

doesn't support the use of multiple character delimiters. Column separators are usually just one character. Thus, you will probably need to write your own code to do this.

PROC EXPORT

for delimited files, generates old old SAS code which is then executed. You should see the code in the SAS log from where you can grab it and change it as needed.



See my answer to this other question for a SAS macro that might help you. You cannot use it exactly as written, but it should help you create a version that suits your needs.

0


source


The issue is referenced in the SAS manual page for the FILE statement http://support.sas.com/documentation/cdl/en/lestmtsref/63323/HTML/default/viewer.htm#n15o12lpyoe4gfn1y1vcp6xs6966.htm

Restriction:Even though a character string or character variable is accepted, only the first character of the string or variable is used as the output delimiter. The FILE DLM= processing differs from INFILE DELIMITER= processing.

      



However, there is (according to some version, one way or another) a new expression, DLMSTR. Unfortunately, you cannot use DLMSTR in PROC EXPORT, but if you cannot easily write variables, you can create a log from PROC EXPORT and insert it into your program and change DELIMITER to DLMSTR. You can even do it dynamically - use PROC PRINTTO to create a log file, then read in that file, parse the line numbers and code, change DELIMITER to DLMSTR and% include the code.

0


source


Since you are using unix, why not use unix tools to fix this?

You can invoke the unix command from your sas program using the X instruction: http://support.sas.com/documentation/cdl/en/hostunx/61879/HTML/default/viewer.htm#xcomm.htm

after export, use sed to fix the file

PROC EXPORT DATA=Exp_TXT
         OUTFILE="/fbrms01/dev/projects/tadis003/Export_txt_OF_New.txt"
         DBMS=DLM REPLACE;
     DELIMITER="~";
     PUTNAMES=YES;
RUN;

X sed 's/~/~|~/g' /fbrms01/dev/projects/tadis003/Export_txt_OF_New.txt > /fbrms01/dev/projects/tadis003/Export_txt_OF_New_v2.txt ;

      

Depending on your unix, it might need to be configured, but this works on AIX. Some versions of sed can use the -i flag for in-place editing, so you don't need to type the filename twice.

This is a much simpler and simpler one-line solution than a large macro.

0


source







All Articles