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.
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);
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.
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?
I'm using :
proc export data= test
dbms=csv
file='test.csv';run;
@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;
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.
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;
I have tried it, but the output did not hold the format. especially for date, it showed numbers instead of mm/dd/yy.
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.
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!
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.
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);
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.