topic Export ASCII file with double quotes in SAS Programming

Export ASCII file with double quotes

Sha88 — Fri, 23 Apr 2021 11:52:50 GMT

Hello.. I need an ASCII delimited output file from my program, with every variable enclosed in "" double quotes regardless of the contents. Depending on the scenario, some variables can be null and should output as "". I've searched around the forum but most people want to remove the quotes, my customer needs them in permanently:

e.g.

"999999","","10","01NOV2020","BILI"

"999999","","20","05NOV2020","BILI"

"999999","","30","10NOV2020","BILI"

My datasets are built dynamically inside a macro based on some other input handled externally and because of this %ds2csv wouldn't execute (something to do with SCL). Anyway, I finally came up with this, which is working from my macro but is just about 95% of what I need:

%Let outfile = '\\MyServerPath/export.dat' DLM = ',' DSD;

DATA _NULL_;

SET WORK.OUT2_LAB;

FILE &outfile;

put (_ALL_) (~);

RUN;

With this code, null variables contain a space e.g. " " instead of "". Is there anything I can do to adjust the above code just trim empty spaces but keep everything else the same?

So it generates this:

"999999","","10","01NOV2020","BILI"

And not this:

"999999"," ","10","01NOV2020","BILI"

Re: Export ASCII file with double quotes

Tom — Fri, 23 Apr 2021 12:19:42 GMT

You could post process the file.

data have;
  infile cards dsd truncover ;
  input (v1-v5) (:$30.);
cards;
"999999","","10","01NOV2020","BILI"
"999999","","20","05NOV2020","BILI"
"999999","","30","10NOV2020","BILI"
;

filename original temp;
data _null_;
 set have;
 file original dsd ;
 put (v1-v5) (~) ;
run;

data _null_;
  infile original;
  input;
  put _infile_;
run;

filename want temp;
data _null_;
 infile original   ;
 file want ;
 input ;
 _infile_=tranwrd(cats(',',_infile_,','),'," ",',',"",');
 _infile_=substrn(_infile_,2,length(_infile_)-2);
 put _infile_;
run;

data _null_;
  infile want;
  input;
  put _infile_;
run;

"999999","","10","01NOV2020","BILI"
"999999","","20","05NOV2020","BILI"
"999999","","30","10NOV2020","BILI"

Re: Export ASCII file with double quotes

Patrick — Sat, 24 Apr 2021 01:35:06 GMT

And what should happen if there is a double quote in your data? Or is that never going to happen?

Re: Export ASCII file with double quotes

Sha88 — Sat, 24 Apr 2021 07:14:25 GMT

The system generating the data wouldn’t allow it.

Re: Export ASCII file with double quotes

Tom — Sat, 24 Apr 2021 21:55:30 GMT

If you only have character variables you could use a user defined format to add the quotes.

data have;
  infile cards dsd truncover;
  input (v1-v5) (:$30.);
cards;
"999999","","10","01NOV2020","BILI"
"999999","","20","05NOV2020","BILI"
"999999","","30","10NOV2020","BILI"
;

proc format ;
  value $q(max=200) ' '='""' other=[$quote.] ;
run;

data _null_;
  set have ;
  file &outfile dlm=',' ;
  put (_all_) (:$q200.);
run;

Results:

"999999","","10","01NOV2020","BILI"
"999999","","20","05NOV2020","BILI"
"999999","","30","10NOV2020","BILI"

If the data has mixed numeric and character variables or variables that have formats attached then use an intermediate text file. If there are a lot of variables you might also need to set the logical record length longer then the default of 32K bytes.

data have;
  infile cards dsd truncover;
  input v1 v2 (v3-v5) (:$30.);
cards;
"999999","","10","01NOV2020","BILI"
"999999","","20","05NOV2020","BILI"
"999999","","30","10NOV2020","BILI"
;

proc format ;
  value $q(max=200) ' '='""' other=[$quote.] ;
run;

filename step1 temp;
data _null_;
  set have ;
  file step1 dsd lrecl=1000000;
  put (_all_) (+0) ;
run;

data _null_;
  infile step1 dsd length=ll column=cc truncover lrecl=1000000;
  file &outfile dlm=',' lrecl=1000000;
  do until(cc>ll);
    input value :$200. @;
    put value :$q200. @;
  end;
  put;
run;

Re: Export ASCII file with double quotes

Sha88 — Mon, 26 Apr 2021 13:00:36 GMT

This is working, but it’s cutting off the variables at 38 characters. Any idea why might do that? Set LRECL as above.

Re: Export ASCII file with double quotes

Tom — Mon, 26 Apr 2021 13:12:09 GMT

You need to show the code you ran to see what could be truncating values.

Most likely you are using a character variable of length 38 (or possibly using a format with a width of 38). If you do not explicitly define your variables then SAS must guess how to define them based on how you first use them.

Re: Export ASCII file with double quotes

Sha88 — Mon, 26 Apr 2021 16:14:45 GMT

Hi sure, sharing below.

So I tried both approaches (post processing and not) but both truncate a $200. char field down to 38 only during export. If it takeaway the new format, it exports without truncation but obviously is then missing the desired quotes "".

In the WORK.OUT2_LAB dataset all var's are character and lengths all set, they range from $10. to $200. but none have $38. Tempfile is not truncated. Here is what I ran separately:

%LET OutFileQ = &OFP.\&OutFileMask..&OutExtension.;
%LET TempFile = &OFP.\TempLab.txt;

PROC FORMAT;
  VALUE $q(MAX=200) ' '='""' other=[$quote.] ;
RUN;

FILENAME step1 "&TempFile";

DATA _NULL_;
	SET WORK.OUT2_LAB;
	FILE step1 DSD LRECL=10000;
	PUT (_ALL_) (+0);
RUN;

DATA _NULL_;
	INFILE step1 DSD LENGTH=LL COLUMN=CC TRUNCOVER LRECL=10000;
	FILE "&OutFileQ." DLM=',' LRECL=10000;
	DO UNTIL (CC>LL);
		INPUT VALUE :$200. @;
		PUT VALUE :$q200. @;
	END;
	PUT;
RUN;
/*File ^^ max output length 38.. LBTSTCOM truncated in output but not infile.*/

/*Separate just the below with the proc format*/
DATA WORK.TEST1;
	SET WORK.OUT2_LAB;
	FILE "&OutFileQ." DLM=',';
	PUT (_ALL_) (:$q200.);
RUN;
/*File ^^ max output length 38.. LBTSTCOM truncated in export but not OUT2_LAB.*/

Thanks for the assistance.

Re: Export ASCII file with double quotes

Sha88 — Mon, 26 Apr 2021 16:18:32 GMT

Or should I do $quote200. in the PROC FORMAT?

Re: Export ASCII file with double quotes

Tom — Mon, 26 Apr 2021 16:53:45 GMT

Yes. The WIDTH from the $Q format is NOT being passed to the $QUOTE format call. Instead it is defaulting to $QUOTE40. which means there is room for only 38 characters. So include the width in the nested format in the defintion.

PROC FORMAT;
  VALUE $Q(MAX=32767) ' '='""' other=[$quote32767.] ;
RUN;

But you might also be truncating the total line length. Why did you include a LRECL of 10,000? That is SHORTER than the default of 32,767? In my example I use a LRECL or 1,000,000 to show that you could use values larger than the default.

But if your variables have the wrong format then the PUT statement will truncate, no matter what you do if you let SAS use those formats for printing. For example try this program:

1671  data test;
1672    x='This is a long string';
1673    format x $10.;
1674    put x;
1675  run;

This is a

The maximum length for a character variable is 32,767 bytes. So if the total width of the generated lines is less than this then the simple postprocessing using the _INFILE_ variable will work.

%LET OutFileQ = &OFP.\&OutFileMask..&OutExtension.;
%LET TempFile = &OFP.\TempLab.txt;
FILENAME step1 "&TempFile";
FILENAME OUTFILE "&outfileq";

DATA _NULL_;
  SET WORK.OUT2_LAB;
  FILE step1 DSD LRECL=32767;
  PUT (_ALL_) (~);
RUN;
DATA _NULL_;
  INFILE step1 LRECL=32767;
  FILE outfile LRECL=32767;
  INPUT;
  _infile_=tranwrd(cats(',',_infile_,','),'," ",',',"",');
  _infile_=tranwrd(_infile_,',".",',',"",');
  _infile_=substr(_infile_,2,length(_infile_)-2);
  PUT _infile_;
RUN;

If you want to use the other method then use something larger than 200 if some of your variables could require that.

PROC FORMAT;
  VALUE $Q(MAX=32767) ' '='""' other=[$quote32767.] ;
RUN;
DATA _NULL_;
  SET WORK.OUT2_LAB;
  FILE step1 DSD LRECL=2000000;
  PUT (_ALL_) (+0);
RUN;

DATA _NULL_;
  INFILE step1 DSD LENGTH=LL COLUMN=CC TRUNCOVER LRECL=2000000;
  FILE "&OutFileQ." DLM=',' LRECL=2000000;
  DO UNTIL (CC>LL);
    INPUT VALUE :$32765. @;
    PUT VALUE :$Q32767. @;
  END;
  PUT;
RUN;

NOTE: That printing with the $QUOTE format the width specified counts the quotes. So $QUOTE10. will print the first 8 characters of the string.