11-03-2016 02:24 PM
One of column names in table is in format xyz_1 to xyz_16. I am trying to include only columns xyz_6 to xyz_16 by using:
case when substr('columnname', 1, length(columnname)-1) <= 16 and substr('columnname', 1, length(columname)-1) => 6
then columnname end as columnname. But its not working.
Can someone point out what error I am making?
11-03-2016 03:37 PM
what do you mean by include columns ?
Did you mean KEEP on output ? - if so then code: KEEP xyz_6 - xyz_16
KEEP xyz_6 - xyz_16;
11-03-2016 03:50 PM
Provide examples of the actual data involved. It seems that the story changes each time you get a suggestion. Not a description actual rows of data pasted as text.
11-03-2016 03:52 PM - edited 11-03-2016 04:00 PM
It was my mistake. I used the word column instead of row. My column name is xyz. and it has values xyz_1 to xyz_16. so I want rows which only have values xyz_6 to xyz_16.
11-03-2016 04:54 PM
11-05-2016 09:23 PM - edited 11-05-2016 09:29 PM
That's why it's so important that you post sample data and then explain us how the desired result needs to look like.
Here you go:
data have; length xyz $32; do xyz='xyz_1','xYz_2','xyz_16','xyz_17','abc_1','xyz','xyz_1_abc','xyz_abc_1','xyz'; output; end; run; data want; set have; if upcase(scan(xyz,1,'_'))='XYZ' and ( 1 <= input(substrn(xyz,findc(xyz,'_')+1),?? f12.) <= 16 ); ; run;