topic Re: How to Write a SAS Dataset to a Pipe Delimited Text File (Not Using PROC EXPORT) in SAS Programming

How to Write a SAS Dataset to a Pipe Delimited Text File (Not Using PROC EXPORT)

c8826024 — Mon, 23 Jan 2012 22:12:52 GMT

Somebody might answer this question. But I can't find it. Somehow, the PROC EXPORT, for some reasons, is just not working in my company's server. Are there any neat solution?

Thanks.

How to Write a SAS Dataset to a Pipe Delimited Text File (Not Using PROC EXPORT)

art297 — Mon, 23 Jan 2012 22:15:24 GMT

With or without column headers? And have you tried just selecting Export from the file menu?

How to Write a SAS Dataset to a Pipe Delimited Text File (Not Using PROC EXPORT)

c8826024 — Mon, 23 Jan 2012 22:25:33 GMT

Sorry, I forgot one requirement: the first row need to have the variable name.

How to Write a SAS Dataset to a Pipe Delimited Text File (Not Using PROC EXPORT)

c8826024 — Mon, 23 Jan 2012 22:27:37 GMT

I try to productionize my program for a batch production. So selecting Export from the file menu is out of my option.

How to Write a SAS Dataset to a Pipe Delimited Text File (Not Using PROC EXPORT)

art297 — Mon, 23 Jan 2012 22:50:28 GMT

I don't know if this qualifies as "neat", but how about:

proc sql noprint;

select '"'||trim(name)||'"',

case

when type="char" then 'put '||trim(name)||' $ '

else 'put '||trim(name)

end

into :vars

separated by "'|'",

:data

separated by ' @ ;'

from dictionary.columns

where libname="SASHELP" and

memname="CLASS"

;

quit;

data _null_;

file 'C:\want.txt' delimiter='|' DSD DROPOVER lrecl=32767;

if _n_ = 1 then do;

put &vars.;

end;

set SASHELP.CLASS;

&data.;

run;

How to Write a SAS Dataset to a Pipe Delimited Text File (Not Using PROC EXPORT)

ScottBass — Mon, 23 Jan 2012 23:06:27 GMT

Hi,

Well at first I had this:

proc contents data=sashelp.class out=names noprint;

run;

proc sql noprint;

select name into :vars separated by "|" from names order by varnum;

quit;

filename temp temp;

data _null_;

file temp;

put "&vars";

do until (eof);

set sashelp.class end=eof;

put (_all_) ("|");

end;

stop;

run;

dm "fslist temp";

But unfortunately there is a leading "|" in the data portion.

However, seeing Art's using of delimiter='|' on the file statement, I tried:

data _null_;

file temp delimiter="|";

put "&vars";

do until (eof);

set sashelp.class end=eof;

put (_all_) ($);

end;

stop;

run;

dm "fslist temp";

I only got to the variable list modifier "($)" by reviewing error messages in the SAS log :-), but it seems to work ok.

Make sure you don't define any additional working variables in the data step or the "put _all_" will not be what you want.

HTH...

Scott

How to Write a SAS Dataset to a Pipe Delimited Text File (Not Using PROC EXPORT)

Doc_Duke — Mon, 23 Jan 2012 23:26:07 GMT

The %SAS2CSV macro will do what you want.

How to Write a SAS Dataset to a Pipe Delimited Text File (Not Using PROC EXPORT)

c8826024 — Tue, 24 Jan 2012 14:53:01 GMT

I add DSD and LRECL option. The error message is gone.

Thanks Scott and Art.

How to Write a SAS Dataset to a Pipe Delimited Text File (Not Using PROC EXPORT)

data_null__ — Tue, 24 Jan 2012 15:33:34 GMT

put (_all_) (:);

Re: How to Write a SAS Dataset to a Pipe Delimited Text File (Not Using PROC EXPORT)

MikeZdeb — Tue, 24 Jan 2012 18:13:56 GMT

hi ... another idea ..

#1 go to ... http://support.sas.com/rnd/base/ods/odsmarkup/ ... and download new CSV tagset definition

#2 submit the SAS code downloaded in #1

#3 try ...

ods results off;

ods listing close;

ods csv file='z:\class.txt' options(delimiter='|');

proc print data=sashelp.class noobs;

run;

ods csv close;

ods listing;

ods results;

#4 portion of output ...

"Name"|"Sex"|"Age"|"Height"|"Weight"

"Joyce"|"F"|11|51.3|50.5

"Jane"|"F"|12|59.8|84.5

"Louise"|"F"|12|56.3|77.0

"Alice"|"F"|13|56.5|84.0

"Barbara"|"F"|13|65.3|98.0

"Carol"|"F"|14|62.8|102.5

"Judy"|"F"|14|64.3|90.0

"Janet"|"F"|15|62.5|112.5

#5 LRECL doesn't seem to matter ... record length of first row in the text file (with variable names) is 6892 ...

data test;

retain x1-x1000 0;

run;

ods results off;

ods listing close;

ods csv file='z:\xyz.txt' options(delimiter='|');

proc print data=test noobs;

run;

ods csv close;

ods listing;

ods results;

Re: How to Write a SAS Dataset to a Pipe Delimited Text File (Not Using PROC EXPORT)

R_Hood — Tue, 24 Jan 2012 18:55:40 GMT

I have some ugly mainframe code that worked a long time ago. It uses macro processing and has 8 char cryptic names and is heavily commented. Reply if you think it will be of use or nothing else worked...

How to Write a SAS Dataset to a Pipe Delimited Text File (Not Using PROC EXPORT)

DouglasMartin — Wed, 25 Jan 2012 16:55:19 GMT

Just an observation - If you select Export from the file menu, one of the options is to have SAS write out the SAS code that it generated. You can then append it back into your program and continue writing your program from there. I usually do that when I need code in a SAS program to import or output; it means I at least have a starting point for getting the syntax right.

Re: How to Write a SAS Dataset to a Pipe Delimited Text File (Not Using PROC EXPORT)

Peter_C — Thu, 26 Jan 2012 14:46:11 GMT

disappointed not to see a link to your demo of CALL VNEXT() for this application.

http://communities.sas.com/message/31140#31140

using no macro variables to hold the headers removes the risk of overflowing any limit to the length of the macro variable holding all headers. For a wide table with many columns the limit (32K or 64K) is not so very large.

That is teh feature I most appreciate in thast VNEXT method

Message was edited by: Peter Crawford to add one link

Re: How to Write a SAS Dataset to a Pipe Delimited Text File (Not Using PROC EXPORT)

data_null__ — Thu, 26 Jan 2012 15:37:56 GMT

I had thought about that a bit but as it doesn't use macro variables I didn't think it would get much traction. :smileymischief:

I update my reply in the thread you referenced because as you know all the formatting that was so carefully done in the old forum using "pre" was totally mangled by the new and improved forum.

Re: How to Write a SAS Dataset to a Pipe Delimited Text File (Not Using PROC EXPORT)

Peter_C — Thu, 26 Jan 2012 16:13:04 GMT

It's worth bookmarking that posting, so thank you for updating it. (having had to do something similar for one of my own I appreciate your effort)

I liked the technique because it does not use macro variables ...

Macro variables are just fine until they bump into their own limits.

Re: How to Write a SAS Dataset to a Pipe Delimited Text File (Not Using PROC EXPORT)

Stephen — Wed, 25 Jun 2014 18:57:21 GMT

Even though this is an old post, it's labeled as not being answered. I thought I'd reply because it came up on an internet search.

data _null_ ;
     set sds.filename ;
     FILE 'u:\rawfile.txt' DLM='|' ;
     PUT var5 var2 var3 var1 var4 ;
run ;

Re: How to Write a SAS Dataset to a Pipe Delimited Text File (Not Using PROC EXPORT)

sas_9 — Fri, 11 Jul 2014 21:16:52 GMT

try this....

filename test "c:\test_file.txt";

data _null_;

set your_sas_dataset;

file test dlm="|";

put var1 var2 var3 var10;

run;