Export csv file delimiter based on character or numeric variable

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

Export csv file delimiter based on character or numeric variable

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

 

 

 


Accepted Solutions
Solution
‎04-12-2017 11:55 AM
Esteemed Advisor
Posts: 6,648

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

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Esteemed Advisor
Posts: 6,648

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 28

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

[ Edited ]

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

 

Esteemed Advisor
Posts: 6,648

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

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 28

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

thanks for solution.
Valued Guide
Posts: 2,174

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

[ Edited ]

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.

Valued Guide
Posts: 2,174

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

b*mm*r
multiple """
Valued Guide
Posts: 2,174

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

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 ;
Valued Guide
Posts: 2,174

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

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 ;
Contributor
Posts: 28

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

thanks for solution
Valued Guide
Posts: 2,174

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

Getting a smiley where the code line should be
Put ( _all _ )( : ) ;
Grand Advisor
Posts: 9,576

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

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;



Solution
‎04-12-2017 11:55 AM
Esteemed Advisor
Posts: 6,648

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

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 28

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

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"

Esteemed Advisor
Posts: 6,648

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

Split your put statement:

put '"col1","col2","col3",'@;
put '"col4","col5","col6"';
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 14 replies
  • 170 views
  • 0 likes
  • 4 in conversation