BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
elwayfan446
Barite | Level 11

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!


Dataset.JPG
1 ACCEPTED SOLUTION

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

View solution in original post

12 REPLIES 12
ballardw
Super User

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
Barite | Level 11

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.


Grid.JPG
Reeza
Super User
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
Barite | Level 11
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
Barite | Level 11

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
Super User
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
Super User
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
Barite | Level 11

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
Super User
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
Barite | Level 11

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
Super User
I wouldn't consider EG a dashboard tool, SAS Visual Analytics would be more in that category. Its good at static reports though...
elwayfan446
Barite | Level 11
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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 2960 views
  • 1 like
  • 3 in conversation