BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
rnmishra
Calcite | Level 5

The data set 'Have' has hundreds of coumns. The column names neither have common prefix nor  a suffix.

I want to extract column 1 throgh 20  and 60 through 76.

Is there a quicker way to accomplish this, without writing all the variable names in select statement?

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Anotherdream
Quartz | Level 8

Heya. Yes there is. First take a proc contents on your dataset, then sort the data by varnum, then only select 1-20, and 60-76, and finally select these columns into a macro variable. The code below will accomplish what you need.

proc contents data=have out=keep_names(keep=name varnuM);

run;

proc sort data=keep_names;

by varnum;

run;

data only_specific_names;

set keep_names;

where varnum between 1 and 20 or varnum between 60 and 76;

run;

proc sql;

select name into :mynames separated by ', '

from only_specific_names;

run;

proc sql;

create table Specific_names as

select &mynames

from have;

quit;

run;

Let me know if you have any questions on this, or if it does not work and get you what you wanted!

View solution in original post

8 REPLIES 8
Anotherdream
Quartz | Level 8

Heya. Yes there is. First take a proc contents on your dataset, then sort the data by varnum, then only select 1-20, and 60-76, and finally select these columns into a macro variable. The code below will accomplish what you need.

proc contents data=have out=keep_names(keep=name varnuM);

run;

proc sort data=keep_names;

by varnum;

run;

data only_specific_names;

set keep_names;

where varnum between 1 and 20 or varnum between 60 and 76;

run;

proc sql;

select name into :mynames separated by ', '

from only_specific_names;

run;

proc sql;

create table Specific_names as

select &mynames

from have;

quit;

run;

Let me know if you have any questions on this, or if it does not work and get you what you wanted!

rnmishra
Calcite | Level 5

Thanks!!! Works great!

Anotherdream
Quartz | Level 8

No problem!

If anyone knows the 'prettier' and more efficient method of doing this with Macro variables, I would also like to see this posted.

Always nice to become more efficient!

Brandon

Haikuo
Onyx | Level 15

I can't say this is 'prettier or more efficient', other than another approach:

proc sql;

select name into :names separated by ' ' from dictionary.columns

  where LIBNAME='WORK'

  AND MEMNAME='HAVE'

AND (1<=VARNUM<=20 OR 60<=VARNUM<=76);

QUIT;

DATA WANT;

  SET HAVE (keep=&names);

run;

Haikuo

UPdate: never forget the parentheses.

rnmishra
Calcite | Level 5

Hi Reeza and Hai Kuo,

Can you tell me  what ' from dictionary.columns'  means?

Thanks!

Reeza
Super User

SAS basically keeps information on your tables, ie columns, libname, variable names, formats, size, order etc. You can query it to simply documentation and other tasks.

Go open it and take a look, its found under SASHELP.VCOLUMNS

There's also SASHELP.VTABLE that's useful.

Reeza
Super User

Another option:

You can also use the shorthand notation of --. This is mostly useful if you already know the variables in those positions.

proc sql;

    select name into :vname1-:vname4

    from dictionary.columns

    where libname='MYLIB' and memname='MYDATASET'

    and varnum in (1, 20, 60,76);

quit;

data want;

    set have;

    keep &vname1--&vname2 &vname3--&vname4;

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
  • 8 replies
  • 10476 views
  • 6 likes
  • 4 in conversation