topic Re: Creating csv with space in columns in SAS Enterprise Guide

Creating csv with space in columns

santhosh — Fri, 12 May 2023 08:12:50 GMT

I need to create .csv output should have space in rows
Sample output
No particular amount
1 I BOOKS 5000
2 A PLANE 3000
3 B DOUBLE ROOL 2000
4 II STATENAORY 1000
5 A PEN 500
6 B PENCIL 500

Re: Creating csv with space in columns

MayurJadhav — Fri, 12 May 2023 10:10:22 GMT

You can do this using option:

delimiter=" ";

for example:

data credit;
set sashelp.credit(obs=10);
run;
proc print;

proc export data=credit
    outfile="/home/u61950255/Files/data.csv"
    dbms=csv
    replace;
    delimiter="	";
    putnames=NO;
run;

But beware that it would cause problem when you have space in between the data in the same column. @santhosh

Look at the data from csv file generated by above code with delimiter=" ";

Re: Creating csv with space in columns

Tom — Fri, 12 May 2023 12:32:13 GMT

Your desired output does not look like something usable. You appear to have three variables named No, particular, and amount. But some of your data rows appear to have more than 3 values on them.

Normally in a delimited text file (like a CSV file) you have to quote values that contain the delimiter.

So, depending on which value the space belongs to, your file might look like this:

No particular amount
1 "I BOOKS" 5000
2 "A PLANE" 3000
3 "B DOUBLE ROOL" 2000
4 "II STATENAORY" 1000
5 "A PEN" 500
6 "B PENCIL" 500

SAS can generate that type of file using a simple data step with the DSD option of the FILE statement.

data _null_;
  set have;
  file 'mytext_file.txt' dsd dlm=' ';
  if _n_=1 then put 'No particular amount';
  put no particular amount;
run;

Re: Creating csv with space in columns

santhosh — Sat, 13 May 2023 09:26:42 GMT

Dear Sir,
There is leading space for some rows in second column
2,3 rows there should be a space before letter A and B

No particular amount
1 "I BOOKS" 5000
2 " A PLANE" 3000
3 " B DOUBLE ROOL" 2000
4 "II STATENAORY" 1000
5 " A PEN" 500
6 " B PENCIL" 500

Re: Creating csv with space in columns

Patrick — Sat, 13 May 2023 09:35:14 GMT

Your question is a bit underspecified so my answer based on some assumptions.

data work.have;
  infile datalines dsd dlm=' ';
  input no particular:$40. amount;
  datalines;
1 "I BOOKS" 5000
2 " A PLANE" 3000
3 " B DOUBLE ROOL" 2000
4 "II STATENAORY" 1000
5 " A PEN" 500
6 " B PENCIL" 500
;
proc export 
  data=work.have
  outfile= "c:\temp\want.csv"
  dbms=csv
  ;
quit;

c:\temp\want.csv opened with a text editor

Re: Creating csv with space in columns

Tom — Sat, 13 May 2023 17:30:06 GMT

Leading spaces do not translate well into a delimited file.

To preserve them when reading a file like you could try using the $CHAR informat instead of the normal $ informat.

data have;
  infile cards dsd dlm=' ' truncover;
  input No particular :$char20. amount;
cards;
1 "I BOOKS" 5000
2 " A PLANE" 3000
3 " B DOUBLE ROOL" 2000
4 "II STATENAORY" 1000
5 " A PEN" 500
6 " B PENCIL" 500
;

But to write that back out you will need to NOT use the DSD option on the FILE statement.

Which means you cannot use PROC EXPORT.

So instead just use the $QUOTE. format to add quotes around every string variable, just in case it has any embedded space.

data _null_;
 file 'want.csv';
 set have;
 if _n_=1 then put 'No particular amount';
 put no particular amount;
 format _character_ $quote.;
run;

No particular amount
1 "I BOOKS" 5000
2 " A PLANE" 3000
3 " B DOUBLE ROOL" 2000
4 "II STATENAORY" 1000
5 " A PEN" 500
6 " B PENCIL" 500