Date:         Fri, 20 Feb 2004 12:14:18 -0500
Reply-To:     "Chakravarthy, Venky" <Venky.Chakravarthy@PFIZER.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Chakravarthy, Venky" <Venky.Chakravarthy@PFIZER.COM>
Subject:      SQL join with Mixed Data types (was RE: Help with Proc Means..)
Content-Type: text/plain; charset="iso-8859-1"

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.