Venky: To settle questions about data typing in SAS SQL: proc sql; create table X(x char) ; insert into X /*set x=1 causes an error*/ set x='1' ; create table Y as select x, input(x,8.) from X where x='1' ; create table Z as select input(x,8.) as x from X where x='1' ; quit;

Column variables accessed from SAS datasets keep the same data type unless explicitly converted to another data type. SAS SQL separates the name of a column variable in a source table from the alias of a column variable in the yield of the query. Sig

-----Original Message----- From: Chakravarthy, Venky [mailto:Venky.Chakravarthy@PFIZER.COM] Sent: Friday, February 20, 2004 1:22 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: SQL join with Mixed Data types (was RE: Help with Proc Means. .)

Okay, I see my goof in the syntax. Thanks. I am in data step default mode when it comes to separating variables with spaces instead of commas.

Quite a remarkable goof though. Table B is assigned an alias A and is referenced both by its alias and its full name in the query, giving the superficial appearance that Table A and B are both involved. It is even more remarkable that Kumar (the original poster of the Means question) probably goofed in the same way when he mentioned:

<<<< >Thanks to all of you who helped me out. I had converted the ptid field >to numeric, but I did a join (a.ptid=b.ptid) and when the resulting >table was created, it converted it back to char. >>>>>>

I was incredulous at first. However, when I reproduced that behavior (with the goofy syntax) I assumed it must be true and posted it.

Perhaps, this question does deserve the Friday treatment after all. _________________________________ Venky Chakravarthy E-mail: swovcc_AT_hotmail_DOT_com

-----Original Message----- From: Howard Schreier [mailto:Howard_Schreier@ITA.DOC.GOV] Sent: Friday, February 20, 2004 12:40 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: SQL join with Mixed Data types (was RE: Help with Proc Means..)

Try it with a comma between B and A.

On Fri, 20 Feb 2004 12:14:18 -0500, Chakravarthy, Venky <Venky.Chakravarthy@PFIZER.COM> wrote:

>I am raising this on a Friday. Therefore, I run the risk of not being >taken seriously. But I think it is serious. > >I wasn't aware that a SQL join of mixed data types would be allowed >without an explicit conversion. I think this has potential for >disaster. > >data a ; > ptid = "1" ; >run ; > >data b ; > ptid = 1 ; >run ; > >proc sql ; > create table c as > select a.ptid /* this is defined as character */ > from b a > where a.ptid = b.ptid ; >quit ; > >proc contents ; >run ; > ># Variable Type Len Pos >----------------------------------- >1 PTID Num 8 0 > >In a data step MERGE this would result in an error and rightfully so. >Am I the only one thinking that mixing up data types in this manner is >asking for >trouble? Is this an ANSI SQL standard? SQLheads please weigh in. > >Thanks. >________________________________ >Venky Chakravarthy >E-mail: swovcc_AT_hotmail_DOT_com > >-----Original Message----- >From: Nagakumar Sridhar [mailto:nsridhar@ATHEROGENICS.COM] >Sent: Friday, February 20, 2004 11:42 AM >To: SAS-L@LISTSERV.UGA.EDU >Subject: Re: Help with Proc Means.. > > >Hi Folks: > > > >Thanks to all of you who helped me out. I had converted the ptid field >to numeric, but I did a join (a.ptid=b.ptid) and when the resulting >table was created, it converted it back to char. > > > >Regards > > > >Kumar > > > > > > > > > > > >-----Original Message----- >From: Nagakumar Sridhar >Sent: Friday, February 20, 2004 11:31 AM >To: SAS-L@LISTSERV.UGA.EDU >Subject: Help with Proc Means.. > > > >Hi Folks: > > > > I have a weird problem with proc means (maybe not weird to >y'all but I've never encountered this)..The problem is that in the proc >means step, in the var statement I have a variable which I need to >analyze. > > > > My code looks like so: > > > >proc means data=all1 noprint nway; > > class analyte trtmnt cpevent status; > > var ptid; > > output out=final1(drop=_type_ _freq_) n=n ; > > > >I get an error: > > > >MPRINT(ANALYZE): proc means data=all1 noprint nway; > >MPRINT(ANALYZE): class analyte trtmnt cpevent status; > >MPRINT(ANALYZE): var ptid; > >ERROR: Variable ptid in list does not match type prescribed for this >list. > >MPRINT(ANALYZE): output out=final1(drop=_type_ _freq_) n=n ; > >NOTE: The SAS System stopped processing this step because of errors. > >WARNING: The data set WORK.FINAL1 may be incomplete. When this step >was stopped there were 0 observations and 0 variables. > >WARNING: Data set WORK.FINAL1 was not replaced because this step was >stopped. > >NOTE: PROCEDURE MEANS used: > > real time 0.03 seconds > > cpu time 0.00 seconds > > > > > >I did try converting the ptid to numeric, but no cigar!! Is there any >"Prescribed var type" for proc means? Any help would be greatly >appreciated.. > > > >Thanks and regards > > > >Kumar > > > > > > > > > > >LEGAL NOTICE >Unless expressly stated otherwise, this message is confidential and may >be privileged. It is intended for the addressee(s) only. Access to this E-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents of this E-mail or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately.

LEGAL NOTICE Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this E-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents of this E-mail or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately.