Sort sales ascending

[nksever2]

I need to sort the following data numerically by sales, for the months within the year:

PROC IMPORT OUT=salesByProductByDate DATAFILE= "/home/u36030758/Output/bsa570_v4_wk3_assignment_data.xlsx"
DBMS=xlsx REPLACE;
GETNAMES=YES;
RUN;

DATA salesByProductByDate;
SET salesByProductByDate;
year=YEAR(date);
month=month(date);
RUN;

PROC REPORT DATA=salesByProductByDate NOWD;
COLUMN ('Sales' year month) product,(sales);
DEFINE year / GROUP STYLE (header)={background=lightgreen};
DEFINE month / GROUP STYLE (header)={background=lightgreen};
DEFINE product / ACROSS ' ' STYLE(header)={background=lightyellow};
DEFINE sales / SUM STYLE(header)={background=lightyellow} FORMAT=DOLLAR15.2;
BREAK AFTER year /SUMMARIZE DOL DUL;
RBREAK AFTER/SUMMARIZE;
COMPUTE AFTER year;
CALL DEFINE('year','style','style=Header{pretext="SubTotal " tagattr="Type:String"}');
ENDCOMP;
COMPUTE AFTER;
CALL DEFINE('year','style','style=Header{pretext="Grand Total " tagattr="Type:String"}');
ENDCOMP;
RUN;

TITLE '2015 Sales By Product';
PROC SGPLOT DATA=salesByProductByDate(WHERE=(year = 2015));
VBAR product / RESPONSE=sales GROUP=month GROUPDISPLAY=cluster;
XAXIS DISPLAY=(nolabel noticks);
YAXIS GRID;
RUN;

TITLE '2016 Sales By Product';
PROC SGPLOT DATA=salesByProductByDate(WHERE=(year = 2016));
VBAR product / RESPONSE=sales GROUP=month GROUPDISPLAY=cluster;
XAXIS DISPLAY=(nolabel noticks);
YAXIS GRID;
RUN;

TITLE '2017 Sales By Product';
PROC SGPLOT DATA=salesByProductByDate(WHERE=(year = 2017));
VBAR product / RESPONSE=sales GROUP=month GROUPDISPLAY=cluster;
XAXIS DISPLAY=(nolabel noticks);
YAXIS GRID;
RUN;

TITLE '2018 Sales By Product';
PROC SGPLOT DATA=salesByProductByDate(WHERE=(year = 2018));
VBAR product / RESPONSE=sales GROUP=month GROUPDISPLAY=cluster;
XAXIS DISPLAY=(nolabel noticks);
YAXIS GRID;
RUN;

PROC SORT DATA=data;

by sales descending;

RUN;

Does nothing to change the order of the sales.  I am a new user and am having trouble.  Any help is appreciated.

[LNEW]

"descending" should go in before sales in your proc sort statement.

PROC SORT DATA=data;

by descending sales ;

RUN;

[nksever2]

I am still not getting my sales data to sort from lowest to highest, regardless of month.  I have attached a PDF of what I have right now, if that helps.

[Kurt_Bremser]

Please post the log of the proc sort step, using the {i} button.

[LNEW]

I think sorting before using the report would work. Then reference the new report name. Let me know if this works.

data salesbyproductbydate;

set salesbyproductbydate;

year = YEAR(date);

month = Month(date);

run;

proc sort data = salesbyproductbydate out = salesbyproductbydate_1;

by year descending sales;

run;

[nksever2]

I entered that code:

proc sort data=salesbyproductbydate out=salesbyproductbydate_1;
by year descending month;
run;

The data is still sorted by month 1-12, instead of by sales lowest to highest.

I also want to get my 4 supporting charts to be rearranged from lowest to highest sales, from the code.

[LNEW]

try this instead in the proc sort before the proc report statement.

proc sort data =  salesbyproductbydate out = salesbyproductbydate_2;

by year sales;

run;

reference new dataset salesbyproductbydate_2

your month refers to date. It does not refer to low to high amounts this too could be part of the issue.

[LNEW]

by year descending sales;               

[ballardw]

Which output do you want sorted, it is not clear. You have five outputs and not of the code appears to attempt to do anything with the order of sales.

In proc report your are using GROUP based on year and month, so that is the row order and product is the across variable and controls column order. So if something in proc report is supposed to sort on sales your report syntax is incomplete.

In your SGPLOT code you might try GROUPDISPLAY GROUPORDER=DESCENDING

[nksever2]

I am new to this, but entering code to group the sales for each product from highest to lowest, for each month of that year is exactly what I want to do.  

I tried changing my GROUPDISPLAY=descending, instead of GROUPDISPLAY=cluster, and it did not reorder the graph.

Still no luck here.

[ballardw]

---

@nksever2 wrote:

I am new to this, but entering code to group the sales for each product from highest to lowest, for each month of that year is exactly what I want to do.  

I tried changing my GROUPDISPLAY=descending, instead of GROUPDISPLAY=cluster, and it did not reorder the graph.

Still no luck here.

---

Sorry I meant GROUPORDER= descending.

Since I did not have any data to test code on ...

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

[nksever2]

That helped group the graphs.  For the Data table, how do I group the code below by sales, highest to lowest:

PROC REPORT DATA=salesByProductByDate NOWD;
COLUMN ('Sales' year month) product,(sales);
DEFINE year / GROUP STYLE (header)={background=lightgreen};
DEFINE month / GROUP STYLE (header)={background=lightgreen};
DEFINE product / ACROSS ' ' STYLE(header)={background=lightyellow};
DEFINE sales / SUM STYLE(header)={background=lightyellow} FORMAT=DOLLAR15.2;
BREAK AFTER year /SUMMARIZE DOL DUL;
RBREAK AFTER/SUMMARIZE;
COMPUTE AFTER year;
CALL DEFINE('year','style','style=Header{pretext="SubTotal " tagattr="Type:String"}');
ENDCOMP;
COMPUTE AFTER;
CALL DEFINE('year','style','style=Header{pretext="Grand Total " tagattr="Type:String"}');
ENDCOMP;
RUN;

It only goes by month 1-12, the sales are different each month and I want to have the top selling month at the top (for each year) and the lowest selling month at the bottom.  I tried grouping that and the data table disappears when I run it, without errors/warnings.

[JDRII]

Hello, I'm having trouble getting my color graphs to appear in addition to charts.  This is what I have so far

1. PROC IMPORT OUT=salesByProductByDate DATAFILE= "/home/emilymoore20/EPG194/output/myTestFolder/BSA570v4_Week3_assignment_data.xlsx"
2. DBMS=xlsx REPLACE;
3. GETNAMES=YES;
4. RUN;
5.
6. DATA salesByProductByDate;
7. SET salesByProductByDate;
8. year = YEAR(date);
9. month = YEAR(date);
10. RUN;
11.
12. PROC REPORT DATA=salesByProductByDate NOWD;
13. COLUMN ('Sales' year month) product,(sales);
14. DEFINE year / GROUP STYLE (header)={background=lightgreen};
15. DEFINE month / GROUP STYLE (header)={background=lightgreen};
16. DEFINE product / ACROSS ' ' STYLE(header)={background=lightyellow};
17. DEFINE sales / SUM STYLE(header)={background=lightyellow} FORMAT=DOLLAR15.2;
18. BREAK AFTER year /SUMMARIZE DOL DUL;
19. RBREAK AFTER/SUMMARIZE;
20. COMPUTE AFTER year;
21. CALL DEFINE('year','style','style=Header{pretext="SubTotal " tagattr="Type:String"}');
22. ENDCOMP;
23. COMPUTE AFTER;
24. CALL DEFINE('year','style','style=Header{pretext="Grand Total " tagattr="Type:String"}');
25. ENDCOMP;
26. RUN
27.
28. TITLE '2015 Sales By Product';
29. PROC SGPLOT DATA=salesByProductByDate(WHERE=(year = 2015));
30. VBAR product / RESPONSE=sales CATEGORYORDER=respasc GROUP=month 31.GROUPDISPLAY=cluster
32. STAT=sum DATASKIN=gloss;
33. XAXIS DISPLAY=(nolabel noticks);
34.YAXIS GRID;
35. RUN;

etc
36. TITLE '2016 Sales By Product';
37. PROC SGPLOT DATA=salesByProductByDate(WHERE=(year = 2016));
38. VBAR product / RESPONSE=sales GROUP=month GROUPDISPLAY=cluster
39. STAT=sum DATASKIN=gloss;
40. XAXIS DISPLAY=(nolabel noticks);
41. YAXIS GRID;
42. RUN;

[Kurt_Bremser]

Please post your question in a new thread of your own. Otherwise only the participants of this year-old thread can see your question.

[Thomas11]

Hello,

Thank you for your help, you really help me alot. Would you please provide the SAS codes to import the FDA FAERS ASCII 2019 files (all files), if you have them. I appreciate your help.

Sorry i did not know how to start a new topic