英文:
How can I retrieve a list of table names and arrange them in ascending order based on their dates?
问题
以下是您要翻译的代码部分：
proc sql;
select memname
from dictionary.tables
where libname = 'KALP51' and memname like 'BRANCH_AQB_INDIA_%_MAIN'
order by memname asc;
quit;
英文:
I have some rows like this
BRANCH_AQB_INDIA_NOV2022_MAIN
BRANCH_AQB_INDIA_FEB2023_MAIN
BRANCH_AQB_INDIA_SEP2023_MAIN
BRANCH_AQB_INDIA_AUG2022_MAIN
BRANCH_AQB_INDIA_APR2023_MAIN
BRANCH_AQB_INDIA_JAN2024_MAIN
i want to sort them by asc like this
BRANCH_AQB_INDIA_AUG2022_MAIN
BRANCH_AQB_INDIA_NOV2022_MAIN
BRANCH_AQB_INDIA_FEB2023_MAIN
BRANCH_AQB_INDIA_APR2023_MAIN
BRANCH_AQB_INDIA_SEP2023_MAIN
BRANCH_AQB_INDIA_JAN2024_MAIN
Currently I'm just getting all the list with asc order like this in SAS
`
proc sql;
select memname
from dictionary.tables
where libname = 'KALP51' and memname like 'BRANCH_AQB_INDIA_%_MAIN'
order by memname asc;
quit;`
答案1
得分: 2
从memname中提取日期字符串，将其转换为SAS日期，并按该值排序。您可以使用scan获取MONYY7.格式中倒数第二个日期字符串，然后使用input将其转换为SAS日期。使用outobs=1选项仅获取列表中的第一行。
proc sql outobs=1;
select memname
from dictionary.tables
where libname = 'KALP51' and memname like 'BRANCH_AQB_INDIA_%_MAIN'
order by input(scan(memname, -2, '_'), monyy7.);
quit;
memname
BRANCH_AQB_INDIA_AUG2022_MAIN
英文:
Extract the date string from memname, convert it to a SAS date, and order by that value. You can do this with scan to get the second-to-last date string in MONYY7. format, then use input to convert it to a SAS date. Use the outobs=1 option to only get the first row in the list.
proc sql outobs=1;
select memname
from dictionary.tables
where libname = 'KALP51' and memname like 'BRANCH_AQB_INDIA_%_MAIN'
order by input(scan(memname, -2, '_'), monyy7.);
quit;`
memname
BRANCH_AQB_INDIA_AUG2022_MAIN
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论