SAS sql whichn

[eramirez]

Hi all,

I have this code:

proc sql;
create table epi_flu_pneumonia_deaths2 as
select week_end, mmwr_week, season, flu_deaths, pneumonia_deaths, all_deaths , season_num from epi_flu_pneumonia_deaths
order by whichc(season, '2009-2010','2010-2011','2011-2012','2012-2013','2013-2014','2014-2015','2015-2016','2016-2017','2017-2018','2018-2019','2019-2020','2020-2021','2021-2022'),
whichn(mmwr_week, 40,41,42,43,44,45,46,47,48,49,50,51,52,53,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39);
quit;

and I keep getting this note: NOTE: The query as specified involves ordering by an item that doesn't appear in its SELECT clause.

and the resulting table is only sorted by the first whichc, season.

am I missing something?

Thanks

Enrique 

[Tom · Accepted solution]

No idea what you are trying to do, but to get rid of the NOTE include the variables in the SELECT list.

Plus then you can debug why it does not appear to be sorting.

create table epi_flu_pneumonia_deaths2 as
select
  whichc(season, '2009-2010','2010-2011','2011-2012','2012-2013','2013-2014'
         ,'2014-2015','2015-2016','2016-2017','2017-2018','2018-2019'
         ,'2019-2020','2020-2021','2021-2022') as sort1
, whichn(mmwr_week, 40,41,42,43,44,45,46,47,48,49,50,51,52,53
         ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20
         ,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39) as sort2
, week_end
, mmwr_week
, season
, flu_deaths
, pneumonia_deaths
, all_deaths 
, season_num 
from epi_flu_pneumonia_deaths
order by 1,2
;
quit;

You can always add a DROP= dataset option to remove the extra variables.

epi_flu_pneumonia_deaths2(drop=sort1 sort2)

[eramirez]

Thanks Tom, this worked as well. I realized what the issue was, the original code was ordering the table correctly, but it seemed like there missing weeks for one of the seasons so maybe that is why I was getting the note?  In any case, I learned two ways to sort it.  appreciate it. ER. 

[ballardw]

The note is just that, a note and is basically a reminder to you that the appearance of your output may not appear to be ordered as expected because you did not include the value(s) in the output. That is a standard note and has nothing to do with your output order.

I would suggest actually adding the results of those WhichC and WhichN calculations as variables and use order by those calculated variables.

Or possibly create an actual date value and sort by that.

Without actually seeing some input values and what you expect the order for the output just guessing.

[eramirez]

thank you! I realized that some weeks in one of seasons were missing so the order was not going to appear the way I expected. Otherwise, the custom order worked. appreciate it. ER