Date: Wed, 1 Apr 2009 17:52:35 -0500
Reply-To: Joe Matise <snoopy369@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Joe Matise <snoopy369@GMAIL.COM>
Subject: Re: proc sql and catx question
In-Reply-To: <BLU132-W42AA68E44319C55776EEC2C88B0@phx.gbl>
Content-Type: text/plain; charset=windows-1252
Transpose seems the easiest way...
data have;
set sashelp.class;
transpvar=1;
run;
proc transpose data=have out=want let;
id sex ;
by name;
copy age weight height;
run;
(for example)
Obviously the actual answer depends more on your potential values of vname;
if it is a small number, perhaps consider just hardcoding them in there.
-Joe
On Wed, Apr 1, 2009 at 5:40 PM, D T <sasandstats@live.com> wrote:
> Joe,
>
> thanks! I see how I compare the values in the two variables, desc and q19,
> and I got it to work, too. There is one complication, though, and that was
> why I had tried to have two tables in the code. After I compare q19 and
> descr, and both have the same value, I will need to take the value stored in
> vname (a variable name for a variable in data set a), and reference this
> variable in my data set a to set the value of the referenced variable to 1.
> That complicates things, and I have not been able to figure out how to do
> this best.
>
> Do you have additional thoughts?
>
> Thanks!
> D.T.
>
> ------------------------------
> Date: Wed, 1 Apr 2009 10:04:53 -0500
> Subject: Re: proc sql and catx question
> From: snoopy369@gmail.com
> To: sasandstats@live.com
> CC: SAS-L@listserv.uga.edu
>
>
> 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
>
>
>
> ------------------------------
> Rediscover Hotmail®: Get e-mail storage that grows with you. Check it out.<http://windowslive.com/RediscoverHotmail?ocid=TXT_TAGLM_WL_HM_Rediscover_Storage1_042009>
>
| 
