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

Accepted Solution Solved
Reply
Contributor
Posts: 66
Accepted Solution

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

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.


Accepted Solutions
Solution
‎12-31-2012 12:25 PM
Super Contributor
Posts: 418

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

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


All Replies
Solution
‎12-31-2012 12:25 PM
Super Contributor
Posts: 418

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

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!

Contributor
Posts: 66

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

Posted in reply to Anotherdream

Thanks!!! Works great!

Super Contributor
Posts: 418

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

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

Respected Advisor
Posts: 3,156

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

Posted in reply to Anotherdream

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.

Contributor
Posts: 66

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

Hi Reeza and Hai Kuo,

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

Thanks!

Super User
Posts: 19,822

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

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.

Respected Advisor
Posts: 3,156

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

Here is a paper hopefully can get you started:

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

Haikuo

Super User
Posts: 19,822

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

Posted in reply to Anotherdream

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 465 views
  • 6 likes
  • 4 in conversation