BookmarkSubscribeRSS Feed
akhilesh_joshi
Fluorite | Level 6

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?

10 REPLIES 10
data_null__
Jade | Level 19

why not just say

 

xyz6-xyz16
akhilesh_joshi
Fluorite | Level 6

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__
Jade | Level 19

 

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

 

akhilesh_joshi
Fluorite | Level 6

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

data_null__
Jade | Level 19

Extract the number using

 

Input(Scan(column,-1,'_'),f12.);
Shmuel
Garnet | Level 18

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
Super User

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
Fluorite | Level 6

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
Super User

@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
Opal | Level 21

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1339 views
  • 3 likes
  • 6 in conversation