Removing a part of column name

[akhilesh_joshi]

Hi all,

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?

[data_null__]

why not just say

xyz6-xyz16

[akhilesh_joshi]

Hi,

This column is in another table and I am trying to create a new table where in I want columns only from x_6 to x_16. 

[data_null__]

data new;
   set old(keep=xyz6-xyz16);
   run;

[akhilesh_joshi]

The name of column is xyz and it has values xyz_1 to xyz_16.

[data_null__]

Extract the number using

Input(Scan(column,-1,'_'),f12.);

[Shmuel]

what do you mean by include columns ?

Did you mean KEEP on output ?  - if so then code: KEEP xyz_6 - xyz_16

like:

data want;

  set have;

       KEEP xyz_6 - xyz_16;

run;

[ballardw]

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.

[akhilesh_joshi]

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. 

[Reeza]

@akhilesh_joshi Then @data_null__ solution is the correct solution. Use the function to extract the number and then filter on it using an IF statement.

[Patrick]

@akhilesh_joshi

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;