When I export SAS dataset to csv; it strips off all leading spaces in characters
When I export SAS dataset to csv; it strips off all leading spaces in characters. Please help me to keep all leading spaces in the csv output. Operator used:
Proc Export Data = Globl_Mth_Sumry
OutFile = "&GMUPath.\20&RptYr._&RptMt.\03 Output\01 GMU\&Brnd_Abbr.\&Brnd._&Mkt._Globl_Mth_Sumry_&RptMt.&RptYr.&NeuronQTR..csv"
DBMS = CSV Replace;
Run;
So there is a column containing a list of countries that is similar to
Asia India China etc. But the csv file shows it as: - Asia India China.
Please, help.
source to share
I find this an interesting question, in large part because I was sure I knew the answer ... to find out I didn't.
This is technically a solution and, if you are at some point, perhaps enough, although I suspect it is too cumbersome to use in practice. First I generate the data (input using $ CHAR8. To keep leading whitespace), then I output it using fixed column output, not list output.
data test;
input
@1 x $CHAR8.
@9 y $CHAR8.;
format x y $char8.;
datalines;
USA China
Canada N Korea
Russia Mexico
;;;;
run;
data _null_;
file "c:\temp\test.csv" lrecl=80 dropover;
set test;
if _n_ = 1 then do;
put "x,y";
end;
put @1 x $char8. @9 "," @10 y $char8.;
run;
Unfortunately, using DBMS = CSV doesn't seem to allow $ CHAR8. to function as you expected. I don't know why this is so. The solution I was expecting was to write it like this:
data _null_;
file 'c:\temp\test.csv' delimiter=',' DROPOVER lrecl=32767;
if _n_ = 1 then /* write column names or labels */
do;
put
"x"
','
"y"
;
end;
set TEST;
put x $ :char8. y $ :char8.;
run;
which is essentially the code printed in the PROC EXPORT log followed by: $ CHAR8. after each variable. For some reason this (and a bunch of other things like that) didn't work. ODS CSV also doesn't work for keeping leading spaces.
source to share
Like Joe, I was intrigued. Doesn't seem PROC EXPORT
to do what you want. But here's a SAS macro that can do the trick:
%macro mydlm(dsn, outf, dlm, headers);
/*****************************************************************
MYDLM.SAS
SAS Macro to create a delimited file from a SAS data set
Positional Parameters
DSN = SAS Dataset Name
OUTF = Output Text File
DLM = Delimiter to use (CSV, TAB, PIPE, or constant)
HEADERS = Y or N, Include line with variable names at top
Example
%mydlm( sashelp.class , 'c:\temp\tempfile.csv', csv, Y);
****************************************************************/
%if %QUPCASE(&DLM)=CSV %then %let DLM=%str(,);
%else %if %QUPCASE(&DLM)=TAB %then %let DLM='09'x;
%else %if %QUPCASE(&DLM)=PIPE %then %let DLM=%str(|);
proc contents noprint data=&DSN
out=_temp_(keep=name type length varnum label format formatd formatl);
run;
proc sort data=_temp_;
by varnum;
run;
data _null_;
set _temp_ end=eof;
call symput(cats('zvnm',put(_n_,5.)), name);
if format ne ' '
then call symput(cats('zvft',put(_n_,5.))
, cats(format
, put(formatl,best.), '.'
, put(formatd,best.))
);
else if type=2
then call symput(cats('zvft',put(_n_,5.)),cats('$char',put(length,best.),'.'));
else call symput(cats('zvft',put(_n_,5.)),' ');
if eof then call symput('zvcnt',left(put(_n_,8.)));
run;
data _null_;
file &outf;
set &dsn;
%if %upcase(&headers) = Y %then %do;
if _n_ = 1 then put
%do i =1 %to %eval(&zvcnt.-1);
"'%trim(&&zvnm&i)'" "&dlm"
%end;
"'%trim(&&zvnm&zvcnt)'" ;
%end;
put
%do i =1 %to %eval(&zvcnt.-1);
&&zvnm&i &&zvft&i "&dlm"
%end;
&&zvnm&i &&zvft&i;
run;
%mend mydlm;
The macro allows you to select CSV, TAB or PIPE (|) as the separator. Character variables will have leading spaces, and all variable widths will be based on any predefined SAS formats. I tested the macro with this example:
data a;
number2 = 2;
format num comma7.;
format date yymmdd10.;
format char $char40.;
date = today();
num = 1; char = ' This has 10 leading blanks'; output;
num = 2; char = 'This has no leading blanks'; output;
run;
%mydlm( a , 'c:\temp\tempfile.csv', csv, y);
UPDATE: To give credit, the above code was derived from ideas I found on this web page . I would just point you to this link, but it didn't actually do what you want. It's also hard to read.
UPDATE2 . Revised the sample example macro to fix the syntax error and make it more general. This new version allows you to specify any character string as the column separator. I am adding this to support this other Stack Overflow question , which can be answered using the following macro call:
%mydlm( Exp_TXT, '/fbrms01/dev/projects/tadis003/Export_txt_OF_New.txt', ~|~, Y);
source to share