what is difference between formatted data and detailed data when exporting data from a SAS VA report

[pgadde]

Hi, I created a report using SAS VA 7.4 version. When i export the data into excel there are 2 options formatted data and detailed data.

The formatted data is not exporting all the records which i was expecting.  Detailed data is including all the rows but adding columns which are not selected for the report.

Ideally i wanted only selected columns with all rows when exporting data. Does anyone know why records are removed from formatted data export.

[SAS_Cares]

You can find more explanations on this under documentation, see http://support.sas.com/documentation/cdl/en/vaug/67270/HTML/default/viewer.htm#p0e7gix09rfudzn1w55fg...

[pgadde]

Thanks for getting back to me. In this document listed it just says the below.

(Optional) To choose whether the exported data is formatted, either select or clear the Formatted data check box. This check box is selected by default.

It is not giving the actual definition. 

The reason i asked this question to begin with is, I created a new VA dashboard and when i try to export the data from tab list not all the rows are exported. I should got around 13,886 rows i got only 8333 records when exporting in formatted way. When i did in detail way i am getting all the records but it is adding all the columns which are not selected in the report too. 

Ideally i want formatted way to export all the records in data .

[SAS_Cares]

Thanks for your follow up question! We are researching your question further and will be in touch soon. 

[SteveMellgren · Accepted solution]

At a high level, there are a few differences between exporting formatted data and exporting detailed data:

* Exporting formatted data exports only the summarized data used in an object.  You can only export the columns of data used in the object.

* Exporting detailed data exports all data supporting an object.  You are able to export ALL columns of data in the supporting table (default option), but you have the option to choose specific columns to export.

When you export data from a list table or cross tab, you can either select or de-select the formatted data option.  By default formatted data is selected.  If you export data from a graph you get the additional option of selecting detail data.  These options are independent, and selecting one does not impact how the other works.

Formatted Data

Selecting formatted data applies the VA data format to your export.  De-selecting formatted data exports the same data, but without formatting.  You will end up with exactly the same number of records, with the only difference how the data is formatted. 

The values exported will be the values used in the VA object.  So if values are summarized by product in the object, then the values exported will be summarized by product as well.  Choosing or not choosing to format does not change the values exported, only how they are formatted.

You have the option to select all rows or selected rows as well as all columns or selected columns.  All rows and all columns is the default.  If you choose to select columns, you can ONLY choose from the columns that support your object.  You do not have the option to include columns of data not part of your object

Detailed Data

Selecting detailed data from a graph will export ALL the data that supports the object.  So if your graph summarizes by product, selecting detail data will show every record that supports that summarization.  

You have the option to select all rows or selected rows as well as all columns or selected columns.  All rows and all columns is the default.  However, when exporting detailed data, ALL columns in the entire table are included in the default selection.  This includes those columns not used in the object.  If you select detailed data, you may need to select only specific columns to limit how many columns of data are exported.

[pgadde]

Thanks for the detailed response. Is there a limit in the number of rows that can be exported out of SAS VA report. I am trying to export data in detailed report and it is only exporting 50,000 rows. The data actually has about 255K records.

[SteveMellgren]

There is a limit to the number of rows you can download at one time. I have included a couple resources that may point you in the right direction.

1) Here is a SAS Communities thread asking a question about the 50,000 row limit which may help - https://communities.sas.com/t5/SAS-Visual-Analytics/SAS-VA-7-4-modern-viewer-limits-on-rows-returned.... 

2) There is a hot fix available that addresses the issue - http://support.sas.com/kb/61/087.html

[KeithM]

Sometimes it is easier to see the solution with a few screen captures.  Here is my analysis that corresponds to the accepted solution.

This is easily illustrated with a few screen captures.  I created a simple cross tab from cars table.  I formatted frequency to be currency.  I also created a custom category called Custom Category 1 based on Origin.

Default option window show when exporting data from a list table:

Formatting:  if this is selected then any formats that you added WITHIN VA will be exported.

DETAIL DATA does not aggregate and does not show calculated columns that were created in VA.  So in the example above, CUSTOM CATEGORY 1 does not exist in detail data.  Here are the results formatted and unformatted.

[pgadde]

Thanks for the reply. I think we need to apply the hotfix.