topic Re: Export csv file delimiter based on character or numeric variable in SAS Programming

Export csv file delimiter based on character or numeric variable

sasuser101 — Tue, 11 Apr 2017 18:49:16 GMT

Hello

i have data which is combination of character, numeric and date value. i want to export this dataset in csv format.

i want to enclose all character values in double quotes, numeric and date values just seperated by comma. data will also have null values for all character/numeric/date values. First line of file will have title/heading

Example data has columns

like Name-Character, DOB date format mmddyy10. and Age is number format.

Desired output file

title/heading of file
"Name"           ,      "DOB"        , "Age"
"john, kalan"   ,     1/1/2017   ,     3
"john kalan"    ,                        ,
" "             ,    1/1/2017    ,    3

Re: Export csv file delimiter based on character or numeric variable

Kurt_Bremser — Tue, 11 Apr 2017 19:06:43 GMT

Use dlm=',' dsd in the file statement.

Re: Export csv file delimiter based on character or numeric variable

sasuser101 — Tue, 11 Apr 2017 20:20:07 GMT

Thanks for your reply, i am using this

proc export data=&datast
      outfile="test.csv"
      dbms=dlm
      label
      replace;
      delimiter=',' ;
   run;

above is my code. but only character values with comma etc are double quoted but one with spaces are not.

desired output

"Name"           ,      "DOB"        , "Age"
"john, kalan"   ,     1/1/2017   ,     3
"john kalan"    ,                        ,
" "             ,    1/1/2017    ,    3

Re: Export csv file delimiter based on character or numeric variable

Kurt_Bremser — Wed, 12 Apr 2017 07:00:49 GMT

Use this data step:

data _null_;
file "test.csv" dlm=',' dsd;
set &datast;
if _n_ = 1 then put '"Name","DOB","Age"';
put name ~ dob age;
run;

Re: Export csv file delimiter based on character or numeric variable

Peter_C — Wed, 12 Apr 2017 12:48:19 GMT

For such a specific solution, you might find the EXPORT Procedure fails to meet the need.
A DATA Step provides all the flexibility you seek and with brevity.
____________________________

Proc contents noprint out= _data_ data= your.data ;
Proc sort; by varnum ; run ;

FILENAME fref "the path/filename.csv" lrecl= 10000 ;
Data _null_ ;
   File fref dsd ;
   Set ;
   Put name ~ @@ ;
Run ;
* the tilde~modilier in PUT statements would "quote" all variables output, including numeric 
  values. As this is not wanted an alternate quoting method is used for the output of the data rows ;
Data _null_ ;
   File fref mod dsd ;
   Set your.data ;
   Format _character_ $quote200. ;
   Put ( _all_ )( : ) ;
run ;
* need to release the file so other apps can open it;
FILENAME fref ;

___________________________________
That solution is quite generalised but might fail for character variables that are filled wider than 198.
Also as I defined LRECL= 10000, should column names or data values require a line wider than that these beyond this limit will be lost. The NOTEs in the SASlog will indicate the widest line written providing an easy check.

Re: Export csv file delimiter based on character or numeric variable

Peter_C — Wed, 12 Apr 2017 12:42:58 GMT

Getting a smiley where the code line should be
Put ( _all _ )( : ) ;

Re: Export csv file delimiter based on character or numeric variable

Peter_C — Wed, 12 Apr 2017 12:54:35 GMT

b*mm*r
multiple """

Re: Export csv file delimiter based on character or numeric variable

Peter_C — Wed, 12 Apr 2017 12:59:27 GMT

a suitable workaround might be to replace the DSD in the second FILE statement with DLM= ','
Then you get none of the delimiter protection that DSD provides, which might be OK ;

Re: Export csv file delimiter based on character or numeric variable

Peter_C — Wed, 12 Apr 2017 13:04:05 GMT

here is my final working code

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Proc contents noprint out= _data_ data= your.data ;
Proc sort; by varnum ; run ;

FILENAME fref "%PATH(YOUR)/filename.csv" lrecl= 10000 ;
Data _null_ ;
   File fref dsd ;
   Set ;
   Put name ~ @@ ;
Run ;
* the tilde~modilier in PUT statements would "quote" all variables output, 
  including numeric   values. As this is not wanted an alternate quoting 
  method is used for the output of the data rows ;
Data _null_ ;
   File fref mod dlm=',' ;
   Set your.data ;
   Format _character_ $quote200. ;
   Put ( _all_ )( ~ ) ;
run ;
* need to release the file so other apps can open it;
FILENAME fref ;

Re: Export csv file delimiter based on character or numeric variable

Ksharp — Wed, 12 Apr 2017 13:32:24 GMT

Try ODS CSV .



filename temp temp;
ods _all_ close;
ods csv file=temp options(quote_by_type='yes');
proc print data=sashelp.class noobs;run;
ods csv close;


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

Re: Export csv file delimiter based on character or numeric variable

Kurt_Bremser — Wed, 12 Apr 2017 13:54:18 GMT

Has anyone of you tried my code?

data test;
infile datalines4 dlm=';' dsd truncover;
input name :$20. dob : mmddyy10. age;
format dob mmddyy10.;
datalines4;
Frank Jones;1/1/2017;3
Frank,Jones;1/1/2017;3
;;;;
run;

data _null_;
file '$HOME/test.txt' dlm=',' dsd;
set test;
if _n_ = 1 then put '"Name","DOB","Age"';
put name ~ dob age;
run;

creates this text file:

"Name","DOB","Age"
"Frank Jones",01/01/2017,3
"Frank,Jones",01/01/2017,3

which is the OP's intention, as I guess.

Re: Export csv file delimiter based on character or numeric variable

sasuser101 — Wed, 12 Apr 2017 16:11:57 GMT

Thank you for solution it works as expected.

Only issue i see is

when i do if _n_ = 1 then put statement i have close to 20 columns, so when i export to csv format and if i press enter for names since 20 is lot to read on one line. csv output breaks the line with tab or enter

example

if _n_ = 1 then put '"Col1", "Col2", "Col3", "Col4", "Col5", "Col6", "Col7", "Col8", "Col9", "Col10",

"Col11", "Col12", "Col13", "Col14", "Col15", "Col16", "Col17",

so in csv output there is extra space between Col10 and Col11 and is causing issue in csv file. Is there way to fix it?

example

"Col9","Col10", "Col11","Col12"

Re: Export csv file delimiter based on character or numeric variable

sasuser101 — Wed, 12 Apr 2017 16:27:30 GMT

thanks for solution.

Re: Export csv file delimiter based on character or numeric variable

sasuser101 — Wed, 12 Apr 2017 16:27:42 GMT

thanks for solution

Re: Export csv file delimiter based on character or numeric variable

Kurt_Bremser — Wed, 12 Apr 2017 16:47:36 GMT

Split your put statement:

put '"col1","col2","col3",'@;
put '"col4","col5","col6"';