chart

[Ronein]

Hello

In this example there are 4 fields:

Month- period of the informaton

ACTUAL- Sum of Loans that were taken in Millions of $ 

OFFERED-Sum of offers of loans that were given to customers in Millions of $

PCT_USED - percet of offers that were used by loans

I want to create a visual way (chart) to show this summary table.

In the x-asix will be month

In Y-axis1 will be amount in Millions of $

In Y-axis2 will be PCT in %

What is the best way to create for each month 2 bars (one for ACTUAL and second for OFFERED) and also create a line chart of PCT_USED?

If there is another good way to represent the information then I will be happy to learn.

I also think of another way to show it:

For each month will have 1 stack bar (with information about ACTUAL and  OFFERED) and also create a line chart of PCT_USED?

Data example_Summary_Info;
Retain Month ACTUAL	OFFERED	PCT_USED;
informat PCT_USED percent9.1;
format  PCT_USED percent9.1;
Input Month $	ACTUAL	OFFERED	PCT_USED;
cards;
10/18 250 485 51.5%
11/18 432 860 50.2%
12/18 497 950 52.3%
01/19 483 898 53.8%
02/19 435 776 56.1%
03/19 436 784 55.6%
04/19 396 684 57.9%
05/19 486 831 58.5%
06/19 438 764 57.3%
07/19 461 821 56.2%
08/19 393 714 55.0%
09/19 377 668 56.4%
10/19 358 638 56.1%
11/19 411 713 57.6%
12/19 420 728 57.7%
;
Run;

[PeterClemmensen · Accepted solution]

This should get you started. Feel free to ask 🙂

Data example_Summary_Info;
Retain Month ACTUAL	OFFERED	PCT_USED;
informat PCT_USED percent9.1;
format  PCT_USED percent9.1;
Input Month $	ACTUAL	OFFERED	PCT_USED;
cards;
10/18 250 485 51.5%
11/18 432 860 50.2%
12/18 497 950 52.3%
01/19 483 898 53.8%
02/19 435 776 56.1%
03/19 436 784 55.6%
04/19 396 684 57.9%
05/19 486 831 58.5%
06/19 438 764 57.3%
07/19 461 821 56.2%
08/19 393 714 55.0%
09/19 377 668 56.4%
10/19 358 638 56.1%
11/19 411 713 57.6%
12/19 420 728 57.7%
;
Run;

data temp;
    set example_Summary_Info;
    p = OFFERED; group = "OFFERED"; output;
    p = actual;  group = "Actual";  output;
run;

proc sgplot data=temp;
    vbarparm category=Month response=p / group=group groupdisplay=cluster baselineattrs=(thickness=0) name='a';
    series x=Month y=PCT_USED / y2axis lineattrs=(color=gold thickness=4) name='b';
    xaxis display=(nolabel);
    keylegend / title="";
run;

[Ronein]

This is the chart that I made but i need to improve it.

I need to add also information about ACTUAL field (one way is to add another bar for each month and second way is to create stack bar with information of ACTUAL and OFFERED fields)

proc sgplot data=example_Summary_Info noborder;
    vbar month / response=OFFERED dataskin=pressed baselineattrs=(thickness=0) fillattrs=graphdata1 
            name='a' nostatlabel;
    vline month / response=PCT_USED y2axis lineattrs=(color=gold thickness=4) nostatlabel name='b';
    xaxis discreteorder=data display=(noline nolabel noticks);
    yaxis display=(noline noticks) grid label='(Millions USD)' values=(200 to 1000 by 50) offsetmin=0;
    y2axis display=(noline noticks) values=(0 to 1 by 0.05) offsetmin=0;
    keylegend 'a' 'b' / title='' linelength=24;
run;

[PeterClemmensen]

Did you try my code?

[Ronein]

It is great, Thank you.

What about display ACTUAL and OFFERED together in one bar (stack bar).

Do you think it is better ?

What is the way to do it?

[PeterClemmensen]

Simply do

proc sgplot data=temp;
    vbarparm category=Month response=p / group=group groupdisplay=stacked baselineattrs=(thickness=0) name='a';
    series x=Month y=PCT_USED / y2axis lineattrs=(color=gold thickness=4) name='b';
    xaxis display=(nolabel);
    keylegend / title="";
run;

Whether it is better or not, it depends on what you want to show. 

[Ronein]

There are some small features that I want to make from your code please.

1-I want to change the label in Y-axis from "P" to "Amnt in millions $" 

2-Below the chart I want to change the label of the yellow line from "PCT_USED" to "%Use"

3-I want to add a title within the chart to "Loans/Offers by Month"

[ed_sas_member]

Hi @Ronein 

Here is an attempt to achieve this:

proc sgplot;
	vbar month / response = ACTUAL legendlabel='Sum of Loans that were taken' ;
	vbar month / response = OFFERED barwidth=0.5 legendlabel='Sum of offers of loans that were given to customers';
	vline month / response = PCT_USED y2axis lineattrs=(color=gold thickness=3) legendlabel='% of offers that were used by loans';
	xaxis display=(noline noticks) label='(Month)' grid ;
	yaxis display=(noline noticks) label='(Millions of $)';
	y2axis display=(noline noticks) label='%' values=(0 to 1 by .05);
run;

[ed_sas_member]

proc sgplot;
	title "Loans/Offers by Month";
	vbar month / response = OFFERED legendlabel='Sum of offers of loans that were given to customers';
	vbar month / response = ACTUAL legendlabel='Sum of loans that were taken' ;
	vline month / response = PCT_USED y2axis lineattrs=(color=gold thickness=3) legendlabel='%Use';
	xaxis display=(noline noticks) label='Month' grid ;
	yaxis display=(noline noticks) label='Amnt in millions $';
	y2axis display=(noline noticks) label='%' values=(0 to 1 by .05);
run;

[Ronein]

Thank you!

I see that  in your output there are 3 bar charts and not 2 ( and 2 of the 3 are same). why?  

[ed_sas_member]

Hi @Ronein 

This is actually what I get -> so 2 bar charts (overlayed) and one line 

[Ronein]

Why are there 2 blue bar charts in same height??