Use EG to build a report from data set with summarized data

[elwayfan446]

Hello,

I have build a project in SAS that processes observation level data with the query task and summarizes based on criteria I have provided.  The resulting data set has this summarized data in several columns.  I want to put this data "as is" in this new data set into a report but I want each column to be verical on the report with the values out beside them.

For example:

                                DLY          MTD       YTD

Count

Amount

Percent

Currently, I have a summarized data set for the DLY, MTD, YTD info.  I want to be able to combine those data sets for a report in the format above instead of each number being its own variable.  I have attached a screenshot of how the data looks in the summarized data set.

All of the reporting features in EG seem to want to summarize the data.  I have tried transpose as well and have had issues.  When I do, the variable formats are lost.  I found a workaround to add a new text field but that turns everything into a char string and I don't like that either.

Any help or suggestions would be greatly appreciated!

[ballardw]

If you have multiple data sets with different structures or contents you will likely need to pick a structure, modify all of the data to match and append resulting data. Then do the report.

I can't tell exactly what you mean by "I want to put this data "as is" in this new data set into a report ".

You should provide an example of your desired output to match the input as the variable names aren't intuitively obvious as to whcih are amounts or percents or in DLY MTD YTD.

What report procedure are you using to finalize the report?

[elwayfan446]

Thanks for the reply, ballard. The 3 data sets I have all have the same variables which have the same formats. Their is a DLY, WTD, MTD, and YTD data set with those same variables.  You can see an example of how those data sets look with the attachment I posted.

I simply want to take the data as it appears in those data sets and add it to a report without any additional summarizing.  The result would look like a transposed table on the report.  If I could simply build the report free-form by dragging each label and field where I wanted it that would be ideal, however, it doesn't seem as if I can do that in SAS.  I have attached the way it should look on the report.

I have tried using the Summary Tables task, List Data task, List Tables task in EG.  None of those seem to let me do what I am trying to do.

[Reeza]

You need a transpose task, merge each of the 3 files together and a List Data Task. You most likely will have to convert your data to text/characters because the fields all have different types, i.e. numeric, dollar, percent and even a custom format and there currently isn't a way to store different types of information in the same column.

[elwayfan446]

Thanks Reeza. I did all of this but instead of merging I joined the data sets. I wasn't sure how I could get the numbers (text) into the DLY, WTD, MTD, and YTD columns by merging.

When I converted to text it seemed to center the values in the columns.

[elwayfan446]

Another option I was thinking about was going back to the record level data sets after they have been broken out by criteria and building the reports off of those. Then I could summarize everything when building the report.  I will have to play with it and see if I can get the results to look like I need to according to the screenshot.  Maybe you guys can tell me if this is a better approach.

It just seems like this should be a relatively simple thing for a SAS report to do.

[Reeza]

I find SAS weak in it's handling of date calculations for reports, i.e. YTD, WTD and Y/Y calculations. My 2 cents though.

[Reeza]

Use left align in your PUT statement, its the third optional parameter.

PUT (variable, format -l)

See note under format in the documentation:
http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000199354.htm

[elwayfan446]

Off the top of your head... can this be done through the EG task?  I can play with it tomorrow but thought you may know.  So far I have done everything through EG without writing any SAS code myself.  Trying to avoid that if possible.

[Reeza]

I don't see why it can't, of course it depends on what your original data looks like but if you got to the summary section initially then it should be possible.

[elwayfan446]

Thanks Reeza, I will see what I can do tomorrow.

I am hoping that EG is going to be able to create nice dashboard reports like I assumed it would.  I am starting to feel like there are some limits to it.  I am new to it, however.

[Reeza · Accepted solution]

I wouldn't consider EG a dashboard tool, SAS Visual Analytics would be more in that category. Its good at static reports though...

[elwayfan446]

I think I am going to go the route of bringing in everything at the record level and summarize using the summary tables task. This seems to be working easier than working with the static data. Thanks for your help!