topic Re: using array in dynamic query in SAS Programming

using array in dynamic query

JohnnySas — Thu, 24 Jan 2019 03:39:28 GMT

I just completed a proc transpose.

proc transpose data=WORK.SIGN1 out=WORK.SIGN2 prefix=E_;
by CLIENT_XID;
id SQID;
var EMAIL;
run;

The E_ = E1, E2, E3, E4, E.....(nth)

Question: How do I use an array in the next data step to:

1. dynamically select all the E1,E2, E3, E....(nth) from WORK.SIGN2

Thank You

Re: using array in dynamic query

ChrisNZ — Thu, 24 Jan 2019 03:58:26 GMT

What do you want to do?

Like this?

sum( of E_: )

Re: using array in dynamic query

Reeza — Thu, 24 Jan 2019 04:18:41 GMT

Here are a few ways.

https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html

@JohnnySas wrote:

I just completed a proc transpose.

proc transpose data=WORK.SIGN1 out=WORK.SIGN2 prefix=E_;
by CLIENT_XID;
id SQID;
var EMAIL;
run;

The E_ = E1, E2, E3, E4, E.....(nth)

Question: How do I use an array in the next data step to:

1. dynamically select all the E1,E2, E3, E....(nth) from WORK.SIGN2

Thank You

Re: using array in dynamic query

Astounding — Thu, 24 Jan 2019 14:04:43 GMT

The next step can identify all variable names that begin with E_ into an array in this way:

data want;

set sign2;

array emails {*} E_: ;

*** add logic here if desired ;

run;

Re: using array in dynamic query

JohnnySas — Thu, 24 Jan 2019 15:06:03 GMT

Thank You for the reply.

All i want is:

do a dynamic creation of a select from the result of the proc transpose.

select

E_1, E_2,E_3, E,....(nth)

from ....;

Re: using array in dynamic query

Reeza — Thu, 24 Jan 2019 16:00:55 GMT

SQL does not support variable lists. A data step does, so using a data step is a better option.

Re: using array in dynamic query

ChrisNZ — Thu, 24 Jan 2019 20:26:28 GMT

select

t1.*

....

from TABLE ( keep= E_: .... ) t1

....

;