BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasuser101
Obsidian | Level 7

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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

14 REPLIES 14
sasuser101
Obsidian | Level 7

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

 

Peter_C
Rhodochrosite | Level 12

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.

Peter_C
Rhodochrosite | Level 12
b*mm*r
multiple """
Peter_C
Rhodochrosite | Level 12
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 ;
Peter_C
Rhodochrosite | Level 12

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 ;
Peter_C
Rhodochrosite | Level 12
Getting a smiley where the code line should be
Put ( _all _ )( : ) ;
Ksharp
Super User
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;



Kurt_Bremser
Super User

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.

sasuser101
Obsidian | Level 7

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"

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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