Desktop productivity for business analysts and programmers

Problem When doing "List Report" with duplication values in every month

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 76
Accepted Solution

Problem When doing "List Report" with duplication values in every month

I have a set of data that has no duplication and with the only info that i need for reporting.

Im doing this by using SAS EG GUI drag and drop, not by coding.

 

Here is my problem.

I can get the List Report and displaying properly if i view it as Full report with every records in it with a SUM at the end counting my Car price..

If i set it to group(create a seperate table for each value of) with Month, i should be getting the results of every month's sales record with a total amount for each month. But, Im only getting the Final total at the most bottom of the report. I tried tweaking the settings in Page Break, tried putting the Total by Grand Total, it will duplicates all my records for each and every field.

 

Let me show u my data and step by step

Clean data.jpg

As you can see, these are my data. I want to show the Car record by ColumnX_CarMANUMONTH with the SUM of Carprice in every month.

 

I then make a full report with the SUM of carprice(for some reason, the Same carmake will not appear and appear as blank, need ur advice):

Report.jpg

 

 

But when i try to set it to separate table based on MONTH:

reporting problem 3.png

 

 

and with Page break of grand Total:

 

reporting problem 5.png

 

 

 

 

I then only get this result:

reporting problem 6.png

 

 

As you can see, there is only a CARPRICE SUM at the most bottom of the report. What i want is to have a SUM for every month.

 

 

 

 

 

I then modify the task Page Break into this:

reporting problem 4.png

 

 

And then im getting this:

reporting problem 1.png

 

 

 

 

 

 

I spent a long time trying all the other settings and still no luck. I then google and found some basic code that can do Reporting.

Here are my code:

PROC SORT DATA=WORK.QUERY_FOR_TRNSTRANSPOSEDAPPEND_T OUT=WORK.YANDHIMONTH;

BY COLUMNX_CARMANUMONTH;

 

PROC PRINT DATA=WORK.YANDHIMONTH;

VAR ColumnX_CARMANUMONTH ColumnX_CARMAKE ColumnX_CARMODEL ColumnX_CARTYPE ColumnX_CARYEAR;

SUM CARPRICE;

BY ColumnX_CARMANUMONTH;

title 'YANDHI IS KING';

RUN;

 

 

and guess what, i got what i want (but i am still thinking on how to insert title and let all the table have the same alignment)

reporting problem 7.png

 

As you all can see, there is a SUM in every month. This is my expectation by using drag and drop in SAS EG but so far no luck on my side.

 

 

I need your kind advice to help me on this.

 

 

Best Regards, 

Dickson

 


Clean data.jpg

Accepted Solutions
Solution
‎01-20-2016 03:08 AM
Contributor
Posts: 20

Re: Problem When doing "List Report" with duplication values in every month

Click on the dataset in SAS EG.

 

Go to Task List, click "List Data"

 image1.PNG

 

In Data tab, drag and drop the variables to "List variables" panel in the right.

drag n drop your month column in "group analysis by", carprice column in "total of"

image2.PNG

Go to Options tabs, uncheck the print the row number.

 

Go to Title tab, uncheck the Use default text and tyoe your custom title.

 

click run.

image3.PNG

This is the simple list report. Hope might help you.

View solution in original post


All Replies
Solution
‎01-20-2016 03:08 AM
Contributor
Posts: 20

Re: Problem When doing "List Report" with duplication values in every month

Click on the dataset in SAS EG.

 

Go to Task List, click "List Data"

 image1.PNG

 

In Data tab, drag and drop the variables to "List variables" panel in the right.

drag n drop your month column in "group analysis by", carprice column in "total of"

image2.PNG

Go to Options tabs, uncheck the print the row number.

 

Go to Title tab, uncheck the Use default text and tyoe your custom title.

 

click run.

image3.PNG

This is the simple list report. Hope might help you.

Frequent Contributor
Posts: 76

Re: Problem When doing "List Report" with duplication values in every month

Thx man, u helped me. This is exactly what i want. Meaning i shouldn't use "List Report".

Thx again.

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 323 views
  • 1 like
  • 2 in conversation