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
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)
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)
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.