topic Re: select multiple variables with same naming convention in SAS Programming

select multiple variables with same naming convention

runrunbunny — Mon, 18 Jan 2021 19:08:16 GMT

Hi,

I would like to select 10 columns(var1 to var10) from a table:

Proc sql;

select var1, var2, var3, var4, var5,var6,var7,var8,var9,var10

from table;

quit;

Is there an convenient way to represent these variables instead of manually listing them?

Thanks a lot!

runrunbunny

Re: select multiple variables with same naming convention

novinosrin — Mon, 18 Jan 2021 19:11:32 GMT

Hi @runrunbunny I'm afraid Proc SQL does not support convenient variable lists like that of a datastep. You may have to switch to datastep or read dictionary information aka proc contents aka metadata and assign the variable lists in a macro variable and call the macro variable in SQL.

Re: select multiple variables with same naming convention

Tom — Mon, 18 Jan 2021 19:12:04 GMT

Yes. But not with SQL.

The SAS equivalent of your SQL code is:

proc print data=table ;
  var var1-var10 ;
run;

Re: select multiple variables with same naming convention

runrunbunny — Mon, 18 Jan 2021 19:13:39 GMT

Thank you so much!

Re: select multiple variables with same naming convention

FreelanceReinh — Mon, 18 Jan 2021 19:16:33 GMT

Hi @runrunbunny,

In your particular example you could use a dataset option:

proc sql;
select *
from table(keep=var1-var10);
quit;

Re: select multiple variables with same naming convention

runrunbunny — Mon, 18 Jan 2021 19:20:04 GMT

Good point! Thank you so much!

Re: select multiple variables with same naming convention

ChrisNZ — Mon, 18 Jan 2021 19:21:09 GMT

SQL doesn't not allow for any shortcut except for the *.

You can use non-SQL options from the SAS language such as

select * from TABLE(keep=VAR1-VAR10);