topic Re: loss of formatted values in the exported CSV file in SAS Programming

loss of formatted values in the exported CSV file

Chang — Fri, 10 Jul 2015 15:36:06 GMT

hi,

I would like to export my SAS data set to an external file that is readable to my clients. To do so, the file should use labels as column names and contain the formatted values. With the following program, the first purpose has been achieved. Unfortunately, some values are lost in the exported CSV. In the SAS data set, these values have the format of Percent5.2.

Here is my program:

proc export
    data= data_pro.table01_row1_to_row9
    outfile= "&dir_project.\&output_folder.\NIS_colon_table01_b.csv"
    dbms= CSV
    label
    replace;
    putnames= yes;
run;

I would like to know if labels and formatted values can be also export to other file formats, such as MS Access or Excel. I am not sure why it is currently now allowed to attach such file type as .CSV, .sas7bat or .zip. Please download the SAS dataset and the exported CSV at this link

loss of formatted values in the exported CSV file | Like-Minded Data Analysts

Re: loss of formatted values in the exported CSV file

Reeza — Fri, 10 Jul 2015 15:49:04 GMT

Formatted values are exported in CSV by default.

What version of SAS are you on? What type of format isn't working for you, all or one?

Formats aren't allowed for Excel, they can be attached to sas7bdat but then the system needs a file that also defines the custom format as format definitions are not store in the file.

If you're viewing your CSV file in Excel, Excel may do it's own interpretation so make sure to view the file in a text editor.

Does the following not work for you?

proc format;

value age_cat

0-12 = 'Child'

12-14 = 'Teen'

14-high='Adult';

run;

data class;

set sashelp.class;

format age age_cat.;

label age='Age Category' weight='Weight(lbs)';

run;

proc export data=class outfile="C:\temp\outtest.csv" Label dbms=csv replace; putnames=yes;run;

Re: loss of formatted values in the exported CSV file

ballardw — Fri, 10 Jul 2015 15:56:23 GMT

By lost do you mean they are not in the data or that the appearance is unformatted such as .012 instead of 1.2%?

You could also try ODS CSV with proc print and the NOOBS and LABEL options.

And if you mean readable by people and not needed to be read as data for another program any of the report procedures and RTF or PDF might be acceptable.

Re: loss of formatted values in the exported CSV file

Reeza — Fri, 10 Jul 2015 16:03:34 GMT

Using ODS Tagsets is also a great option that I like, people are familiar with Excel.

Base SAS: Demo: ExcelXP Tagset and Microsoft Excel

Re: loss of formatted values in the exported CSV file

Steelers_In_DC — Fri, 10 Jul 2015 17:36:05 GMT

You aren't losing the format in your csv, if you open the csv in excel or another spreadsheet application that is where you are losing your format. Test exporting then importing the csv, the formats will hold. The problem is with your spreadsheet. Try ods, here is a template that I use:

ods tagsets.ExcelXP path='PATH' file='OUTPUT.xml'

style=printer;

ods tagsets.ExcelXP options(sheet_name='SHEET NAME ONE' AUTOFILTER = 'ALL');

proc print data=FILE1 noobs;

var var1 var2 / style(column)={tagattr='Format:@'}; /* format as character for leading zeros */

var var3 var4 / style={tagattr='Format:#,###'};run;quit; /* format comma */

run;quit;

ods tagsets.ExcelXP options(sheet_name='SHEET NAME TWO' AUTOFILTER = 'ALL');

proc print data=FILE2 noobs;

var _all_;run;quit;

ods tagsets.ExcelXP close;

Re: loss of formatted values in the exported CSV file

Reeza — Mon, 13 Jul 2015 15:58:35 GMT

I think your width is too small in your export. Try something wider than percent5.2, perhaps percent8.2

Re: loss of formatted values in the exported CSV file

Reeza — Mon, 13 Jul 2015 17:09:52 GMT

You're not quite understanding the percent format.

SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition

Your values are over 1, so 3.45, which I'm assuming you intend as 3.45%. SAS would present this as 345.55% which is why it's running out of digits. You can either scale your numbers to less than 100 or you can use a custom format.

Here's a custom format, but it rounds differently that you might expect. I'm sure there's a way to correct that, but I'll leave that to you. The new, scaled variables are at the end of the output.

proc format;

picture mypct low-high='000,009.99%';

run;

data test;

set test.table01_row1_to_row9:;

format _numeric_ best12.;

dead_pct2=dead_pct/100;

female_pct2=female_pct/100;

format dead_pct2 female_pct2 percent8.2;

format dead_pct female_pct mypct.;

run;

proc export

data= test

outfile= "C:\_LocalData\NIS_colon_table01_V2.csv"

dbms= CSV

label

replace;

putnames= yes;

run;

Re: loss of formatted values in the exported CSV file

Chang — Tue, 14 Jul 2015 03:48:28 GMT

hi,

Thanks for your reply. To answer your questions, I am running SAS 9.3 on 64 bits Windows 7. Yes, the format in your program is working well on my side. The age variable is in a format similar to what is specified in the PROC FORMAT. The values are Teen, Child, or Adult, whether the CSV is opened in Excel or Notepad.

Values in the format of comma8., 5.2 and 4.2 are shown correctly in my CSV file. Values in the format of Percent4.2 or Percent5.2, on the other hand, are shown as *% or **%. My formats are specified in the PROC SQL. I have pasted my full program and data from my CSV below

my SAS program:

/*----------------merge row 1 to row 9 by year-------------*/

proc sql;

create table data_pro.table01_row1_to_row9 as

select a.year

,a.num_hosp_wt format= comma8. label='Number of hospitalization'

,b.dead_pct format= Percent5.2 label='died(%)'

,c.num_PR_wt format= comma8. label='Number of procedures'

,d.age_mean_wt format= 5.2 label='Mean age'

,e.female_pct format= Percent5.2 label='Female(%)'

,f.mean_CCXscore_wt format= 4.2 label='mean combined comorbidity score'

,g.Percent as urban_pct format= Percent5.2 label='Urban hospital(%)'

,h.Percent as rural_nonteach_pct format= Percent5.2 label='Rural non-teaching hospital(%)'

,i.Percent as rural_teach_pct format= Percent5.2 label='Rural teaching hospital(%)'

from data_pro.table01_row_01 as a,

data_pro.table01_row_02 as b,

data_pro.table01_row_03 as c,

data_pro.table01_row_04 as d,

data_pro.table01_row_05 as e,

data_pro.table01_row_06 as f,

data_pro.table01_row_07 as g,

data_pro.table01_row_08 as h,

data_pro.table01_row_09 as i

where a.year=b.year=c.year=d.year=e.year=f.year=g.year=h.year=i.year

order by a.year;

quit;

proc export

data= data_pro.table01_row1_to_row9

outfile= "&dir_project.\&output_folder.\NIS_colon_table01.csv"

dbms= CSV

label

replace;

putnames= yes;

run;

CSV opened in Notepad:

"Calendar year","Number of hospitalization","died(%)","Number of procedures","Mean age","Female(%)","mean combined comorbidity score","Urban hospital(%)","Rural non-teaching hospital(%)","Rural teaching hospital(%)"

2004,"94,626",*%,"333,298",69.80,**%,3.66,**%,**%,**%

2005,"93,858",*%,"339,726",69.90,**%,3.61,**%,**%,**%

2006,"58,008",*%,"216,229",70.18,**%,3.73,**%,**%,**%

2007,"91,827",*%,"340,850",69.33,**%,3.59,**%,**%,**%

2008,"85,115",*%,"321,637",69.54,**%,3.62,**%,**%,**%

2009,"59,684",*%,"250,416",69.55,**%,4.01,**%,**%,**%

2010,"55,346",*%,"240,234",69.41,**%,4.08,**%,**%,**%

2011,"55,499",*%,"238,977",69.38,**%,4.15,**%,**%,**%

2012,"51,310",*%,"221,750",69.11,**%,4.17,**%,**%,**%

2013,"645,273",*%,2503116,69.60,**%,3.79,**%,**%,**%

Re: loss of formatted values in the exported CSV file

Reeza — Tue, 14 Jul 2015 03:56:48 GMT

Please look at the examples in the percent documentation in my link above.

Your raw data set shows your numbers as 3.4525 for example. Applying a percent format would make the number 345.25% which requires a width of 8.2, so using a format of percent8.2 would generate the value 345.25%. However, I think that your actual value is 3.45%. Please run my code above on your data and let me know if it works properly.

Re: loss of formatted values in the exported CSV file

Chang — Tue, 14 Jul 2015 04:15:43 GMT

hi Reeza,

I was not aware that the values were larger than 1. I have replaced the format with format=7.2. It is working well. Thanks