Removing a part of column name

Reply
Contributor
Posts: 40

Removing a part of column name

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?

Respected Advisor
Posts: 3,775

Re: Removing a part of column name

why not just say

 

xyz6-xyz16
Contributor
Posts: 40

Re: Removing a part of column name

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. 

Respected Advisor
Posts: 3,775

Re: Removing a part of column name

 

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

 

Contributor
Posts: 40

Re: Removing a part of column name

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

Respected Advisor
Posts: 3,775

Re: Removing a part of column name

Extract the number using

 

Input(Scan(column,-1,'_'),f12.);
Super User
Posts: 1,228

Re: Removing a part of column name

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;

Grand Advisor
Posts: 10,210

Re: Removing a part of column name

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.

Contributor
Posts: 40

Re: Removing a part of column name

[ Edited ]

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. 

Grand Advisor
Posts: 17,325

Re: Removing a part of column name

@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.

Respected Advisor
Posts: 3,825

Re: Removing a part of column name

[ Edited ]

@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;

 

Ask a Question
Discussion stats
  • 10 replies
  • 337 views
  • 3 likes
  • 6 in conversation