DATA Step, Macro, Functions and more

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

Reply
Contributor
Posts: 38

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

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.

PROC Star
Posts: 7,471

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

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

Contributor
Posts: 38

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

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

Contributor
Posts: 38

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

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

PROC Star
Posts: 7,471

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

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;

Super Contributor
Posts: 387

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

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

Contributor
Posts: 38

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

Posted in reply to ScottBass

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

Thanks Scott and Art.

Respected Advisor
Posts: 3,799

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

Posted in reply to ScottBass

put (_all_) (:);

Valued Guide
Posts: 2,177

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

Posted in reply to data_null__

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

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

Respected Advisor
Posts: 3,799

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

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.

Valued Guide
Posts: 2,177

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

Posted in reply to data_null__

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.

Contributor
Posts: 35

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

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.

Trusted Advisor
Posts: 2,115

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

The %SAS2CSV macro will do what you want.

Valued Guide
Posts: 765

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

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;



N/A
Posts: 1

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

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

Ask a Question
Discussion stats
  • 16 replies
  • 13998 views
  • 2 likes
  • 11 in conversation