proc sgplot xaxistable- remove a treatment

[PI3]

I have a graph that I want to list both weeks since we treated and the name of the month in which we treated. I decided to do this using an xaxistable positioned on the top of the graph near the x2 axis. (I was originally trying to add an x2 axis, but didn't have luck getting it to list the months). I added a month column in the data step and added only the months that I wanted displayed. 

Eg.

data Coop;
set sasuser.Coopf2018;

if trt='Control' and week=4 then month='Jan';
if trt='Control' and week=5 then month='Feb';
if trt='Control' and week=11 then month='Mar';
if trt='Control' and week=15 then month='Apr';
if trt='Control' and week=19 then month='May';
if trt='Control' and week=23 then month='Jun';
if trt='Control' and week=27 then month='Jul';
if trt='Control' and week=31 then month='Aug';
if trt='Control' and week=37 then month='Sept';
if trt='Control' and week=41 then month='Oct';
if trt='Control' and week=45 then month='Nov';
if trt='Control' and week=49 then month='Dec';
run;

I used the Control treatment because there were two instances where we took measurements for the separate treatments a week apart, but in the same month. In those two instances, the control was measured first (a week before the Dino treatment), and I wanted the month label at the start of the month.

My issue is that SAS lists the two treatments on top of each other, and puts the control treatment over the Dino treatment. This results in an empty space between the name of the month and the x2 axis. I have tried so many things to either switch the order, or to only display the control treatment, but I haven't had any luck. Can someone please help me fix this graph and make it look sharp? Thank you.

Here is my sgplot code:

title "Mean Percentage of Occupied Nest Boxes";
proc sgplot data=m1;
vline week / response=xnest group=trt stat=mean limitstat=stderr numstd=1;
styleattrs datacontrastcolors=(blue red) datalinepatterns=(solid solid);
xaxistable month/ nolabel position=top title="Month in 2018" titleattrs=(weight=bold size=10) pad=(top=20 bottom=0);
keylegend / title='Treatment:' noborder outerpad=(top=25 bottom=0);
yaxis label="Mean Percentage of Occupied Nest Boxes" LABELATTRS=(Size=10 Weight=Bold);
xaxis label="Weeks Elapsed Since Treatment" LABELATTRS=(Size=10 Weight=Bold);
format xnest percent12.1;
run;
title;

[ballardw]

Welcome to the wonderful world of not trying to use character values as "dates" or "times".

SAS does not know that you want to use "Jan" or "Feb" as a date, so it treated as category and there is no natural space relationship between them. Date values quite often belong as actual SAS date values. Then SAS knows how to provide intervals in graphs and you have lots of tools for manipulating them. A reference you may find handy: https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/... has a PDF with much information about dates.

I think you have to be bit more specific discussing which values are "over Dino" and which space is involved. Every "Month" appears over a Control value of the given month in your code. Are you interpreting a value of Xnest for Dino greater than for Control at week 4 as making the "Jan" appear over Dino? It is over the corresponding Control.

If you want Jan to appear over week 1 then you need to have a week 1 in the data.

I actually have a hard time believing that xaxis appearance with the shown code unless your week variable is actually character because the interval between 8 and 9 is the same as between 9 and 11. Generally numeric axis would use the actual interval and without additional instructions from your generally create axis tick mark labels evenly spaced by interval.

If by "SAS lists the two treatments on top of each other", means that one group value (the "later" since "Dino" comes after "Control" in character sort order) that is happening because of the way you define your vline. You might try adding an option to Vline of Transparency=.5 , to allow some of the line underneath to show through. If you do not provided a Transparency setting then the value is 0 and results in opaque colors, i.e. the top one completely hides the bottom. A value between 0 and 1(fully transparent) will make  both lines somewhat visible though the result may be an apparent change of color as both a partial blue and partial red appear in the same part of the graph.

[PI3]

I appreciate that you attempted to help me, but we are happy with the spacing between weeks. We took data at unequal intervals for a specific reason, and we starting taking measurements at week 4 for a specific reason, as well. I don't want to use date/time formatting and I don't think I should have to. I did originally put all of my dates into date/time formatting, but it ended up making more of a mess than helping me.

Here's what is happening with the xaxistable, slightly clarified.

It is listing the two treatments in two rows like this:

Control month, month, month, etc.

Dino  month, month, month, etc.

I removed labels, so the Control and Dino labels are not showing. And, since the Dino treatment has all blank months, that row is completely blank. I just want to get rid of that treatment so that the Control treatment months are slightly closer to the x2 axis. Or, I could also have them listed like this instead:

Dino  month, month, month, etc.

Control month, month, month, etc.

That would also solve my problem, which is purely aesthetic.

I am happy with all other aspects of this graph.

Thank you. I'm hoping I can get some more help on this problem from someone.

[JeffMeyers]

Hello, I would like to help but find it easiest when actually trying things myself.  Could you post your dataset as an Excel or something so I can try your code directly?  Just the graph dataset used in the SGPLOT code.

[ballardw]

---

@PI3 wrote:

Here's what is happening with the xaxistable, slightly clarified.

It is listing the two treatments in two rows like this:

Control month, month, month, etc.

Dino  month, month, month, etc.

I removed labels, so the Control and Dino labels are not showing. And, since the Dino treatment has all blank months, that row is completely blank. I just want to get rid of that treatment so that the Control treatment months are slightly closer to the x2 axis. Or, I could also have them listed like this instead:

Dino  month, month, month, etc.

Control month, month, month, etc.

---

So you mean the appearance of the XAXISTABLE values is "too far" from the border of the graph?

Provide some data to test code against. I have some ideas but without data they are hard to test.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the <> icon or attached as text to show exactly what you have and that we can test code against.

[PI3]

I feel uncomfortable adding our data to a public forum. This isn't my research, I'm just helping analyse it and I don't think the researcher would appreciate the information being readily available on the internet before publication. I really wish that I could because this has been frustrating me to no end and I would love some help.

I am trying something else now, and encountering new and different problems. Maybe this is an easier problem to solve, though..?

We have some other graphs that need to be generated that are going to have four treatments. If I can't specify the treatment with the xaxistable, this is going to create even more blank space. I was thinking that maybe I could use the x2axis instead and add the months that way. The issue is that I'm not sure there's a way to do this while plotting with vline. All that I can find are statements that switch the x-axis to the x2axis. Does anyone know if there is a way to do this? I switched the week values to a character format and I wanted to use the values=() statement and then valuesdisplay=() statement to change them to the months.

title "Average TCA count";
proc sgplot data=m1;
vline weekchar / response=xTCA group=trtboxflag stat=mean limitstat=stderr numstd=1;
styleattrs datacontrastcolors=(blue red green purple) datalinepatterns=(solid dash shortdash longdash);
keylegend / title='Treatment:' noborder outerpad=(top=25 bottom=0);
yaxis label="Average Tawny Crazy Ant count" LABELATTRS=(Size=10 Weight=Bold);
xaxis label="Weeks Elapsed Since Treatment" LABELATTRS=(Size=10 Weight=Bold);
scatter x=weekchar y=xTCA / markerattrs=(size=0) x2axis;
x2axis label="Month in 2018" LABELATTRS=(Size=10 Weight=Bold) values=('04' '05' '06' '07' '08' '09' '11' '13' '15' '17' '19' '21' '23' '25' '27' '29' '31' '33' '35' '37' '39' '41' '43' '45' '49' '50') valuesdisplay=('Jan' 'Feb' '' '' '' '' 'Mar' '' 'Apr' '' 'May' '' 'Jun' '' 'Jul' '' 'Aug' '' '' 'Sept' '' 'Oct' '' 'Nov' 'Dec' '');
run;
title;

I do understand playing around with something to get it to work (that's how I've been doing everything while learning how to use this program!). If there's not an easy way to add an x2 axis, I could maybe create some fake data that wouldn't be a problem to post. 

I'm very new at this and a little bit in over my head, honestly. 

[JeffMeyers]

Can you change your values by a randomly generated number and then post the dataset?  I don't really care what the numbers are but just having something with the same structure will allow me to test solutions.

[PI3]

Does this work for you? The graphs aren't going to look exactly the same, but I think it should be okay to play with.  I am not used to inputting data manually (we import from excel), so I'm not sure if this will work to create the data in the datastep. This is equivalent to the m1 data set.

data WORK.FAKEDATA;
infile datalines dsd truncover;
input Obs:32. Site:$6. Rep:32. Week:32. month:$3. Trt:$7. _TYPE_:32. _FREQ_:32. nnest:32. xnest:32.;
label Obs="Obs" Site="Site" Rep="Rep" Week="Week" month="month" Trt="Trt" _TYPE_="_TYPE_" _FREQ_="_FREQ_" nnest="nnest" xnest="xnest";
datalines;
1 Cback 1 4 Jan Control 0 7 4 0.3
2 Cback 1 5 Feb Control 0 7 7 0.5
3 Cback 1 6 Control 0 7 4 0.4
4 Cback 1 8 Control 0 7 7 0.6
5 Cback 1 9 Control 0 7 7 0.8
6 Cback 1 11 Mar Control 0 7 7 0.5
7 Cback 1 13 Control 0 7 7 1
8 Cback 1 15 Apr Control 0 7 7 0.8
9 Cback 1 17 Control 0 7 7 0.9
10 Cback 1 19 May Control 0 7 7 0.9
11 Cback 1 21 Control 0 7 7 0.5
12 Cback 1 23 Jun Control 0 7 7 0.3
13 Cback 1 25 Control 0 7 7 0.3
14 Cback 1 27 Jul Control 0 7 6 0.8
15 Cback 1 29 Control 0 7 7 0.2
16 Cback 1 31 Aug Control 0 7 7 0.8
17 Cback 1 33 Control 0 7 7 0.6
18 Cback 1 35 Control 0 7 7 0.9
19 Cback 1 37 Sep Control 0 7 7 0.8
20 Cback 1 39 Control 0 7 7 0.6
21 Cback 1 41 Oct Control 0 7 7 0.7
22 Cback 1 43 Control 0 7 7 0.5
23 Cback 1 45 Nov Control 0 7 7 0.6
24 Cback 1 49 Dec Control 0 7 7 0.7
25 Cback 2 4 Jan Control 0 7 4 0.5
26 Cback 2 5 Feb Control 0 7 7 0.6
27 Cback 2 6 Control 0 7 4 0.8
28 Cback 2 8 Control 0 7 7 0.9
29 Cback 2 9 Control 0 7 7 0.3
30 Cback 2 11 Mar Control 0 7 7 0.6
31 Cback 2 13 Control 0 7 7 0.3
32 Cback 2 15 Apr Control 0 7 7 0.6
33 Cback 2 17 Control 0 7 7 1
34 Cback 2 19 May Control 0 7 7 1
35 Cback 2 21 Control 0 7 6 0.8
36 Cback 2 23 Jun Control 0 7 7 0.9
37 Cback 2 25 Control 0 7 7 0.7
38 Cback 2 27 Jul Control 0 7 7 1
39 Cback 2 29 Control 0 7 7 0.3
40 Cback 2 31 Aug Control 0 7 7 0.9
41 Cback 2 33 Control 0 7 7 1
42 Cback 2 35 Control 0 7 6 1
43 Cback 2 37 Sep Control 0 7 7 1
44 Cback 2 39 Control 0 7 7 0.9
45 Cback 2 41 Oct Control 0 7 7 0.9
46 Cback 2 43 Control 0 7 7 1
47 Cback 2 45 Nov Control 0 7 7 1
48 Cback 2 49 Dec Control 0 7 7 1
49 Cfront 1 4 Dino 0 42 41 0.6
50 Cfront 1 6 Dino 0 42 42 0.5
51 Cfront 1 7 Dino 0 42 42 0.6
52 Cfront 1 8 Dino 0 42 42 0.2
53 Cfront 1 9 Dino 0 42 42 0.1
54 Cfront 1 11 Dino 0 42 42 0.1
55 Cfront 1 13 Dino 0 42 42 0.1
56 Cfront 1 15 Dino 0 42 42 0.05
57 Cfront 1 17 Dino 0 42 42 0
58 Cfront 1 19 Dino 0 42 40 0
59 Cfront 1 21 Dino 0 42 41 0.05
60 Cfront 1 23 Dino 0 42 41 0.3
61 Cfront 1 25 Dino 0 42 41 0.1
62 Cfront 1 27 Dino 0 42 41 0.5
63 Cfront 1 29 Dino 0 42 41 0.3
64 Cfront 1 31 Dino 0 42 42 0.4
65 Cfront 1 33 Dino 0 42 41 0.4
66 Cfront 1 35 Dino 0 42 41 0.5
67 Cfront 1 37 Dino 0 42 39 0.4
68 Cfront 1 39 Dino 0 42 40 0.5
69 Cfront 1 41 Dino 0 42 41 0.4
70 Cfront 1 43 Dino 0 42 40 0.5
71 Cfront 1 45 Dino 0 42 41 0.05
72 Cfront 1 50 Dino 0 42 39 0.1
73 Cfront 2 4 Dino 0 30 30 0.8
74 Cfront 2 6 Dino 0 30 29 0.7
75 Cfront 2 7 Dino 0 34 30 0.9
76 Cfront 2 8 Dino 0 34 34 0.5
77 Cfront 2 9 Dino 0 34 30 0.4
78 Cfront 2 11 Dino 0 34 30 0.1
79 Cfront 2 13 Dino 0 34 29 0.2
80 Cfront 2 15 Dino 0 34 30 0.3
81 Cfront 2 17 Dino 0 34 30 0.3
82 Cfront 2 19 Dino 0 34 26 0.3
83 Cfront 2 21 Dino 0 34 29 0.1
84 Cfront 2 23 Dino 0 34 30 0.1
85 Cfront 2 25 Dino 0 34 30 0.1
86 Cfront 2 27 Dino 0 34 10 0.5
87 Cfront 2 29 Dino 0 34 30 0.05
88 Cfront 2 31 Dino 0 34 30 0.2
89 Cfront 2 33 Dino 0 34 29 0.3
90 Cfront 2 35 Dino 0 34 28 0.3
91 Cfront 2 37 Dino 0 34 28 0.07
92 Cfront 2 39 Dino 0 34 28 0.2
93 Cfront 2 41 Dino 0 34 29 0.05
94 Cfront 2 43 Dino 0 34 30 0.05
95 Cfront 2 45 Dino 0 34 29 0
96 Cfront 2 50 Dino 0 34 30 0

[JeffMeyers · Accepted solution]

Here is what I did making some random data that is similar:

data m1;
    call streaminit(123);
    do i = 4 to 50 by 1;
        week=i;trt='Control';
        if trt='Control' and week=4 then month='Jan';
        if trt='Control' and week=5 then month='Feb';
        if trt='Control' and week=11 then month='Mar';
        if trt='Control' and week=15 then month='Apr';
        if trt='Control' and week=19 then month='May';
        if trt='Control' and week=23 then month='Jun';
        if trt='Control' and week=27 then month='Jul';
        if trt='Control' and week=31 then month='Aug';
        if trt='Control' and week=37 then month='Sept';
        if trt='Control' and week=41 then month='Oct';
        if trt='Control' and week=45 then month='Nov';
        if trt='Control' and week=49 then month='Dec';
        do j = 1 to 100; xnest=rand("Uniform");output; end; 
            
        month='';xnest=.;
        week=i;trt='Dino';
        do j = 1 to 100; xnest2=rand("Uniform");output; end;   
        output;
    end;
run;
        
proc sgplot data=m1;
vline week / response=xnest stat=mean limitstat=stderr numstd=1 legendlabel='Control' name='one';
vline week / response=xnest2 stat=mean limitstat=stderr numstd=1 legendlabel='Dino' name='two';
styleattrs datacontrastcolors=(blue red) datalinepatterns=(solid solid);
xaxistable month/ nolabel position=top title="Month in 2018" titleattrs=(weight=bold size=10) pad=(top=20 bottom=0);

keylegend 'one' 'two'/ title='Treatment:' noborder outerpad=(top=25 bottom=0);
yaxis label="Mean Percentage of Occupied Nest Boxes" LABELATTRS=(Size=10 Weight=Bold);
xaxis label="Weeks Elapsed Since Treatment" LABELATTRS=(Size=10 Weight=Bold);
format xnest percent12.1;
run;
title;

When I tried your exact SGPLOT I used TMPLOUT to see what the actual GTL code in the background was doing.  I found out that even though you didn't specify a CLASS option in the XAXISTABLE that it was applying one automatically.  If I removed the CLASS option in GTL the extra space went away, but there wasn't a way to do this in the SGPLOT code.  So I instead broke up your estimates variable into two (one for each treatment) and did two separate VLINE statements with no GROUP options.  This cleared up the issue for me:

[PI3]

Hey, so there's actually a problem.... 😞

Removing group changes the statistics. I thought the graphs were the same at first and that the axis was just different, but they aren't. On top is the output generated by adding 'group' back in. (Also, I checked that the multiple vline statements with group=trt matched the original graph and it did.)

Top- correct, but ugly graph code:

data m1;
set m1;
if trtboxflag='Control,f' then xTCA2=xTCA;
if trtboxflag='Dino,b' then xTCA3=xTCA;
if trtboxflag='Dino,f' then xTCA4=xTCA;
run;

title "Mean Tawny Crazy Ant count";
proc sgplot data=m1;
vline week / response=xTCA group=trtboxflag stat=mean limitstat=stderr numstd=1 legendlabel='Control, b' name='one';
vline week / response=xTCA2 group=trtboxflag stat=mean limitstat=stderr numstd=1 legendlabel='Control, f' name='two';
vline week / response=xTCA3 group=trtboxflag stat=mean limitstat=stderr numstd=1 legendlabel='Dino, b' name='three';
vline week / response=xTCA4 group=trtboxflag stat=mean limitstat=stderr numstd=1 legendlabel='Dino, f' name='four';
styleattrs datacontrastcolors=(blue red green purple) datalinepatterns=(solid dash shortdash longdash);
xaxistable month/ nolabel position=top title="Month in 2018" titleattrs=(weight=bold size=10) pad=(top=20 bottom=0);

keylegend 'one' 'two' 'three' 'four' / title='Treatment:' noborder outerpad=(top=25 bottom=0);
yaxis label="Mean Tawny Crazy Ant Count" LABELATTRS=(Size=10 Weight=Bold);
xaxis label="Weeks Elapsed Since Treatment" LABELATTRS=(Size=10 Weight=Bold);
run;
title;

Bottom- incorrect, but pretty graph code:

data m1;
set m1;
if trtboxflag='Control,f' then xTCA2=xTCA;
if trtboxflag='Dino,b' then xTCA3=xTCA;
if trtboxflag='Dino,f' then xTCA4=xTCA;
run;

title "Mean Tawny Crazy Ant count";
proc sgplot data=m1;
vline week / response=xTCA stat=mean limitstat=stderr numstd=1 legendlabel='Control, b' name='one';
vline week / response=xTCA2 stat=mean limitstat=stderr numstd=1 legendlabel='Control, f' name='two';
vline week / response=xTCA3 stat=mean limitstat=stderr numstd=1 legendlabel='Dino, b' name='three';
vline week / response=xTCA4 stat=mean limitstat=stderr numstd=1 legendlabel='Dino, f' name='four';
styleattrs datacontrastcolors=(blue red green purple) datalinepatterns=(solid dash shortdash longdash);
xaxistable month/ nolabel position=top title="Month in 2018" titleattrs=(weight=bold size=10) pad=(top=20 bottom=0);

keylegend 'one' 'two' 'three' 'four' / title='Treatment:' noborder outerpad=(top=25 bottom=0);
yaxis label="Mean Tawny Crazy Ant Count" LABELATTRS=(Size=10 Weight=Bold);
xaxis label="Weeks Elapsed Since Treatment" LABELATTRS=(Size=10 Weight=Bold);
run;
title;

quit;

[JeffMeyers]

One potential issue I see in your code is this:

data m1;
set m1;
if trtboxflag='Control,f' then xTCA2=xTCA;
if trtboxflag='Dino,b' then xTCA3=xTCA;
if trtboxflag='Dino,f' then xTCA4=xTCA;

You are assigning separate variables for only three of the four values.  That means when you use the original variable, xTCA for your "Control,b" group it is using ALL FOUR groups for it.  Make a fourth variable for just this level like you have with the other three groups and I think your values will be correct in your pretty graph.  It's hard to tell because the y-axes are different but I think your blue group is the only one that changes (the Control,b group).

[PI3]

Ah- that fixed it! Thank you for the continued help. I understand the point of assigning the different response variables a little bit better now, too. This has definitely been a learning experience for me.

You're the best!

[PI3]

Okay! I'm definitely going to have to google various aspects of this in order to understand what you did, but this is definitely giving me something new to work off of and removing one roadblock.  This is what that sgplot generates for me: 

Is there a way to only display the weeks in which we took measurements? And also not have February overlapping with January? I'm sorry, this is a bit like, "If you give a mouse a cookie," haha. Now I just want all my issues solved.

[PI3]

Oh! I'm so sorry, I'm an idiot. You don't need to respond. I was confused. This solved everything for me. Thank you! You are my savior! I really, really appreciate all the wonderful people on here who use their free time to help out n00bs like me learn SAS. 

❤️