problem export to csv file

[ursula]

HI there,

when I export file to csv format, the output file looks error.

some rows files are missing values for some variables, see below example for detail:

NO.	ID	visit	VALUE1	VALUE2	VALUE3	VALUE4	VALUE5	VALUE6
1	100	1	A	B	C	D	E	F
2	200	1	A	B	C	D	E	F
3	B	C	D	E	F
4	B	C	D	E	F

row 1 and 2 are correct, but row 3 and 4 are wrong.

row 3 and 4 are missing values for variables ID, visit, and value1. so the VALUE2(=B) was filled in ID field, VALUE3(=C) was filled in visit field and so on..

the correct one will be like this:

NO.	ID	visit	VALUE1	VALUE2	VALUE3	VALUE4	VALUE5	VALUE6
1	100	1	A	B	C	D	E	F
2	200	1	A	B	C	D	E	F
3	300	2	A	B	C	D	E	F
4	400	3	A	B	C	D	E	F

any one can help to solve the problem? 

thank you very much in advance.

[Kurt_Bremser]

Inspect the file with a text editor (eg notepad++). If there's further doubt, copy/paste the contents into a window opened with the {i} button.

[Tom]

Please show the actual CSV file (not those tables you show in your post). Perhaps your issue is the tool you are using to LOOK at the CSV file.

A CSV file is a text file. Should look like:

NO.,ID,visit,VALUE1,VALUE2,VALUE3,VALUE4,VALUE5,VALUE6
1,100,1,A,B,C,D,E,F
2,200,1,A,B,C,D,E,F
3,,,B,C,D,E,F 
4,,,B,C,D,E,F

What code did you run to produce the CSV file?

[ursula]

I'm using :

proc export data= test 

dbms=csv

file='test.csv';run;

[Tom]

---

@ursula wrote:

I'm using :

proc export data= test 

dbms=csv

file='test.csv';run;

---

To QC the file add a step to dump the first few lines to the LOG for review.

data _null_;
  infile 'test.csv' obs=10;
  input;
  list;
run;

[ursula]

if I export to excel file, all are going well. The only issue is, the output excel file cannot hold the format that I run in the code.

for example; 1 = yes, 2 = no. 

in the output excel file, it will show as 1, and 2, but I need it shows 'Yes' ans "No".

the reason I'm using output csv is, the output file will show "Yes" and "No", and then I convert it to excel file.

[Tom]

If you are using a reasonable recent release of SAS then use the ODS EXCEL destination and PROC PRINT.

ods excel file='myfile.xlsx';
proc print data=test;
run;
ods excel close;

[Reeza]

Use ODS EXCEL instead then if you want formatted otuput. I suspect some of your fields are text and have either a tab or line return that Excel is interpreting incorrectly and splitting lines instead of maintaining the correct output.

ODS EXCEL file='myfile.xlsx';
proc print data=sashelp.class;
run;
ods excel close;

There are lots of options to customize the output as desired. This assumes you're using SAS 9.4 TS1M3+

[ursula]

I have tried it, but the output did not hold the format. especially for date, it showed numbers instead of mm/dd/yy.

[Tom]

Works fine for me.

data test;
  set sashelp.class;
  date = '01JAN2019'd + int(180*ranuni(0));
  format date mmddyy10.;
run;

ods excel file='c:\downloads\test_date.xlsx';
proc print data=test;
run;
ods excel close;

That is one of the advantages of writing an XLSX file instead of a CSV file. A CSV has no place to store the TYPE of a column.

One thing to remember is NEVER let Excel open your CSV files on its own.  It will change data values.  Always open Excel  and use the menus to import the CSV file so that you get the chance to tell Excel what data type each column contains.

[ursula]

Thank you all for the nice comment and useful information.

I really appreciate for your time.

I have figured it, by cleaning the raw file, every thing is going well!

[Reeza]

You removed the hard returns? Post what the solution was and mark that answer as correct please.

[ursula]

after look into the original file, I realized that there is one "text: field have "space" in the value, for example: 

this original file: (this is not a real data, the actual data have more variables including date field).

ID	visit	value1	Name	value3	value4
100	1	Asia	Lita wong	Honda	credit
200	1	USA	Irene	Tesla	cash

the "Name'"="Lita Wong" have a space in between. so when I export it to csv file, the the 'Name' shift to next row.

I did not realize it shifted to the next row, I thought it was missing ID and visit after export to csv file.

the output of csv looks like this:

ID	visit	value1	Name	value3	value4
100	1	Asia	Lita
wong	Honda	credit
200	1	USA	Irene	Tesla	cash

so after I deleted the space, from Lita Wong to Litawong, the csv file looked like this:

ID	visit	value1	Name	value3	value4
100	1	Asia	Litawong	Honda	credit
200	1	USA	Irene	Tesla	cash

 this is  what i expected, and then I saved it to excel file.

[Tom · Accepted solution]

That is not a space then. 

It is either a CR (ASCII code '0D'x) or a LF (ASCII code '0A'x) or perhaps both.

You can remove them from the data?

name = compress(name,'0D0A'x);

Or replace them with actual spaces.

name = translate(name,'  ','0D0A'x);

[ursula]

I think you are right, it's not just a space from the original file. it must be something in there?

but I like the code you sent.

Thank you.