proc sgplot log issue

[purpleclothlady]

hi all:

I am stucked in this and can't figure out how to solve the log issue. please advise.

Thank you.

/*Plotting on same graph by ID*/
ods results off;
ods listing close;
ods rtf file = "c:\plot.rtf";		

proc sgplot data=have noautolegend;
   series x=time1 y=y1/break  markers ;
	 series x=time1 y=y2/break markers y2axis ;
	 by id;

  xaxis display=(nolabel)      label = "Time(Day)";
  yaxis grid  values = (5 to 100 by 10)          label="y1";
	y2axis values= (0 to 100 by 10)	               label="y2";
	keylegend "y1 y2" / location=inside position=top; 
run; 

ods rtf close;
ods listing;
ods results on;

[PaigeMiller]

It's nearly impossible for us to work from partial LOGs, we need to see the ENTIRE log for this PROC SGPLOT. We also need to see a portion of the data set named HAVE, presented as working SAS data step code (and not via any other method).

By the way, the WARNING about Y=Y2 is invalid seems rather clear to me. What part is not clear?

[purpleclothlady]

Hi @PaigeMiller :

Have data is attached again as have.sas7bdat

log is attached as havelog.docx

Thanks so much for helping.

here is code 

data have;
set f.have;
run;
proc sort;by id time1;run;

/*Plotting on same graph by ID*/
ods results off;
ods listing close;
ods rtf file = "C:\Users\Temp\plot.rtf";		

proc sgplot data=have noautolegend;
   series x=time1 y=y1/break  markers ;
   series x=time1 y=y2/break markers y2axis ;
   by id;

  xaxis display=(nolabel)      label = "Time(Day)";
  yaxis grid  values = (5 to 100 by 10)          label="y1";
	y2axis values= (0 to 100 by 10)	               label="y2";
	keylegend "y1 y2" / location=inside position=top; 
run; 
ods rtf close;
ods listing;
ods results on;

[Rick_SAS]

For some reason I cannot open your SAS data set, but here are my thoughts:

1. The WARNING says that PROC SGPLOT thinks that the Y2 variable contains only missing values. This usually happens when the variable was read incorrectly. Try running
proc means data=have min max N NMISS;
var time1 y1 y2;
run;

to see if Y2 has any nonmissing values;

2. The NOTE says that the plot is large enough to plot all the tick marks. I don't know if it is the X or Y axis that is complaining, but the output from PROC MEANS should help us decide. This error can also occur if you are reading X from an excel spreadsheet and the TIME1 variable is being read as a character variable rather than as numeric with a time or date format. So make sure the range of TIME1 makes sense.

[purpleclothlady]

@Rick_SAS :

Thank you for helping.

Here is the proc means output and snippet of "Have" dataset

- Y1, Y2 had some missing values , but NOT all were missing.

- Time1 is numeric variables 

-Data was taken from sas datasets

[Rick_SAS]

You need to add

BY ID;

to your PROC MEANS call.

Your output shows that OVERALL, not all of the Y values are missing. However, you have BY ID in the call to PROC SGPLOT, which means that it will try to create a plot for each value of the ID variable. I am confident that some of your ID values have all missing values for a Y variable.

For an example, run .this test program:

data Test;
ID = 1;
do time1 = 1 to 10;
   y2 = time1;
   if time1 in (3,4,8) then y2=.;
   output;
end;
ID = 2;
do time1 = 1 to 5;
   y2 = .;
   output;
end;
ID = 3;
do time1 = 1 to 5;
   y2 = 5-time1;
   if time1 in (3,5) then y2=.;
   output;
end;
run;

proc sgplot data=Test;
   series x=time1 y=y2 / break markers;
   by ID;   /* Note all values of Y2 are missing when ID=2 */
run;

[PaigeMiller]

Some of us (like me) refuse to download attached files.

The log you can post in your message as text. Data should be provided as working SAS data step code (instructions).

[ballardw]

Any time you attempt to create more tick marks than will display reasonably, given the options you provide, SAS will thin, i.e. reduce the number of values shown based on the current value of the FITPOLICY setting  to create something legible. The default for an axis not set with the Type= option is to THIN. You get one message for each graph, value of your BY variable.

An example of looking at some options affecting an axis and how many tick marks and the text displayed. The title comments about how well text displays is based on my ods graphics width and height settings so you may not get exactly the same result but hopefully see changes based on changing the TYPE= option which changes the default Fitpolicy from Thin to StaggerRotate, and the affect of the number of requested ticks plus changing the ValuesRotate from slant to vertical.

Text attributes may also play a part in causing thinning if the text is tall enough to encroach on tick text above/below the values.

So look up FITPOLICY options on the yaxis and y2axis, Type=, and other text display options and play with them.

data junk;
   do x= 1 to 1000;
     y= sin(x/200);
     output;
   end;
run;

proc sgplot data=junk;
  title "Request too many ticks, x axis thinned default fitpolicy=thin";
  series x=x y=y;
  xaxis values= (0 to 1000 by 1);
run; title;
proc sgplot data=junk;
  title "Request too many ticks, x axis discrete likely unreadable";
  series x=x y=y;
  xaxis values= (0 to 1000 by 1) type=discrete;
run; title;
proc sgplot data=junk;
  title "Request too many ticks fewer, x axis discrete, not quite readable";
  series x=x y=y;
  xaxis values= (0 to 1000 by 10) type=discrete;
run; title;

proc sgplot data=junk;
  title "Request many ticks much fewer, x axis discrete, almost readable";
  series x=x y=y;
  xaxis values= (0 to 1000 by 20) type=discrete;
run; title;
proc sgplot data=junk;
  title "Request many ticks much fewer, x axis discrete, barely readable";
  series x=x y=y;
  xaxis values= (0 to 1000 by 20) type=discrete valuesrotate=vertical;
run; title;
proc sgplot data=junk;
  title "Request too many ticks much fewer, x axis discrete, probably readable";
  series x=x y=y;
  xaxis values= (0 to 1000 by 50) type=discrete;
run; title;

proc sgplot data=junk;
  title "Request many fewer ticks, x axis not thinned";
  series x=x y=y;
  xaxis values= (0 to 1000 by 100);
run; title;

[purpleclothlady]

Hi Geeks: 

I spent some time to use the @PaigeMiller to get

"https://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/" 

1. the sasdata , now it is available. 

2. proc means does show Y2 for 

ID=884-008 has ALL missing values (but that is what the data is)

3. Run sgplot (or maybe other plotting procedures)

  Goal: 1. **Need to show x scale by ID, how to create dynamic Time1 scale-X Axis? 2.** Need to use SOLID LINES to connect data, but if there is missing Y, then connect with DASHED LINE

Thanks all for your input. 🙏

/********************************************************************************************************
Porgram name: DATA2DATASTEP.sas
Goal: Turn a SAS DATA set into a DATA step

Reference: 
https://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/
*******************************************************************************************************/
LIBNAME F "C:\temp";

%macro DATA2DATASTEP(dsn,lib,obs);

/*Step1: SQL query that gets a space delimited list of the variable names*/
proc sql noprint;
select Name
      into :varlist separated by ' '
   from dictionary.columns
   where libname="&lib"
     and memname="&dsn";
quit;
%put &varlist;

/*Step2: Gets a space delimited list of the variable names concatenated with a ':' and required informat, 
         which we will use on the DATA step INPUT statement to read the values in from the DATALINES:*/
proc sql noprint;
select case type
          when 'num' then 
             case 
                when missing(format) then cats(Name,':32.')
                else cats(Name,':',format)
             end 
          else cats(Name,':$',length,'.')
       end
      into :inputlist separated by ' '
   from dictionary.columns
   where libname="&lib"
     and memname="&dsn";
quit;

%put &inputlist;

/*Step3: DATA _NULL_ step to write a DATA step so we can copy and paste data to the Section: RAW DATA */
data _null_;
   set &lib..&dsn(obs=&obs) end=last; 
   put &varlist @;
   if last then do;
      put;
      put ';;;;';
   end;
   else put;
run;
%mend;

%data2datastep
(HAVE,F,343)

/*-----------------------------------------------------------------------------------------
                                  RAW DATA
------------------------------------------------------------------------------------------*/
 data have;
 infile datalines dlm='' missover /*end missing*/ dsd /*beginning and middle*/;
 input ID $ TIME1 y1 y2;
 datalines;
818-001 1 62
818-001 2 74
818-001 3 79 7.7
818-001 4 73 7.7
818-001 5  7.7
818-001 6 67 8.8
818-001 7 70 10.5
818-001 8 68 12.3
818-001 9 67 14
818-001 10 63 14.5
818-001 11 62 14.5
818-001 12 63 15.5
818-001 13 69 15.5
818-001 14 66 15.5
818-001 15 66 15.5
818-001 16  16.6
818-001 17 64 17.5
818-001 18 69 18.3
818-001 19 64 19.2
818-001 20 73 19.2
818-001 21 66 20
818-001 22 74 20
818-001 23 62 21
818-001 24 58 21
818-001 25 71 22.9
818-001 26 62 24.6
818-001 27 58 24.6
818-001 28 69 24.6
818-001 29 65 26.3
818-001 30 60 26.3
818-001 31 68 28
818-001 32 63 28
818-001 33 70 28
818-001 34 69 28
818-001 35 66 28
818-001 36 80 29.8
818-001 37 66 29.8
818-001 38 66 31.5
818-001 39 67 31.5
818-001 40 66 31.5
818-001 41 61 33.2
818-001 42 70 33.2
818-001 43 61 35
818-001 44 66 35
818-001 45 60 35
818-001 46 62 35
818-001 47 61 36.7
818-001 48 61 36.7
818-001 49 61 36.7
818-001 50  36.7
818-001 51 63 38.4
818-001 52  38.4
818-001 53 69 40.1
818-001 54 60 40.1
818-001 55 59 40.1
818-001 56 62 41.9
818-001 57 60 41.9
818-001 58 60 41.9
818-001 59 64 41.9
818-001 60  41.9
818-001 61 53 41.9
818-001 62 57 44.1
818-001 63 59
818-001 65 61
818-001 66 61
818-001 67 61
818-001 68 60
818-001 69 62
818-001 70 59
818-001 71 62
818-001 72 60
818-001 73 59
818-001 74 64
818-001 75 71
818-001 76 63
818-001 77 60
818-001 78 64
818-001 79 61
818-001 82 56
818-001 84 61
818-001 93 57
818-001 94 55
848-001 1 55
848-001 2 52 2
848-001 3  6
848-001 4  8
848-001 5  8
848-001 6  10
848-001 7 43 10
848-001 8 42 10
848-001 9 41 10
848-001 10 42 12
848-001 11 51 12
848-001 12 43 14
848-001 13 40 14
848-001 14 44 16
848-001 15 49 16
848-001 16 46 18
848-001 17 46
848-001 18 49
848-001 19 60
848-001 20 47 22
848-001 21 49
848-001 22 53
848-001 23 50
848-001 24 54
848-001 26 54
848-001 27 52
851-001 1 56
851-001 2 44 2
851-001 3  6
851-001 4  8
851-001 5 36
851-001 6 42
851-001 7 41
851-001 8 52
851-001 9 46 9
851-001 10 41 9
851-001 11 56 10
851-001 12 56 10
851-001 13 46 9
851-001 14 60 9
851-001 15  10
851-001 16 45 10
851-001 17 42 10
851-001 18 51 10
851-001 19 58 11
851-001 20 48 11
851-001 21 47 11
851-001 22  11
851-001 23 46 12
851-001 24  12
851-001 25  12
851-001 26  12
851-001 27 52 13
851-001 28 61 13
851-001 29 54 13
851-001 30  13
851-001 31  14
851-001 32 51 14
851-001 33 53 14
851-001 34 46 14
851-001 35  15
851-001 36  15
851-001 37 52 15
851-001 38  15
851-001 39  16
851-001 40  16
851-001 41 40 16
851-001 42 61 16
851-001 43 57 17
851-001 44 56 17
851-001 45 53 17
851-001 46  17
851-001 47 53 18
851-001 48 57 18
851-001 49 54 18
851-001 50 54 18
851-001 51 42 20
851-001 52 52 20
851-001 53 56 20
851-001 54  20
851-001 55 55 22
851-001 56 52 22
851-001 57 53 22
851-001 58  22
851-001 59  24
851-001 60 57 24
851-001 61 46 24
851-001 62 46 24
851-001 63  24
851-001 64 46 26
851-001 65  26
851-001 66  26
851-001 67 53 26
851-001 68  28
851-001 69 46 28
851-001 70 51 28
851-001 71 48 28
851-001 72 50 30
851-001 73 53 30
851-001 74 49 30
851-001 75 49 30
851-001 76 46 32
851-001 77  32
851-001 78 37 32
851-001 79 46 32
851-001 80 43 34
851-001 81  34
851-001 82 40 34
851-001 83 45 34
851-001 84 41 36
851-001 85  36
851-001 86 42 36
851-001 87  36
851-001 88 45 38
851-001 89  38
851-001 90 41 38
851-001 91 36 38
851-001 92 42 40
851-001 93 50 40
851-001 94  40
851-001 95 45 40
851-001 96  40
851-001 97  40
851-001 98 44 40
851-001 99 60 40
851-001 100  40
851-001 101 49 40
851-001 102  42
851-001 103 55 42
851-001 104 40 42
851-001 105 46 42
851-001 106  44
851-001 107 40 44
851-001 108 43 44
851-001 109  44
851-001 110 40 46
851-001 111 38 46
851-001 112  46
851-001 113 40 46
851-001 114 42 48
851-001 115  48
851-001 116 43 48
851-001 117  48
851-001 118 48 50
851-001 119 42 50
851-001 120  50
851-001 121 36 50
851-001 122 38 52
851-001 123  52
851-001 124 31 52
851-001 125 40 52
851-001 126 35 54
851-001 127 37 54
851-001 128 44 54
851-001 129  54
851-001 130 52 56
851-001 136  56
851-001 132 43 56
851-001 133  56
851-001 134 40 58
851-001 135  58
851-001 136 46 58
851-001 137  58
851-001 138 48 60
851-001 139 49 60
851-001 140 41 60
851-001 141 36 60
851-001 142 47 62
851-001 143 46 62
851-001 144 44 62
851-001 145 48 62
851-001 146 40 64
851-001 147 35 64
851-001 148 46 64
851-001 149 47 64
851-001 150  66
851-001 151 48 66
851-001 152 38 66
851-001 153 42 66
851-001 154  68
851-001 155 35 68
851-001 156  68
851-001 157  68
851-001 158  70
851-001 159 40
851-001 160 38
851-001 161 37
851-001 162 34
851-001 164 41
851-001 165 35
851-008 1 35
851-008 2 41 2
851-008 3 37 6
851-008 4 35 8
851-008 5 37 8
851-008 6 37 9
851-008 7 37 9
851-008 8 37 10
851-008 9 36 10
851-008 10 34 11
851-008 11 37 11
851-008 12 38 12
851-008 13 35 12
851-008 14 37 12
851-008 15 33 12
851-008 16 33 13
851-008 17 33 13
851-008 18 34 14
851-008 19 33 14
851-008 20 33 15
851-008 21 33 15
851-008 22 33 16
851-008 23 36 16
851-008 24 35 16
851-008 25 33 16
851-008 26 32 16
851-008 27 33 16
851-008 28 35 17
851-008 29 37 17
851-008 30 32 17
851-008 31 33 17
851-008 32 31 18
851-008 33 36
851-008 35 32
851-008 36 31
851-008 37 31
851-008 38 33
851-008 39 39
851-008 40 35
851-008 41 38
884-008 1 53
884-008 3 50
884-008 6 56
884-008 7 56
884-008 8 59
884-008 9 51
884-008 10 55
884-008 11 56
884-008 12 58
884-008 13 60
884-008 14 62
884-008 15 65
884-008 16 64
884-008 17 59
884-008 18 60
884-008 19 57
884-008 20 54
884-008 21 61
884-008 22 60
884-008 23 57
884-008 24 57
884-008 25 59
884-008 26 62
884-008 27 63
884-008 28 60
884-008 29 64
884-008 30 63
884-008 31 64
884-008 32 57
884-008 33 51
884-008 34 54
;
run;
proc sort;by id time1;run;

/*********************************************************************
  CHECK MISSING DATA VALUES
**********************************************************************/
proc means data=have min max N NMISS;
var time1 y1 y2;
BY ID;
run;

/*********************************************************************
                         PLOTTING 
1. **Need to  show x scale by ID, how to create dynamic Time1 scale-X Axis? 
2.** Need to use SOLID LINES to connect data, but if there is missing Y, then connect with DASHED LINE
**********************************************************************/
/*Plotting on same graph by ID*/
ods results off;
ods listing close;

ods rtf file = "C:\temp\plothave.rtf";		

proc sgplot data=have noautolegend;
   series x=time1 y=y1/break  markers ;
	 series x=time1 y=y2/break markers y2axis ;
	 by id;

  xaxis display=(nolabel)      					 label = "Time(Day)";
  yaxis grid  values = (5 to 100 by 10)          label="y1";
	y2axis values=       (0 to 100 by 10)	     label="y2";
	keylegend "y1 y2" / location=inside position=top; 
run; 

ods rtf close;
ods listing;
ods results on;

[PaigeMiller · Accepted solution]

Thanks for providing the data in a usable form.

When I run your code, I do not get the message about the tick values being thinned. Perhaps your or I have different default options set. Tick values are thinned if there are too many to fit on your axis. You can try something like this:

ods graphics / height=5in width=7in;

and then try different values for height= and width= to see if you can make the message go away.

As far as the WARNING

WARNING: Y=Y2 is invalid. The option expects that the column not contain all missing values.

Some of your BY variable levels have all missing values. This should be obvious if you either look at your data set with your own eyes, or look at the output plots, or run PROC FREQ on Y2 with by id;

[Rick_SAS]

To get a dashed line when the data are missing, use the trick explained in "A trick to plot a time series that has missing values,"

 which shows how to overlay two lines:

- a dashed line WITHOUT using the BREAK option

- a solid line that uses the BREAK option

To get an X axis that adjusts to the range of the data, add VALUESHINT to the XAXIS statement, which tells the plot to use the VALUES= option as a "hint," not as a mandate.

[purpleclothlady]

hi all @PaigeMiller @Rick_SAS @ballardw :

I updated the code and tested again, it perfectly worked.

EXCEPT 

1. log issue- how to work around the log issue :"WARNING: Y=Y2 is invalid. The option expects that the column
not contain all missing values."

2. the Legend is not displaying. 
3. since different ID had different  X Axis, how may I lable the minor ticker ? 

thanks again. this is a really helpful place to solve questions. 

Purple

/*********************************************************************
                         PLOTTING -- 
1. Need to  show x scale by ID, do has to use dynamic Time1
2. Need to use SOLID LINES to connect data, but if there is missing Y, then connect with DASHED LINE
**********************************************************************/
/*Plotting on same graph by ID*/

ods results off;
ods listing close;

ods rtf file = "C:\temp\plothave.rtf";		

ods graphics / height=5in width=8in;

proc sgplot data=have noautolegend;
   series x=time1 y=y1 /lineattrs=GraphData1(pattern=dash);       /*creating a dashed line of Y is missing*/
   series x=time1 y=y1/break  markers  lineattrs=GraphData1(pattern=solid thickness=2);

	 series x=time1 y=y2/break markers y2axis ;
	 by id;

  xaxis display=(nolabel)  VALUESHINT    	     label = "Time(Day)";
  yaxis grid  values = (5 to 100 by 10)          label="y1";
	y2axis values=       (0 to 100 by 10)	     label="y2";

	keylegend "y1 y2" / location=inside position=top; 	 /*where does this legend go? */
run; 


ods rtf close;
ods listing;
ods results on;

[PaigeMiller]

---

@purpleclothlady wrote:

hi all @PaigeMiller @Rick_SAS @ballardw :

I updated the code and tested again, it perfectly worked.

EXCEPT 

1. log issue- how to work around the log issue :"WARNING: Y=Y2 is invalid. The option expects that the column
not contain all missing values."

---

I explained #1 earlier. There is no workaround, since some of your IDs have all missing Y2.

[purpleclothlady]

Yes. I realized that. I will keep as this unless the data is updated and got some records. thank you again.

[Rick_SAS]

Fort the legend, put each name in a separate string:

keylegend "y1" "y2" / location=inside position=top; 

For adding minor ticks, add the MINORCOUNT= and MINOR options to the XAXIS statement:

 xaxis minor minorcount=1 display=(nolabel) VALUESHINT label = "Time(Day)";