topic Adding all rows with a prefix to proc sql in SAS Programming

Adding all rows with a prefix to proc sql

JenniferColeDH — Fri, 11 Sep 2020 19:46:40 GMT

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;

Re: Adding all rows with a prefix to proc sql

Kurt_Bremser — Fri, 11 Sep 2020 20:55:18 GMT

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.

Re: Adding all rows with a prefix to proc sql

FreelanceReinh — Fri, 11 Sep 2020 22:09:36 GMT

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;