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

[c8826024]

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.

[art297]

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

[c8826024]

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

[c8826024]

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

[art297]

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;

[ScottBass]

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

[c8826024]

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

Thanks Scott and Art.

[data_null__]

put (_all_) (:);

[Peter_C]

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

[data_null__]

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.

[Peter_C]

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.

[DouglasMartin]

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.

[Doc_Duke]

The %SAS2CSV macro will do what you want.

[MikeZdeb]

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;

[R_Hood]

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...