Adding all rows with a prefix to proc sql

[JenniferColeDH]

I have a set of data that I transposed so that now all instances of results are in columns result_1, result_2, etc.  Now I need to do additional steps which include PROC SQL and I'm looking for a way to say *add all columns that start with result_*  The goal is to automate this program but I won't know how many instances of "result_" their will be for any given time period, as far as I'm aware their is no maximum.

PROC SQL;
CREATE TABLE test AS
SELECT
     T1.NAME,
     T1.DATE,
     T2.(ALL COLUMNS THAT START WITH RESULT)
FROM
     LIST1 AS T1
     INNER JOIN LIST2 AS T2
          ON T1.UID = T2.UID;
QUIT;

[Kurt_Bremser]

This is one of the limitations of SQL; you can only address columns specifically with their full name.

Make your selections with a WHERE condition before you transpose, if you need to do that (transpose) at all.

BIG HINT: long datasets are always better to work with than wide datasets. Write that 10.000 times, or until the message has sunk in.

[FreelanceReinh]

Hi @JenniferColeDH,

In some cases, including the example you've shown, KEEP= and DROP= dataset options (where name prefix lists are allowed) in conjunction with the asterisk notation can overcome that limitation of (PROC) SQL:

PROC SQL;
CREATE TABLE test(drop=uid) AS
SELECT
     T1.NAME,
     T1.DATE,
     T2.*
FROM
     LIST1 AS T1
     INNER JOIN LIST2(keep=uid result:) AS T2
          ON T1.UID = T2.UID;
QUIT;