topic While export the table - Data not perfectly extract in csv file in SAS Programming

While export the table - Data not perfectly extract in csv file

Sathish_jammy — Fri, 03 Jul 2020 17:24:59 GMT

I export the table to csv format but the data is not perfectly fit in csv file.

I have 100 rows in a table. When I transfer that data in csv file it has 108 rows.

proc export data=Proj.GWAS12_Medicalhis_HTN 
outfile="D:\WFH\INSPIRED\Total_Cohort_MedicalHis_HTN.csv" dbms=csv replace;
run;

I attach the error Image. Some remarks column extracted the text to 1st column. Help me to overcome this error.

Thanks in Advance!

Re: While export the table - Data not perfectly extract in csv file

Tom — Fri, 03 Jul 2020 17:39:32 GMT

Why did you post a word document with a picture of a spreadsheet when you asked SAS to generate a text file? Why not just copy the lines from the text file and post them into the body of your question using the pop-up window you get from using the Insert Code button?

Anyway the picture makes it look like some of the character fields in your dataset contain carriage returns and/or line feeds that is causing whatever spreadsheet program you used to open the text to interpret the values as multiple lines. This is probably because SAS does NOT add quotes around values that contain those characters. It only adds the quotes around values that contain the delimiter, comma in your case, or quotes.

Why not just fix the data to not include those characters?

data for_export ;
  set Proj.GWAS12_Medicalhis_HTN ;
  array _c _character_ ;
  do over _c;
     _c=translate(_c,'  ','0A0D'x);
  end;
run;
proc export data=for_export  dbms=csv 
   outfile="D:\WFH\INSPIRED\Total_Cohort_MedicalHis_HTN.csv" replace
;
run;

Re: While export the table - Data not perfectly extract in csv file

Reeza — Fri, 03 Jul 2020 17:38:38 GMT

What makes you think it has 108 rows?
Are you crossing OS, ie from Unix to Windows?
Did you double check that no records have an extra line break in any of your text variables? If you read the data from Excel initially this is sometimes the issue. Find a record where you see the break. Check the record before it for the extra characters or any data issues.