loss of formatted values in the exported CSV file

Accepted Solution Solved
Reply
Contributor
Posts: 61
Accepted Solution

loss of formatted values in the exported CSV file

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


Accepted Solutions
Solution
‎07-13-2015 11:56 PM
Grand Advisor
Posts: 17,316

Re: loss of formatted values in the exported CSV file

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.

View solution in original post


All Replies
Grand Advisor
Posts: 17,316

Re: loss of formatted values in the exported CSV file

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;

Grand Advisor
Posts: 17,316

Re: loss of formatted values in the exported CSV file

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

Grand Advisor
Posts: 17,316

Re: loss of formatted values in the exported CSV file

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;

Contributor
Posts: 61

Re: loss of formatted values in the exported CSV file

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

Contributor
Posts: 61

Re: loss of formatted values in the exported CSV file

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,**%,**%,**%

Solution
‎07-13-2015 11:56 PM
Grand Advisor
Posts: 17,316

Re: loss of formatted values in the exported CSV file

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.

Grand Advisor
Posts: 10,204

Re: loss of formatted values in the exported CSV file

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.

Grand Advisor
Posts: 17,316

Re: loss of formatted values in the exported CSV file

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

Base SAS: Demo: ExcelXP Tagset and Microsoft Excel

Valued Guide
Posts: 854

Re: loss of formatted values in the exported CSV file

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 1300 views
  • 0 likes
  • 4 in conversation