topic Re: How would I write this loop with proc sql to select car1, car2, car3 up to car10 from table A? in SAS Programming

How would I write this loop with proc sql to select car1, car2, car3 up to car10 from table A?

NRichmond — Thu, 04 Jun 2020 03:13:21 GMT

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.)

Re: How would I write this loop with proc sql to select car1, car2, car3 up to car10 from table A?

ChrisNZ — Thu, 04 Jun 2020 04:39:49 GMT

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 🙂

Re: How would I write this loop with proc sql to select car1, car2, car3 up to car10 from table A?

ballardw — Thu, 04 Jun 2020 07:36:34 GMT

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.

Re: How would I write this loop with proc sql to select car1, car2, car3 up to car10 from table A?

FreelanceReinh — Thu, 04 Jun 2020 08:35:45 GMT

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.