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

Hi all,

I used a sgplot in SAS 9.3 EG to plot the relationship between two types of account balances. The x-axis shows dates in character format, such as YYYYMM, from 2007 to 2013. Please see the image below.

Balance_axis.png

The dates on the x-axis is very messy looking, so I was hoping to do either one of the following to clean this up.

1. Provide x-axis in greater margins, maybe only show a few period dates.

2. Print smaller fonts, hopefully these dates will fit onto the axis.

The code I have is attached as follows:

proc sgplot data=Exposure_Amt uniform=scale;

  series x=PROC_YYMM_DT y=LMA_Balance / markers markerattrs= (symbol=trianglefilled);

  series x=PROC_YYMM_DT y=MLIB_Balance / markers markerattrs= (symbol=starfilled);

  xaxis label = 'Cohort Date' values=(200701 to 201312 by 10000);

  yaxis label = 'Exposure in ($) Billions';

  title 'Total Outstanding Balance';

run;

where Exposure_Amt was imported from an Excel worksheet in this format:

PROC_YYMM_DTLMA_BalanceMLIB_Balance
2007018.475.66
2007028.405.96
2007038.495.96
2007048.686.05
2007058.666.05
2007068.676.24
2007078.716.15
2007088.856.27
2007098.956.29
2007109.116.34
2007119.336.28
2007129.416.34
2008019.176.27
2008029.306.38
2008039.586.46
20080410.026.57
20080510.256.72
20080610.197.00
20080710.046.90
20080810.126.66
20080910.166.36
2008108.725.19
2008118.394.84
2008128.314.75

I read online you could use the values=( by ) statement under xaxis, but only for numerical values. Any input on this will be greatly appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Jay54
Meteorite | Level 14

SGPANEL is available in SAS 9.3.

I am not sure what you want, but one way is what I have shown below.  I made your date character, extracted year and month, and used SGPANEL to make this graph using SAS 9.3.  This cleans up the axis.  You can format the axis values 1-12 to Jan, Feb, etc. if you want.  You can also make your date as numeric with a date format.

You can place the cells side by side (as below), or stacked.

Exposure.png

data exposure;

  input date $ LMA_Balance MLIB_Balance;

  year=substr(date, 1, 4);

  month=substr(date, 5, 2);

  datalines;

200701 8.47 5.66

200702 8.40 5.96

200703 8.49 5.96

200704 8.68 6.05

200705 8.66 6.05

200706 8.67 6.24

200707 8.71 6.15

200708 8.85 6.27

200709 8.95 6.29

200710 9.11 6.34

200711 9.33 6.28

200712 9.41 6.34

200801 9.17 6.27

200802 9.30 6.38

200803 9.58 6.46

200804 10.02 6.57

200805 10.25 6.72

200806 10.19 7.00

200807 10.04 6.90

200808 10.12 6.66

200809 10.16 6.36

200810 8.72 5.19

200811 8.39 4.84

200812 8.31 4.75

;

run;

ods html close;

ods listing;

ods graphics / reset width=8in height=4in imagename='Exposure';

proc sgpanel data=exposure;

  panelby year / layout=panel columns=2 onepanel uniscale=row novarname;

  series x=month y=LMA_Balance / markers markerattrs= (symbol=trianglefilled);

  series x=month y=MLIB_Balance / markers markerattrs= (symbol=starfilled);

  colaxis label = 'Cohort Date' values=(200701 to 201312 by 10000);

  rowaxis label = 'Exposure in ($) Billions' values=(5 to 12 by 1) grid;

  title 'Total Outstanding Balance';

run;

View solution in original post

9 REPLIES 9
ballardw
Super User

I might try something like:

xaxis label = 'Cohort Date' values=(200701 to 201401 by 100);

I think you are getting to many as the current version the upper bound is not greater than the increment of 10000 added to the base value of 200701.

xaxis label = 'Cohort Date' values=(200701 to 201312 by 10000);

Bankshot
Obsidian | Level 7

Sorry, it didn't work... Smiley Sad

I thought about converting the whole x-axis to numerical value, but then the gap between 200712 and 200801 would be much bigger than the gap between 200709 and 200712.

Jay54
Meteorite | Level 14

Personally, I would use a Date format on the column mapped to the x-axis with real SAS date values.  You will then get a nice "Time" axis that will only show the necessary tick values.  If you must have a character axis, you can set the Fit policy on the X axis to "THIN". 

Please always include the SAS release number.  SAS 9.4 allows you to break the axis.  Or, you can separate the 2007 and 2008 values using a class variable into two side by side cells using SGPANEL.

Bankshot
Obsidian | Level 7

Thanks Sanjay. I'm using SAS 9.3 and seems like sgpanel is only for 9.4. And we won't have that until late May i think.

So, in this scenerio i have, what other options do I have? Could I simply reduce the size of the font instead to make this work?

Jay54
Meteorite | Level 14

SGPANEL is available in SAS 9.3.

I am not sure what you want, but one way is what I have shown below.  I made your date character, extracted year and month, and used SGPANEL to make this graph using SAS 9.3.  This cleans up the axis.  You can format the axis values 1-12 to Jan, Feb, etc. if you want.  You can also make your date as numeric with a date format.

You can place the cells side by side (as below), or stacked.

Exposure.png

data exposure;

  input date $ LMA_Balance MLIB_Balance;

  year=substr(date, 1, 4);

  month=substr(date, 5, 2);

  datalines;

200701 8.47 5.66

200702 8.40 5.96

200703 8.49 5.96

200704 8.68 6.05

200705 8.66 6.05

200706 8.67 6.24

200707 8.71 6.15

200708 8.85 6.27

200709 8.95 6.29

200710 9.11 6.34

200711 9.33 6.28

200712 9.41 6.34

200801 9.17 6.27

200802 9.30 6.38

200803 9.58 6.46

200804 10.02 6.57

200805 10.25 6.72

200806 10.19 7.00

200807 10.04 6.90

200808 10.12 6.66

200809 10.16 6.36

200810 8.72 5.19

200811 8.39 4.84

200812 8.31 4.75

;

run;

ods html close;

ods listing;

ods graphics / reset width=8in height=4in imagename='Exposure';

proc sgpanel data=exposure;

  panelby year / layout=panel columns=2 onepanel uniscale=row novarname;

  series x=month y=LMA_Balance / markers markerattrs= (symbol=trianglefilled);

  series x=month y=MLIB_Balance / markers markerattrs= (symbol=starfilled);

  colaxis label = 'Cohort Date' values=(200701 to 201312 by 10000);

  rowaxis label = 'Exposure in ($) Billions' values=(5 to 12 by 1) grid;

  title 'Total Outstanding Balance';

run;

Bankshot
Obsidian | Level 7

Thanks Sanjay. This produces output a bit different from what I was expecting, but might work even better.

Again, thanks for your help on this.

Jay54
Meteorite | Level 14

This is just one suggestion to show you what is possible.

ballardw
Super User

If the variable isn't numeric the whole "from to by" structure doesn't really work.

Jay54
Meteorite | Level 14

I assumed the data on x axis was character since there are so many rotated tick values  By default, SGPLOT will not display so many rotated tick values for numeric data.  You will get only a few "nice" values.

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
  • 9 replies
  • 1962 views
  • 1 like
  • 3 in conversation