Proc sql: Selecting a range of columns-Column reference ?

[rnmishra]

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.

[Anotherdream · Accepted solution]

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]

Thanks!!! Works great!

[Anotherdream]

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]

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]

Hi Reeza and Hai Kuo,

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

Thanks!

[Reeza]

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.

[Haikuo]

Here is a paper hopefully can get you started:

http://www2.sas.com/proceedings/sugi31/259-31.pdf

Haikuo

[Reeza]

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;