Date:         Tue, 13 Aug 2002 07:37:23 -0400
Reply-To:     Don Henderson <donaldjhenderson@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Don Henderson <donaldjhenderson@HOTMAIL.COM>
Subject:      Re: query in Enterprise Guide
Content-Type: text/plain; charset="iso-8859-1"

Zbiggy,

If I understand your question correctly, what you need to look into is date
and datetime constants. If the values of your variables are SAS date and
datetime constants you can easily generate the table you want. If the
variables are character variables whose value is the text corresponding to
the date, then you would need to convert them to SAS date and date time
variables first.

For now, in order to keep things simple, lets assume your variables are SAS
datetime constants. If so the following SQL query (untested) should generate
exactly the table you describe. This query presumes that an Active Service
is anything for this year (and yes you can can much more sophisticated with
providing a more current value):

proc sql;
  create table services as
  select service,
            count(*) as Total,
            sum(DateTo >= "01jan2002:00:00:00"dt) as Active
  from <your dataset here>
  group service;
quit;

The expression "01jan2002:00:00:00"dt is a datetime constant. The dt after
the quote string tells SAS to interpret the value as a datetime value. The
first 9 characters are the date in DATE9. format. The next 8 are the hour,
minute, seconds of that day. Similarly, "01jan2002"d is a date constant.
Next consider the logical expression DateTo > "01jan2002:00:00:00"dt is a
logical expression which evaluate to either 0 (if false) or 1 (if true). So
you can see that you are calculation the sum of that 0/1 logical which ends
up being the count of the number of services where the DateTo is this
calander year.

HTH,
Don Henderson

p.s., not sure about how to run this query in EG as I have never used EG
very much. It will work however in SAS itself.

----- Original Message -----
From: "Zbiggy" <zbiggy@POCZTA.ONET.PL>
Newsgroups: bit.listserv.sas-l
To: <SAS-L@LISTSERV.UGA.EDU>
Sent: Monday, August 12, 2002 5:16 PM
Subject: query in Enterprise Guide


> Hello,
> recently I started to work a bit with SAS, EG in particular. My normal way
> of doing things is to join, group and list data and export them to some
> Microsoft databases and finish my work there. This is sufficient for my
job,
> not for me - I wish I could do more with SAS itself (or, at the time, the
> Guide).
> Would anyone give me a hint on how to make such a query:
>
> I have a dataset with services, and this goes like this:
> Customer_ID, Service, DateFrom, DateTo
>
> which tells which customers uses what service(s).
>
> Some fields in "DateTo" contain some past dates like "12OCT01:23:59:59"
> (meaning that a service is inactive).
> What I would like to achieve is a query grouping and counting services
also
> having one column with active only services, like this:
>
>             Total    Active
> Service 1     345        26
> Service 2    1234      1000
> Service 3    8372      7982
> Service 4   11982     10000
>
> so inevitably I must do some calculations based on 'DateTo' to create the
> 'Active' column (I guessed how to count 'Total' ;)
>
> I suppose I can't do that because my knowledge on operating on dates in
SAS
> is pretty poor.
> Who could help me write this magic line saying "case table.dateto > ...."?
> Or just give a hint, a key word?
>
> Thanks in advance,
>
> Zbiggy
>