select all columns with substring Offer via proc sql

[Ronein]

Hello

LEts say I run proc sql query (I must run it because it is many to many  merge that is not working well in data step).

Please note that in this example I didnt perform merge and just want to select speicifc columns by criteria.

Let's say that I want to check which columns exist with name "Offer" in the source data set, and then select them in my query.

What is the way to do it?

I want to select CustID and all columns have  subs tring  "offer" and I wan tto do it via proc sql please

   Data have;
   Input CustID  Offer_Tomato Offer_Banana Milk_offer newspapaer_offer offer_water Firstname $ Year_birth;
   cards;
   111 10 30 20 40 50 Jow 1990
   222 15 25 10 40 18 Yulia 1987
   ;
   Run;

[LinusH]

This will get you started. Use the macro variable in your query.

   proc sql;
   select name into: offer_col separated by ','
	   from dictionary.columns
	   where libname = 'WORK' 
		and upcase(memname) = 'HAVE' 
		and upcase(name) contains 'OFFER';
   quit;

   %put &offer_col;