topic Re: Removing a part of column name in SAS Programming

Removing a part of column name

akhilesh_joshi — Thu, 03 Nov 2016 18:24:16 GMT

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?

Re: Removing a part of column name

data_null__ — Thu, 03 Nov 2016 18:30:58 GMT

why not just say

xyz6-xyz16

Re: Removing a part of column name

akhilesh_joshi — Thu, 03 Nov 2016 19:03:28 GMT

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.

Re: Removing a part of column name

data_null__ — Thu, 03 Nov 2016 19:08:16 GMT

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

Re: Removing a part of column name

akhilesh_joshi — Thu, 03 Nov 2016 19:23:56 GMT

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

Re: Removing a part of column name

data_null__ — Thu, 03 Nov 2016 19:31:09 GMT

Extract the number using

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

Re: Removing a part of column name

Shmuel — Thu, 03 Nov 2016 19:37:12 GMT

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;

Re: Removing a part of column name

ballardw — Thu, 03 Nov 2016 19:50:19 GMT

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.

Re: Removing a part of column name

akhilesh_joshi — Thu, 03 Nov 2016 20:00:13 GMT

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.

Re: Removing a part of column name

Reeza — Thu, 03 Nov 2016 20:54:12 GMT

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

Re: Removing a part of column name

Patrick — Sun, 06 Nov 2016 01:29:19 GMT

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