topic Re: SGPLOT- Y axis values same as the variable values and By-Variable as Sheet Names in excel in Graphics Programming

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

Newbie_sas_YR — Thu, 29 Oct 2020 00:17:48 GMT

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 = ' ';

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

ballardw — Thu, 29 Oct 2020 14:45:58 GMT

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-data-AKA-generate/ta-p/258712 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')

;

ods excel close;

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

Newbie_sas_YR — Thu, 29 Oct 2020 21:58:38 GMT

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
;;

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

Newbie_sas_YR — Thu, 29 Oct 2020 22:10:29 GMT

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.

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

BrunoMueller — Fri, 30 Oct 2020 17:38:16 GMT

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;

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

Newbie_sas_YR — Fri, 30 Oct 2020 20:11:33 GMT

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?

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

BrunoMueller — Mon, 02 Nov 2020 07:09:09 GMT

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/

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

Newbie_sas_YR — Wed, 04 Nov 2020 21:02:53 GMT

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!