topic Re: problem export to csv file in SAS Programming

problem export to csv file

ursula — Tue, 23 Jul 2019 18:40:40 GMT

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.

Re: problem export to csv file

Kurt_Bremser — Tue, 23 Jul 2019 18:56:12 GMT

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.

Re: problem export to csv file

Tom — Tue, 23 Jul 2019 19:00:16 GMT

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?

Re: problem export to csv file

ursula — Tue, 23 Jul 2019 19:02:48 GMT

I'm using :

proc export data= test

dbms=csv

file='test.csv';run;

Re: problem export to csv file

Tom — Tue, 23 Jul 2019 19:12:14 GMT

@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;

Re: problem export to csv file

ursula — Tue, 23 Jul 2019 19:17:26 GMT

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.

Re: problem export to csv file

Tom — Tue, 23 Jul 2019 19:33:48 GMT

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;

Re: problem export to csv file

Reeza — Tue, 23 Jul 2019 19:37:03 GMT

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+

Re: problem export to csv file

ursula — Tue, 23 Jul 2019 19:41:35 GMT

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

Re: problem export to csv file

Tom — Tue, 23 Jul 2019 19:57:53 GMT

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.

Re: problem export to csv file

ursula — Tue, 23 Jul 2019 21:41:52 GMT

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!

Re: problem export to csv file

Reeza — Wed, 24 Jul 2019 14:56:51 GMT

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

Re: problem export to csv file

ursula — Thu, 25 Jul 2019 16:55:13 GMT

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.

Re: problem export to csv file

Tom — Thu, 25 Jul 2019 16:59:41 GMT

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);

Re: problem export to csv file

ursula — Thu, 25 Jul 2019 17:23:32 GMT

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.