Date:         Mon, 16 Jul 2007 07:33:03 -0700
Reply-To:     Franz <franz_cl2003@YAHOO.FR>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Franz <franz_cl2003@YAHOO.FR>
Subject:      Re: PROC SQL: Except (urgent please)
Content-Type: text/plain; charset=iso-8859-1

How do I prevent SAS from reading the carriage return
character while reading raw data?

Thank you.
Franz.


--- Franz <franz_cl2003@yahoo.fr> wrote:

> Dear all,
>
> Here is a sample of the data file I am reading d
> from.
> The first 3 fields are not that important for my
> task.
>
>
> 01020 XXYY12AAA       XX12CCCCC       (R) EMPYEMA
> 01021 XXYY12BBB       XX12CCCCC       ARTHRITIS RIGHT PULS
>
>
>
> In my file the variable d I am having trouble with
> hlod the values:
> R) EMPYEMA
> ARTHRITIS RIGHT PULS)
>
> After reading the raw data file into a dataset B I
> then execute my SQL statement (see bellow) which is
> not working properly, even though those 2 values of
> d
> (above) are also present in A
>
>
> proc sql;
> create table Final as
>      select * FROM A
> except
>      select * FROM B
> ;
> quit;
>
>
> Please help. Thanks.
> Franz.
>
>
>
> --- Arild S <sko@KLP.NO> wrote:
>
> > Again, you'd be easier to help if you could only
> > post some actual data.
> > Now, at least we know that you've got a tab
> > delimited file.
> > Have you tried reading it as such? Alternatively,
> > you can just compress out
> > the tabs, replace them, or whatever suits your
> > needs. For example, are
> > there other variables along with "d" that you want
> > to keep...
> > Anyway, the key is that tab is '09'x (hex value):
> >
> > 224  data _null_;
> > 225      infile 'C:\temp\tabs.txt' dlm='09'x;
> > 226      length a b c d e$20;
> > 227      input @;
> > 228      input a b c; *read;
> > 229      d = compress(_infile_, '09'x); *remove;
> > 230      e = translate(_infile_, ';', '09'x);
> > *replace;
> > 231      put _all_;
> > 232  run;
> >
> > a=aa b=bb c=cc d=aabbcc e=aa;bb;cc _ERROR_=0
> > _INFILE_=aa bb cc
> > _N_=1
> > a=dd b=ee c=ff d=ddeeff e=dd;ee;ff _ERROR_=0
> > _INFILE_=dd ee ff
> > _N_=2
> > NOTE: 2 records were read from the infile
> > 'C:\temp\tabs.txt'.
> >
>
> --- Franz <franz_cl2003@yahoo.fr> wrote:
>
> > Dear all,
> >
> > Yes I have trouble with reading the original
> > (tabulated) raw data file.
> >
> > data y;
> > format d $200.;
> > informat d $200.;
> > input d $;
> > ffff
> > gggg
> > hhhh
> > ;
> >
> > If my variable d where to start in the first
> column
> > of
> > the raw data everything would be fine (above data
> > step).
> >
> > But in reality the var d starts at column 41 of a
> > tabulated raw data. Although I could read the
> field
> > with the following data step, I am still having
> > trouble
> > with my SQL statement (see bellow).
> >
> > data y;
> > format d $200.;
> > informat d $200.;
> > input +26 d $;
> >                 ffff
> >                 gggg
> >                 hhhh
> > ;
> >
> > Is there any formated character I need to remove
> > from
> > the red var d (trim or whatever) for my SQL
> > statement
> > to work.
> >
> > Thank you very much & reagrds,
> > Franz.
> >
> >
> > --- "Howard Schreier <hs AT dc-sug DOT org>"
> > <nospam@HOWLES.COM> wrote:
> >
> > > On Sat, 14 Jul 2007 06:14:42 -0700, Franz
> > > <franz_cl2003@YAHOO.FR> wrote:
> > >
> > > >Dear All,
> > > >
> > > >d is a char Variable having a length of $200 in
> > > both X
> > > >and Y. This is the configuration that I have:
> > > >
> > > >data a (keep=c);
> > > >set X;
> > > >c = compress (d)
> > > >run;
> > > >
> > > >data b (keep=c);
> > > >set Y;
> > > >c = compress (d);
> > > >run;
> > > >
> > > >proc sql;
> > > >create table Final as
> > > >        select * FROM A
> > > >except
> > > >        select * FROM B
> > > >;
> > > >quit;
> > > >
> > > >The problem is that my except is NOT working
> > since
> > > >Work.Final still habe the same number of
> > > observations
> > > >as tWork.A, although nearly all the values of C
> > in
> > > the
> > > >dataset A are also present in the dataset B.
> > > >
> > > >I will really appreciate you help.
> > > >
> > > >Thank you & kind regards
> > > >Franz
> > >
> > > The problem lies in your data. If you can
> provide
> > an
> > > example of X and Y
> > > which produces the unexpected behavior, perhaps
> > > somebody will be able to help.
> > >
> > > I tested your code by first running
> > >
> > >    data x;
> > >    length d $200;
> > >    do d = 'one', 'two', 'three'; output; end;
> > >    run;
> > >
> > >    data y;
> > >    length d $200;
> > >    do d = 'one',        'three'; output; end;
> > >    run;
> > >
> > > then running your code. My output had one row
> > > (containing "two"), as expected.
> > >
> > > By the way, you really don't need those
> > intermediate
> > > DATA steps. You should
> > > get the same output from
> > >
> > >    create table Final as
> > >            select compress (d) as c FROM x
> > >    except
> > >            select compress (d) as c FROM y
> > >    ;
> > >
> >
> >
>
=== message truncated ===




____________________________________________________________________________________
Looking for earth-friendly autos?
Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center.
http://autos.yahoo.com/green_center/