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,
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;
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,
@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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.