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 :
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.
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.
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;
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.
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"
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.