topic Re: Proc sql: Selecting a range of columns-Column reference ? in SAS Programming

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

rnmishra — Mon, 31 Dec 2012 17:18:17 GMT

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.

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

Anotherdream — Mon, 31 Dec 2012 17:25:23 GMT

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!

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

rnmishra — Mon, 31 Dec 2012 17:48:33 GMT

Thanks!!! Works great!

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

Anotherdream — Mon, 31 Dec 2012 17:51:38 GMT

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

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

Haikuo — Mon, 31 Dec 2012 18:11:15 GMT

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.

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

Reeza — Mon, 31 Dec 2012 18:19:59 GMT

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;

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

rnmishra — Mon, 31 Dec 2012 18:28:09 GMT

Hi Reeza and Hai Kuo,

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

Thanks!

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

Reeza — Mon, 31 Dec 2012 18:40:17 GMT

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.

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

Haikuo — Mon, 31 Dec 2012 18:40:33 GMT

Here is a paper hopefully can get you started:

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

Haikuo