proc sql select multiple variables with same words

[km0927]

Hello. I have a problem with selecting multiple variable in proc sql.

My data's variables are as follows....

ID pre_stress_visit1  pre_stress_visit2 pre_stress_visit3 post_stress_visit1 post_stress_visit2 post_stress_visit3 pre_pain_visit1

A 12 10 9 10 8 7 53 ...

B 13 13 6 11 11 4 57 ...

C 13 12 8 10 13 7 55 ...

I want to select variables with same words, 'stress'. So, I want to select.(Maybe you can find that i want to calculate difference between pre_post stress in every visit)

"ID

pre_stress_visit1

pre_stress_visit2

pre_stress_visit3

post_stress_visit1

post_stress_visit2

post_stress_visit3"

I tried to use 'array' function, but it didn't work in proc sql. Is there a better way?

I would really appreciate all your help. thanks.

[SASKiwi · Accepted solution]

Why do you need to use SQL? It's a lot easier in a DATA step:

data want;
  set have (keep = ID pre_stress_visit: post_stress_visit:);
run;

If you still want to stick with SQL:

proc sql;
  create table want as 
  select *
  from have (keep = ID pre_stress_visit: post_stress_visit:)
;
quit;