topic Re: Where statement with double digits in SAS Procedures

Where statement with double digits

GavinB — Wed, 21 Mar 2018 13:02:49 GMT

After running a mixed model on 16 different treatments, where I also compare among the treatments I save the differences in a new file. With this new file I run a proc sgplot to create a difference plot showing the differences with respective confidence limit. In order for me to make this readable I put in a where between and statement to have it select all the differences for the comparisons between treatment 1vs2 till 1vs16. For some reason it fails to show what I want it to show when the second part contains double digits. To show what happens:

proc sgplot data=differences noautolegend;
where comparison between '1vs2' and'1vs9';

-->gives me the plot showing all comparisons between 1vs2 till 1vs9

proc sgplot data=differences noautolegend;
where comparison between '1vs2' and'1vs16';

-->only gives me the differences for 1vs2 and 1vs16 without any in between

It has to do with the second 1vs statement having double digits, because as soon as I change that it messes up the graph. It has to do with the way SAS reads this comparison, but does anyone have a solution for this??

Thanks in advance,
Gavin

Re: Where statement with double digits

ballardw — Wed, 21 Mar 2018 14:26:25 GMT

Interval comparisons with character values do not use the numeric values for comparison. Since 2>1 in character values then '1vs2' is greater than '1vs16'. Since you state 16 treatments I am assuming that you have none such as '1vs18' that might have been included.

Please see for an example of sort order which is used in "between" comparisons:

data junk;
   length str $ 5;
   do i=1 to 16;
      str=cats('1vs',i);
      output;
   end;
run;

proc sort data=junk;
   by str;
run;

proc print data=junk;
   var str;
run;

If you want that kind of name to compare properly with "between" you would have to have values for comparison like '1vs02', not '1vs2'.

Or list the exact values you want in the where statement.

Re: Where statement with double digits

mkeintz — Wed, 21 Mar 2018 15:49:49 GMT

That's because the comparison of '1vs1' and '1vs16' is using "lexicographic ordering". And since your lower-order versions are vs1, vs2, ... vs9 instead of 'vs01','vs02',... 'vs09', you don't have the option of where comparison between '1vs01' and '1vs16'

Try

where (comparison between '1vs2' and '1vs9') or (comparison between '1vs10' and '1vs16')