multiple bar graph problem with set up

[Gieorgie]

I have data and would like to show in a bar chart. In one position Active_today, Active_prior in two colors and in the other Expired_today and expired prior. To show how values change.

My code to create tabel for graph :

proc sql ;
create table graphbar as
select *
from
(select "ACTIVE_TODAY" as Type, 
sum(ak.ACTIVE_TODAY) as Sum,
"ACTIVE_PRIOR" as Type1, 
sum(ak.ACTIVE_PRIOR) as Sum1
from diff_policy as ak)
union
(select "EXPIRED_TODAY" as Typ2, 
sum(wy.EXPIRED_TODAY) as SUM2,
"EXPIRED_PRIOR" as Type3, 
sum(wy.EXPIRED_PRIOR) as Sum3
from diff_policy as wy)
;
quit;

My data:

Type	Sum	Type1	Sum1
ACTIVE_TODAY	122265	ACTIVE_PRIOR	122641
EXPIRED_TODAY	6790	EXPIRED_PRIOR	1744

When i run below code:

proc sgplot data=graphbar;
vbarparm category=Type response=Sum / discreteoffset=-0.17 barwidth=0.3;
vbarparm category=Type1 response=Sum1 / discreteoffset=0.17 barwidth=0.3;
vbarparm category=Type2 response=Sum2 / discreteoffset=0.17 barwidth=0.3;
vbarparm category=Type3 response=Sum3 / discreteoffset=0.17 barwidth=0.3;

run;

log shows that Type/Sum variable not found. How to change data and proc sgplot to get some like a below :

[ballardw]

Please post the LOG of both the Proc SQL and the Proc SGplot code. First, you say you are getting an error but you have 8 possible variables to have problems with and don't specify which one. Second the SQL code you show creates at least one variable, TYP2, that is not used in the Sgplot where you use Type2.

So you may have more than one typo involved and can't guess exactly which error might be involved.

Additionally , your posted "data" shows Expired_today as a value for Type, not Typ2 (or Type2). So your SQL seems not to have created the data you think it did.

[Gieorgie]

Hello Thanks for your answear . Sorry for the typo

This is log from sgplot :

---

28 proc sgplot data=graphbar;
29 vbarparm category=Type response=Sum / discreteoffset=-0.17 barwidth=0.3;
30 vbarparm category=Type1 response=Sum1 / discreteoffset=0.17 barwidth=0.3;
31 vbarparm category=Type2 response=Sum2 / discreteoffset=0.17 barwidth=0.3;
ERROR: Variable TYPE2 not found.
ERROR: Variable SUM2 not found.
32 vbarparm category=Type3 response=Sum3 / discreteoffset=0.17 barwidth=0.3;
ERROR: Variable TYPE3 not found.
ERROR: Variable SUM3 not found.
33
34 run;

This is for my proc sql :

28 proc sql ;
29 create table graphbar as
30 select *
31 from
32 (select "ACTIVE_TODAY" as Type,
33 sum(ak.ACTIVE_TODAY) as Sum,
34 "ACTIVE_PRIOR" as Type1,
35 sum(ak.ACTIVE_PRIOR) as Sum1
36 from diff_policy as ak)
37 union
38 (select "EXPIRED_TODAY" as Type2,
39 sum(wy.EXPIRED_TODAY) as SUM2,
40 "EXPIRED_PRIOR" as Type3,
41 sum(wy.EXPIRED_PRIOR) as Sum3
42 from diff_policy as wy)
43 ;
NOTE: Table WORK.GRAPHBAR created, with 2 rows and 4 columns.

44 quit;

[ballardw · Accepted solution]

Note your log where it says:

NOTE: Table WORK.GRAPHBAR created, with 2 rows and 4 columns.

4 columns = 4 variables. Your SGPLOT code lists 8 variables.

You need to check the results of your SQL, it does not have the variables you think it does because it has "type2" as type.

I think that your SQL needs OUTER Union CORR

proc sql ;
create table graphbar as
select *
from
(select "ACTIVE_TODAY" as Type,
sum(ak.ACTIVE_TODAY) as Sum,
"ACTIVE_PRIOR" as Type1,
sum(ak.ACTIVE_PRIOR) as Sum1
from diff_policy as ak)
outer union corr
(select "EXPIRED_TODAY" as Type2,
sum(wy.EXPIRED_TODAY) as SUM2,
"EXPIRED_PRIOR" as Type3,
sum(wy.EXPIRED_PRIOR) as Sum3
from diff_policy as wy)
;

UNION default behavior is to have the values of the first, second etc column in each of the query bits stack. CORR aligns like name variables, the OUTER means that you have variables that don't align, otherwise Corr would only keep the matched names, which your code doesn't have at all.

[Gieorgie]

yeah , its look much better, thanks ! its look now like this.

But maybe is there a chance that he can combine 4 bars into two bars so that Active and prior will be one bar showing the differences

For example like a below : Active and Prior might be look like a "2006"