Selecting multiple variables with a numbered suffix

[x2PSx]

Hello everyone,

I am trying to select multiple variables with the same prefix but a different numbered suffix in an SQL procedure within SAS Base. I should mention that I need this because the number of variables may change over time.

For example I want this output:

proc sql;
create table WANT as
select VARA1, VARA2, VARA3, VARA4, VARA5, VARB1, VARB2, VARB3, VARB4, VARB5
from HAVE;
quit;

I would like my code to be structured like this to select VARA1 to VARA5 and VARB1 to VARB5, is that possible?:

proc sql;
create table WANT as
select VARA[*], VARB[*] 
from HAVE;
quit;

Thank you so much,

[Reeza]

Unfortunately not in SQL without macro code. This can be easily accomplished in a SAS data step.

https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html

data want;
set have;

keep vara1-vara5 varb1-varb5;

*keep vara: varb:;

*keep vara1--varb5;

run;

---

@x2PSx wrote:

Hello everyone,

I am trying to select multiple variables with the same prefix but a different numbered suffix in an SQL procedure within SAS Base. I should mention that I need this because the number of variables may change over time.

For example I want this output:

proc sql;
create table WANT as
select VARA1, VARA2, VARA3, VARA4, VARA5, VARB1, VARB2, VARB3, VARB4, VARB5
from HAVE;
quit;

I would like my code to be structured like this to select VARA1 to VARA5 and VARB1 to VARB5, is that possible?:

proc sql;
create table WANT as
select VARA[*], VARB[*] 
from HAVE;
quit;

Thank you so much,

---

[ballardw]

---

@x2PSx wrote:

Hello everyone,

I am trying to select multiple variables with the same prefix but a different numbered suffix in an SQL procedure within SAS Base. I should mention that I need this because the number of variables may change over time.

For example I want this output:

proc sql;
create table WANT as
select VARA1, VARA2, VARA3, VARA4, VARA5, VARB1, VARB2, VARB3, VARB4, VARB5
from HAVE;
quit;

I would like my code to be structured like this to select VARA1 to VARA5 and VARB1 to VARB5, is that possible?:

proc sql;
create table WANT as
select VARA[*], VARB[*] 
from HAVE;
quit;

Thank you so much,

---

If your VARA VARB are pairs of data , such as purchase/ sale, count/percent or similar taken at different times or places then you might be better off normalizing the data such that you have another variable to indicate the time period or place or whatever that is being hidden in the VARA and VARB suffix and on one record per pair. When that is done then you do not need to keep adjusting code to select or following variables by name. Instead select values of the identification variable and likely BY group processing or use of the identification variable simplifies other code.

[Tom · Accepted solution]

Use KEEP= dataset option instead. Then you can use variable lists.  You will not have control over the order of the variables however, they will keep their relative order from the source dataset.

proc sql;
create table WANT as
  select *
  from HAVE(keep=vara1-vara5 varb1-varb5)
;
quit;