Hello,
First time posting! I have some sql code where I Select multiple columns with the same name except the final digit in them. I'd like to clean up the code and cannot figure out the loop to write for this. The &IN_SCOPE_CARS is a list of cars that I want to only include in the table. I also searched and could not seem to find a solution for when all the columns names are what is being looped through and the table was staying the same.
PROC SQL;
CREATE TABLE WORK.TABLE AS
SELECT
CAR1
,CAR2
,CAR3
.....
,CAR10
FROM WORK.DATASET
WHERE
CAR1 IN (&IN_SCOPE_CARS.) OR
CAR2 IN (&IN_SCOPE_CARS.) OR
CAR3 IN (&IN_SCOPE_CARS.) OR
.....
CAR10 IN (&IN_SCOPE_CARS.)
I don't see how you could make this code much better.
You only have 20 lines of code, and any attempt at some clever loop will not make the code much more compact, while it will certainly make the code much harder to read.
Sometimes no action is the best action 🙂
Pretty much as soon as you say "loop" you are out of the SQL world.
One data set approach would be:
data work.table; set work.dataset; array c car1-car10; do i= 1 to dim(c); if c[i] in (&IN_SCOPE_CARS.) then do; output; leave; end; end; keep car1-car10;
run;
where you have a small code advantage that you can use arrays to loop over variable values and shorthand lists to reference similarly named (in this instance anyway) variables.
The only tricky parts are inside the loop where we have an explicit output to send the current values to the output data set and the special instruction LEAVE which in this case will stop the loop as soon as one match is found. I would go this way if you start having to deal with more Car variables just for the ability to use lists. Change the car10 in two places to car100 and you can deal with 100 variables and values. SQL you get to list another 90 variables in the Select and another 90 OR clauses in the Where coding.
Hello @NRichmond (and welcome to the SAS Support Communities :-)),
The first part of the SELECT statement could be abbreviated by using a variable list in a KEEP= dataset option:
select * from work.dataset(keep=car1-car10);
or (under certain conditions)
select * from dataset(keep=car:);
I would also consider transposing (relevant parts of) work.dataset from wide to long (with a single variable car), which would allow for easy subsetting in PROC SQL like
having max(car in (&in_scope_cars));
after a suitable GROUP BY clause.
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.