Date:         Sat, 12 Feb 2005 22:54:05 -0600
Reply-To:     Haigang Zhou <hzhou3@UNLSERVE.UNL.EDU>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Haigang Zhou <hzhou3@UNLSERVE.UNL.EDU>
Subject:      Re: Computing Percentages within Subtotals
Comments: To: jfh4992-stanford@yahoo.com
In-Reply-To:  <20050212230654.12152.qmail@web53706.mail.yahoo.com>
Content-Type: text/plain; charset="us-ascii"

No. It does not work.


-----Original Message-----
From: Jack Hamilton [mailto:jfh4992-stanford@yahoo.com]
Sent: Saturday, February 12, 2005 5:07 PM
To: Haigang Zhou; SAS-L@LISTSERV.UGA.EDU
Subject: Re: [SAS-L] Computing Percentages within Subtotals


Try

   select quote(trim(_name_)) into : gone separated by ', '

and

   delete from two where _name_ in (&gone.);



--- Haigang Zhou <hzhou3@UNLSERVE.UNL.EDU> wrote:

> I apprecaite everyone who replied to the original post. So far I have
> tried the two SQL methods and they works great.
>
> In the past few days, I have tried to learn SQL by studying the codes.
> In the process, I tried to delete those observations from set "two"
> directly.
> However, I got an error message. What I did is adding the following
> line to
> the code written by Howard Schreier:
>
> delete from two where (_name_ in &gone.);
>
>
>
>
> *===the complete code I used;
>
> proc format;
>  invalue notrd '-9.00'=.T other=[5.2];
> quit;
>
> DATA ONE;
>    INPUT date (acx dyk ojm wuq btr)(: notrd.);
>    DATALINES;
>  1 16.15 19.63 19.55 15.86 12.36
>  2 10.74 -9.00 13.01 11.62 17.77
>  3 18.10 11.51 10.95 10.09 15.88
>  4 17.00 18.80 11.19 15.87 15.56
>  5 12.38 -9.00 16.14 10.73 12.85
>  6 -9.00 14.16 -9.00 11.22 16.59
>  7 12.60 13.54 12.85 -9.00 11.90
>  8 -9.00 13.17 10.52 14.97 13.55
>  9 12.75 13.18 14.41 18.20 14.13
>  10 -9.00 17.35 11.92 18.61 10.02
>  11 18.49 -9.00 -9.00 16.01 16.41
>  12 18.40 12.07 19.66 13.66 18.61
>  13 -9.00 12.39 15.79 18.00 10.07
>  14 -9.00 19.72 12.83 19.28 15.46
>  15 16.77 19.11 -9.00 11.47 10.56
>  16 10.03 -9.00 10.08 15.21 12.65
>  17 16.18 10.04 14.14 12.86 14.45
>  18 19.01 14.67 12.67 19.76 12.06
>  19 18.86 19.03 16.91 12.99 15.85
>  20 18.02 12.06 14.99 16.02 13.70
> ;
> RUN;
> proc transpose data=one out=two (where = (_name_ ^= 'date') );  by
> date;  var _numeric_;
> run;
>
> proc sql noprint;
>  select _name_ into : gone separated by ', '
>  from two
>  group by _name_
>  having nmiss(col1)/count(*)>0.15;
>  alter table one drop &gone.;
>  delete from two where (_name_ in &gone.);
> quit;
>
>
>
>
> On Sat, 12 Feb 2005 14:56:55 -0500, Howard Schreier <hs AT dc-sug DOT
> org>
> <nospam@HOWLES.COM> wrote:
>
> >Here's one PROC-oriented way.
> >
> >The -9 values undoubtedly represent absence of trading, or some
> other sort
> >of missingness, and it's advantageous to store them that way. So
> start by
> >building a format similar to Pudding Man's:
> >
> >   proc format;
> >   invalue notrd '-9.00'=.T other=[5.2];
> >   quit;
> >
> >Then change the INPUT statement to
> >
> >   INPUT date (acx dyk ojm wuq btr)(: notrd.);
> >
> >and load ONE, as in the original posting.
> >
> >Next, follow Jack's suggestion regarding normalization (but not
> necessarily
> >his aversion to PROC TRANSPOSE):
> >
> >   proc transpose data=one out=two(where = (_name_ ^= 'date') );
> >   by date;
> >   var _numeric_;
> >   run;
> >
> >SQL can do the rest:
> >
> >   proc sql noprint;
> >   select _name_ into : gone separated by ', '
> >    from two
> >    group by _name_
> >    having nmiss(col1)/count(*)>0.15;
> >   alter table one drop &gone;
> >   quit;
> >
> >However, I probably would not do it that way. I would flip the
> inequality
> >so as to yield the names of the columns to be *kept*, then do a
> CREATE VIEW
> >to do the subsetting while preserving all of the original data.
> >
> >On Fri, 11 Feb 2005 00:43:10 -0500, Haigang Zhou
> <hzhou3@UNLSERVE.UNL.EDU>
> >wrote:
> >
> >>I have some stock prices as shown below. I want to count the number
> of
> >>occurrences of "-9" for each stock (stk1 - stk5), and delete a
> stock if the
> >>total number of occurrences of "-9" exceeds 15% of the total
> observations.
> >>For example, stk1 has 5 occurrences of "-9", and therefore, I want
> to
> >delete
> >>the entire column from the dataset.
> >>
> >>Thank you very much!
> >>
> >>
> >>
> >>DATA ONE;
> >>   INPUT date acx dyk ojm wuq btr;
> >>   DATALINES;
> >> 1 16.15 19.63 19.55 15.86 12.36
> >> 2 10.74 -9.00 13.01 11.62 17.77
> >> 3 18.10 11.51 10.95 10.09 15.88
> >> 4 17.00 18.80 11.19 15.87 15.56
> >> 5 12.38 -9.00 16.14 10.73 12.85
> >> 6 -9.00 14.16 -9.00 11.22 16.59
> >> 7 12.60 13.54 12.85 -9.00 11.90
> >> 8 -9.00 13.17 10.52 14.97 13.55
> >> 9 12.75 13.18 14.41 18.20 14.13
> >> 10 -9.00 17.35 11.92 18.61 10.02
> >> 11 18.49 -9.00 -9.00 16.01 16.41
> >> 12 18.40 12.07 19.66 13.66 18.61
> >> 13 -9.00 12.39 15.79 18.00 10.07
> >> 14 -9.00 19.72 12.83 19.28 15.46
> >> 15 16.77 19.11 -9.00 11.47 10.56
> >> 16 10.03 -9.00 10.08 15.21 12.65
> >> 17 16.18 10.04 14.14 12.86 14.45
> >> 18 19.01 14.67 12.67 19.76 12.06
> >> 19 18.86 19.03 16.91 12.99 15.85
> >> 20 18.02 12.06 14.99 16.02 13.70
> >>;
> >>RUN;
>
>