2

I'm trying to export a SAS data set on to UNIX folder as a text file with delimiter 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 output I'm getting on UNIX.....Missing part of delimiter in the data but getting whole delimiter in variable names....

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

Any idea why I'm getting part of delimiter in the data only????

Thanks, Sam.

shellter
  • 36,525
  • 7
  • 83
  • 90
SAS_learner
  • 521
  • 1
  • 13
  • 30
  • probably some component in the system doesn't get the idea of a multiple character field delim. What's wrong with just '|'? Good luck. – shellter Jan 30 '13 at 16:42
  • My users have the code which imports text file with '~|~' as delimiter in to their systems....so need '|' doesn't work for them.... – SAS_learner Jan 30 '13 at 18:22

3 Answers3

0

My guess is that PROC EXPORT does not support using multiple character delimiters. Normally, column delimiters are just a single character. So, you will probably need to write your own code to do this.

PROC EXPORT for delimited files generates plain old SAS code that is then executed. You should see the code in the SAS log, from where you can grab it and alter it as needed.

Please 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 meets your needs.

Community
  • 1
  • 1
BellevueBob
  • 9,498
  • 5
  • 29
  • 56
  • Hello Bob,Thanks for your reply and your code helped me a lot.Addition to that how to export with dynamic variable names for example if I have 2 data sets with same number of vars but different var names...how can I get that??Thanks in advance.... – SAS_learner Jan 30 '13 at 18:02
  • I just revised my answer to the other question. The new version of the macro should do what you want. I even show an example using the data set and target file in your question. – BellevueBob Jan 30 '13 at 20:44
  • Thanks bob....Your update gave me exactly what I wanted...I appreciate your help...Learned a lot today :) – SAS_learner Jan 30 '13 at 22:01
  • No problem, happy to help. If this answer is helpful and meets your needs, please "accept" it so it will no longer appear "unanswered". And welcome to StackOverflow! – BellevueBob Jan 30 '13 at 23:08
  • Hi Bob,One more small issue...may be very small for you.I'm new to macros so I couldn't figure it out...Here is the data 1 FL 03/12/86 Sam 3000 nothing After that code run output is showing trailing blanks.Here is the output. 1 ~|~FL ~|~03/12/86~|~Sam ~|~3000 ~|~nothin.For example after after 1 st value that is 1 I'm getting trailing spaces as the length of that variable is 12...Is there any way to get following output(No trailing blanks regardles of the var length) 1~|~FL~|~03/12/86~|~Sam~|~3000 ~|~nothing – SAS_learner Jan 31 '13 at 16:44
  • Revise the program in 3 places. First, delete the line that says "else if type=2" and the one immediately after that. Second, change the line that creates the headers to `"'%trim(&&zvnm&i)'" +(-1) "&dlm"`. Last, change the line that prints each row to `&&zvnm&i &&zvft&i +(-1) "&dlm"`. The first change removes the format spec for unformatted variables; the other two changes use a line pointer directive +(-1) to "back-up" one space (removing the extra blank). The original solution was customized for the question. – BellevueBob Jan 31 '13 at 17:28
0

The problem is referenced on 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 (as of some version, anyhow) a new statement, DLMSTR. Unfortunately you can't use DLMSTR in PROC EXPORT, but if you can't easily write the variables out, you can generate the log from a PROC EXPORT and paste it into your program and modify DELIMITER to DLMSTR. You could even dynamically do so - use PROC PRINTTO to generate a file with the log, then read in that file, parse out the line numbers and the non-code, change DELIMITER to DLMSTR, and %include the code.

Joe
  • 62,789
  • 6
  • 49
  • 67
0

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

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

after your 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 ;

It might take tweaking depending on your unix, but this works on AIX. Some versions of sed can use the -i flag to edit in place so you don't have to type out the filename twice.

It is a much simpler and easier single-line solution than a big macro.

o.h
  • 1,202
  • 1
  • 14
  • 24