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

Good day,

I am trying to re-create a chart created with Excel where the value of 3 variables are stacked to show a total $ value for each customer.

See Excel example below.

 

Working in Enterprise Guide 7.12 with SAS 9.4 I haven't found any way to do this. I've tried Proc Chart, SGPLOT, FREQ.

Seems this should be simple to do...any help is appreciated.

Thanks!

 

 

Excel Example

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Jay54
Meteorite | Level 14

If your data is in the Excel multi-column format, transform it into the grouped data like shown below.  Then you can create the required bar chart.  You can some options to make it look closer to what you have shown.  You can add other options to to get data or segment labels.  Fixed code.

 

data excel;
label Materials='Raw Materials';
label Labour='Production Labour';
label Contrib='Gross Contribution Margin';
input Customer $1-12 Materials Labour Contrib;
datalines;
Customer 1 1915740 1234973 2076755
Customer 2 2838960 1103123 1955923
Customer 3 2838960 1103123 1955923
Customer 4 1915740 1234973 2076755
;
run;

 

data stacked;
length Group $25;
keep Customer Group Value;
set excel;
Group='Raw Materials'; Value=Materials; output;
Group='Production Labour'; Value=Labour; output;
Group='Gross Contribution Margin'; Value=Contrib; output;
run;

 

ods listing;
proc sgplot data=stacked noborder;
  styleattrs datacolors=(orange gray yellow) datacontrastcolors=(black);
  vbarparm category=customer response=value / group=group
                   barwidth=0.4;
  yaxis display=(noline noticks) grid;
  xaxis display=(nolabel noticks);
  keylegend / noborder;
run;

 

SGPlot9.png

View solution in original post

3 REPLIES 3
DanH_sas
SAS Super FREQ

You just need to transpose you data so that you have three columns: one for the category, one for the response, and one for the group. The first two customers will look like this:

 

Customer         Value         Breakdown

Customer 1      191574      Raw Materials

Customer 1    1234973      Production Labor

Customer 1    2076775      Gross Contribution Margin (before fixed cost)

Customer 2    4283896      Raw Materials

Customer 2    1103123      Production Labor

Customer 2    1955923      Gross Contribution Margin (before fixed cost)

 

Then, any of the rendering methods you listed should work.

 

Hope this helps!

Dan

Jay54
Meteorite | Level 14

If your data is in the Excel multi-column format, transform it into the grouped data like shown below.  Then you can create the required bar chart.  You can some options to make it look closer to what you have shown.  You can add other options to to get data or segment labels.  Fixed code.

 

data excel;
label Materials='Raw Materials';
label Labour='Production Labour';
label Contrib='Gross Contribution Margin';
input Customer $1-12 Materials Labour Contrib;
datalines;
Customer 1 1915740 1234973 2076755
Customer 2 2838960 1103123 1955923
Customer 3 2838960 1103123 1955923
Customer 4 1915740 1234973 2076755
;
run;

 

data stacked;
length Group $25;
keep Customer Group Value;
set excel;
Group='Raw Materials'; Value=Materials; output;
Group='Production Labour'; Value=Labour; output;
Group='Gross Contribution Margin'; Value=Contrib; output;
run;

 

ods listing;
proc sgplot data=stacked noborder;
  styleattrs datacolors=(orange gray yellow) datacontrastcolors=(black);
  vbarparm category=customer response=value / group=group
                   barwidth=0.4;
  yaxis display=(noline noticks) grid;
  xaxis display=(nolabel noticks);
  keylegend / noborder;
run;

 

SGPlot9.png

BHull
Obsidian | Level 7

Works perfectly...THANKS eveyone!

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 3 replies
  • 7578 views
  • 3 likes
  • 3 in conversation