where statement including two name formats in sgpanel

[palolix]

Dear SAS Community,

I want to make a stacked graph that compares two avocado varieties by Season but SAS is only reading the Hass variety but not the '464918_99' variety. Is there a way I can change the WHERE statement to solve this or do I have to format the name of this variety to a name instead of a number?

Thank you very much!

title "Ripe PeelColor: 464918_9 vs Hass by Season";
proc sgpanel data=one pctlevel=group;
where Season in(2021, 2022, 2024) and Variety in('464918_99', 'Hass');
panelby Season / columns=3 onepanel;
vbar Variety / group=PeelColor grouporder=ascending stat=percent groupdisplay=stack seglabel;
run;

[Tom]

What do you see when you run PROC FREQ on the SEASON*VARIETY variables? Are there any observations for HASS in those years?  How exactly is HASS spelled in the data?  

Does the VARIETY variable have a FORMAT attached to it? If so what format?

Remember that SAS is case sensitive.  And leading spaces are significant.  And non-space invisible characters, like TABs, are also significant.

[palolix]

Thanks for your reply Tom. In proc freq SAS is not reading '464918_99' either, only 'Hass'. 

[palolix]

I found out what  the problem was. Even though I entered the var level as 464918_99, SAS was reading it as 464918_9 so when I used 464918_9 instead of 464918_99 in the WHERE statement it worked. 

Thanks for your guidance Tom.

[Tom · Accepted solution]

SAS uses FIXED length character variables.  If you try to store more bytes that the length defined for the variable then it will be truncated.

464918_99

is 9 bytes long. 

Since 8 is the default length for a variable (both numeric and character) most likely you forgot to either DEFINE the variable length using a LENGTH statement before referencing the variable in some other statement (like an INPUT statement for example).  

data 
  length Variety $9 ;
  infile .... ;
  input ... Variety  .... ;
run;

When you first use a variable in some other statement then SAS will GUESS what length to based on the context.

Another possibility is you used column mode input and used too few columns.  Or perhaps the wrong columns so that you read some leading space and then only the first 8 bytes of the value.

Another thing to watch out for is a missmatch between the storage length and the display format being used.   If you attach the $8. format to variable defined to have a length of 9 then when printed the value will appear truncated.  (The opposite problem from what you describe.)

[palolix]

Thank you so much Tom for your great advice! You are right, I specified the length to $9 and it worked! That was all the issue

[Cynthia_sas]

Hi:

  A character string should be OK whether it is all numbers or a mix of numbers and characters or just all alpha letters. What might make a difference is leading blanks or unprintable characters or a hyphen - in the value instead of an underscore _.

  These are the results from my test:

  I made some fake data from SASHELP.CLASS and just made up fake values for PeelColor. I made data for 4 seasons, but one of the seasons didn't pass the WHERE and I had one season where the varieties didn't pass the WHERE, so the resulting SGPANEL only had 2 Seasons but did correctly show the value 464918_99 as a character value.

  Here's the data and code I tested with. 

Cynthia

** Make some fake data using SASHELP.CLASS;
** Data for 4 season values;
** One Season is not wanted in the WHERE;
** One Season has bad varieties;
** final SGPANEL should only show 2 seasons;
** if WHERE is working correctly;
data one;
length variety $15 PeelColor $4;
set sashelp.class;
** final data should be good for 2021 and 2024 with WHERE;
** Seasons 2022 and 2023 should not pass WHERE;
Season = 2021;
if sex = 'M' then variety = '464918_99';
else if sex = 'F' then variety = 'Hass';
if age le 13 then PeelColor = 'Lite';
else if age le 16 then PeelColor = 'Dark';
output;

** SEASON 2022 is OK, but both Varieties are not OK;
Season = 2022;
if sex = 'M' then variety = 'Unknown';
else if sex = 'F' then variety = '589333_44';
if age le 14 then PeelColor = 'Lite';
else if age le 16 then PeelColor = 'Dark';
output;

** Season 2023 should fail where because SEASON is wrong;
Season = 2023;
if sex = 'M' then variety = '464918_99';
else if sex = 'F' then variety = 'Hass';
if age le 14 then PeelColor = 'Lite';
else if age le 16 then PeelColor = 'Dark';
output;

Season = 2024;
if sex = 'M' then variety = '464918_99';
else if sex = 'F' then variety = 'Hass';
if age le 12 then PeelColor = 'Lite';
else if age le 16 then PeelColor = 'Dark';
output;
run;

title "Ripe PeelColor: 464918_9 vs Hass by Season";
proc sgpanel data=one pctlevel=group;
where Season in(2021, 2022, 2024) and Variety in('464918_99', 'Hass');
panelby Season / columns=2 onepanel;
vbar Variety / group=PeelColor grouporder=ascending stat=percent groupdisplay=stack seglabel;
run;
title;

[palolix]

Thank you very much for your help Cynthia. For some reason, SAS  is not reading '464918_99' in the WHERE statement.

[Cynthia_sas]

Hi:

  That is not what I observed. Did you run my test program using my fake test data? With the test data and nearly the same program (columns=2 instead of columns=3) that specific character string worked in the WHERE statement in my test program. If the test program works on my test data, but the WHERE does not work on your real data, then I would suspect some issue with the way the values are stored in the data set....either unprintable spaces, two underscores or two hyphens instead of 1, leading spaces....something is off with the data.

  If you run my test program with MY fake test data, and the WHERE in the SGPANEL does not create the graph that I pasted, then, that is an issue you need to address with Tech Support.

  My test data and test program proves that the string '464918_99' can be used in a WHERE statement and the condition works correctly, when '464918_99' is in the data.

   You have not provided any sample or representative data or shown a SAS log which should have something like THIS after the SGPANEL step that shows EXACTLY how many observations were selected based on the WHERE:

 You keep saying that the character string is not being 'read' by the WHERE statement, which is clearly NOT the case in my test program. Just saying over and over that the value is not being read doesn't help anyone make constructive suggestions because you haven't said whether Variety is a character or numeric variable in your data; you haven't said whether Variety has a user-defined format in use; you haven't shown any results or log messages that illustrate your assertion that the value is not being read or used by the WHERE statement. On the other hand, several community members have made suggestions and provided code and asked questions that all point to other things you need to check before you can really say that the WHERE statement is not 'reading' the value '464918_99'.

  So, did you run my ENTIRE test program and what EXACTLY did the log show and what EXACTLY were the results?

Cynthia