topic Re: proc sql; select short spelling when variables have numeric suffixes in New SAS User

proc sql; select short spelling when variables have numeric suffixes

Harkonnen — Wed, 03 Feb 2021 09:54:06 GMT

Hello! I used proc sql; in my code and I was wondering how can I do a short spelling of this?

In a data step, it will be v1-v11, but what syntaxis will be correct for SQL?

Thank you in advance!

Re: proc sql; select short spelling when variables have numeric suffixes

PeterClemmensen — Wed, 03 Feb 2021 09:58:56 GMT

PROC SQL does not handle variable lists. You can do this with a macro or through meta data, but it will not be shorter.

Re: proc sql; select short spelling when variables have numeric suffixes

FreelanceReinh — Wed, 03 Feb 2021 13:45:07 GMT

Hello @Harkonnen,

In PROC SQL you can use "select *" in conjunction with KEEP= and DROP= dataset options, which support variable lists. However, you would need to check on a case-by-case basis whether an intended query can be simplified by this approach. Here's an example:

/* Create test data for demonstration */

data tab_aval_rdy;
array a[*] or_p or_v or_s v1-v20 (1:23);
output;
call stdize(of a[*]);
output;
run;

data tab_chg_rdy;
array b[*] or_p or_v or_s x y z (1:6);
output;
output;
run;

/* Use "*" and variable lists in dataset options in the query */

proc sql;
create table full_tab(keep=v:) as
select a.*
from tab_aval_rdy(keep=v1-v11 or:) a left join tab_chg_rdy b
on a.or_p=b.or_p and a.or_v=b.or_v and a.or_s=b.or_s;
quit;

The same result would be obtained with the code shown in your screenshot. In this example the dataset option keep=v1-v11 or: could also be replaced by drop=v12-v20.