SGPLOT- Y axis values same as the variable values and By-Variable as Sheet Names in excel

[Newbie_sas_YR]

A Newbie esp in SAS Grahs- I am using SGPLOT to get the graphs for 2 variables.

Code is as follows:

1.The Y axis is fine but, the y2-axis values are the same as the variable values and Y2 Axis is not ordered and crowded.

2. Trying to output each graph into separate excel sheet and want the sheet name to be the "By-Variable"- Not able to get the sheet names correct.

3. X-Axis formatting not working right.

- TIA

proc sgplot data=new2;
styleattrs datacolors=(red blue);

  xaxis type=discrete;
  Y2AXIS TYPE=DISCRETE;
  series x=date y=s_count / datalabel  datalabelattrs=(size=12pt)lineattrs=(color=red thickness=3px)    legendlabel = 'SCount'
  by cvar;
  series x=date y=_M_Count / DATALABEL datalabelattrs=(size=12pt)lineattrs=(color=blue thickness=3px)  
      legendlabel = 'MCount'  y2axis;
   by cvar;
    y2axis label = "Member Count " display=(noline) grid;
   yaxis label = "Acc Count " display=(noline) grid;
   xaxis interval=MONTH label=' ' valuesformat=MONYY7. minor minorcount=120;

   xaxis label = ' ';
  

[ballardw]

Without data to test code we can't see what your Y2Axis or Xaxis statements are doing. Plus you need to describe what is "not working right" with the Xaxis as I might "fix" something that is what you actually want without knowing what is expected.

Provide at least a little data as a data step. 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.

If you want the output to go to excel with a separate sheet for each graph then  your graphing code should go between lines like:

ods excel file="<path to folder>\nameoffile.xlsx"

   options(sheet_interval='BYGROUP' sheet_name='#BYVAL')

;

<sgplot with BY statement goes here>

ods excel close;

[Newbie_sas_YR]

This is the Dataset:

data WORK.NEW1;
  infile datalines dsd truncover;
input Date:DATE9. cli:$5. sbr_count:$5. _Mbr_Count:$5.;
datalines;
01MAR2020 00090 1941 3899                          
02MAR2020 00090 1938 3885                          
09MAR2020 00090 1933 3876                           
16MAR2020 00090 1933 3874                           
23MAR2020 00090 1936 3874                           
30MAR2020 00090 1936 3874                           
06APR2020 00090 1936 3868                           
13APR2020 00090 1936 3867                          
20APR2020 00090 1941 3868                          
27APR2020 00090 1941 3868                           
04MAY2020 00090 1942 3862                           
11MAY2020 00090 1943 3862                           
18MAY2020 00090 1939 3845                           
25MAY2020 00090 1940 3850                           
...... . . . . . . . . . . . . . . . . . .
01MAR2020 00445 3965 7907 . .                         
02MAR2020 00445 3970 7916                           
09MAR2020 00445 3979 7938                         
16MAR2020 00445 3985 7951                        
23MAR2020 00445 3991 7967                        
30MAR2020 00445 4001 7989                         
06APR2020 00445 3936 7855                         
13APR2020 00445 3944 7869                          
20APR2020 00445 3954 7881                           
27APR2020 00445 3969 7905                           
04MAY2020 00445 3915 7802                          
11MAY2020 00445 3924 7814                           
18MAY2020 00445 3936 7832                           
25MAY2020 00445 3940 7836                           
;;

[Newbie_sas_YR]

Thank You. I have attached the Dataset sample and the Query and the Output.
I have 4 sheets with different data and the 5th sheet starts the SGPLOT produce with the By - Group Variable .There is Y2- Axis

-1. It is not sorted in ascending order like the Y- Axis. And if you observe the values are the same as the column values- Mbr_Count. How can I fix it.I do not want to specify values because each by-variable has different range ..unless there is a way of working around that.
2. I have already tried that the sheet Names are the By- Group variable names and that is not working. Any help is Appreciated. TIA.

[BrunoMueller]

The data and the program provided did not match, so I made some assumptions on how the variables might be used.

I made some changes to your Proc SGPLOT, mainly removed double entries and corrected some typos. As a genral rule there is only one BY statement in a procedure. Always just start with the basics and add additional options as necessary.

The following code should bring you close to what you want:

data WORK.NEW1;
  infile datalines ;
  input Date:DATE9. cvar : $5. s_count : 8. _M_Count : 8.;
  format date date9.;
  datalines;
01MAR2020 00090 1941 3899
02MAR2020 00090 1938 3885
09MAR2020 00090 1933 3876
16MAR2020 00090 1933 3874
23MAR2020 00090 1936 3874
30MAR2020 00090 1936 3874
06APR2020 00090 1936 3868
13APR2020 00090 1936 3867
20APR2020 00090 1941 3868
27APR2020 00090 1941 3868
04MAY2020 00090 1942 3862
11MAY2020 00090 1943 3862
18MAY2020 00090 1939 3845
25MAY2020 00090 1940 3850
01MAR2020 00445 3965 7907
02MAR2020 00445 3970 7916
09MAR2020 00445 3979 7938
16MAR2020 00445 3985 7951
23MAR2020 00445 3991 7967
30MAR2020 00445 4001 7989
06APR2020 00445 3936 7855
13APR2020 00445 3944 7869
20APR2020 00445 3954 7881
27APR2020 00445 3969 7905
04MAY2020 00445 3915 7802
11MAY2020 00445 3924 7814
18MAY2020 00445 3936 7832
25MAY2020 00445 3940 7836
;

ods excel file="c:\temp\results.xlsx"
  options(
    sheet_interval='BYGROUP'
    sheet_label='prefix' 
  )
;

proc sgplot data=new1 noborder;
  by cvar;
  styleattrs datacolors=(red blue);

  /*  xaxis type=discrete;*/
  /*  Y2AXIS TYPE=DISCRETE;*/
  series x=date y=s_count /
    datalabel      legendlabel = 'SCount'
  ;
  series x=date y=_M_Count /
    datalabel legendlabel = 'MCount'  y2axis
  ;
  xaxis interval=MONTH label=' ' valuesformat=MONYY7.;

  yaxis label = "Acc Count " display=(noline) grid;
  y2axis label = "Member Count " display=(noline) grid;
run;
ods excel close;

[Newbie_sas_YR · Accepted solution]

The Y2 axis values are not in ascending order and strangely they have the same values of _M_count variable for each date(x-axis).
The Y- axis(S-Count) is not doing that just with the _M_count. I also output only graph with just
series x=date y=_M_Count and that is still happening with _M_Count. Is it something to do with the way the Dataset is?

[BrunoMueller]

Please provide the sample code you run as well as the result you get, this will help to understand what you explain.

Axis scaling and number of thick marks is done automatically depending on the data. There are ways to control this, using the YAXIS/YAXIS2 statements. Have a look at this blog for more information https://blogs.sas.com/content/graphicallyspeaking/2012/04/30/axis-values-and-hint/

[Newbie_sas_YR]

The problem I was experiencing was because the Y2 axis variable came in as character. SO, the axis was not in ascending order. Thank you!