Start by writing your SAS code without any SQL, just what final text you want. Is vname actually a variable in table b? That's what I'm getting from your text. The SAS code would be: data a_new; set a; if q19 = (value from b) then do; (b.vname) = 1; flag19=1; end; run;

So your catx must be catx(' ','if q19 =',b.descr,'then do; ',b.vname,'=1; flag19=1; end;')

That doesn't require table a at all in the SQL, does it. So you can simply write select catx(' ','if q19 =',b.descr,'then do; ',b.vname,'=1; flag19=1; end;') into :tblq19list from commentmg b;

If there are a lot of b.descr that do not qualify, you can run it up against table A with an existential query: select catx(' ','if q19 =',b.descr,'then do; ',b.vname,'=1; flag19=1; end;') into :tblq19list from commentmg b where exist (select 1 from testing a where b.descr=a.q19);

which will only create macro vars for valid descr values.

You probably should consider running this as a regular SQL join, though. This if statement business won't be efficient. Start by doing: proc sql; create table test as select a.*, b.vname, 1 as flag19 from commentmg b, testing a where b.descr=a.q19; quit;

That gets you the table of appropriate values (and you might turn that into a right join, perhaps, if you want all of table a). Then work out getting b.vname into a as a variable, you can do that a few ways, either transposing b.vname on b, or once it's on a, or in a macro step later.

-Joe

On Wed, Apr 1, 2009 at 9:26 AM, D T <sasandstats@live.com> wrote:

> I am not familiar with using catx in proc sql and have been > trying to work out if I could put a line of code into a catx statement. > This > is, in words, what I am trying to do: > > > > if my q19 value in table a is the same as one of the values > in my variable descr in table b, use the value stored in table b in the > variable vname to set the variable with the same name in table a to 1, and > set > a variable called flag19 to 1. > > > > I may be trying something way too complicated. I have not > seen an example that uses two tables in a catx statement, but I have also > not found > many examples of catx in proc sql. I am already failing to just get the > comparison > of values correctly, e.g., for this part: > > 'if',a.q19, '="',b.descr,'"', > > > > Here is what I have—and it is far from working …: > > proc > sql; > > select > catx('','if',a.q19, '="',b.descr,'"', > 'then do;',a.vname,'="1"; > flag1=1;','end;') > into :q1list separated by > ' ' > > from > commentmg as b, testing as a; > > > > quit; > > > > Can someone help? > > Thanks! > > D.T. > > > _________________________________________________________________ > Quick access to your favorite MSN content and Windows Live with Internet > Explorer 8. > > http://ie8.msn.com/microsoft/internet-explorer-8/en-us/ie8.aspx?ocid=B037MSN55C0701A