Date: Tue, 1 Feb 2011 18:32:59 -0500
Reply-To: Arthur Tabachneck <art297@ROGERS.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Arthur Tabachneck <art297@ROGERS.COM>
Subject: Re: How to count via Loop Counting
Bob,
Precisely why I asked the question (other than the fact that I didn't know
the answer). Yes, I agree that proc sql would probably be the easiest way,
but I'd define the table as a view and simply schedule the run.
Art
-------
On Tue, 1 Feb 2011 15:28:45 -0800, DUELL, BOB (ATTCINW) <BD9439@ATT.COM>
wrote:
>Hi Art,
>
>A "view" is nothing more than a description of data. Whatever is in
>"whatever" when the view executes gets processed. In other words, if
>your "whatever" sqlserver table is constantly being updated, executing
>the multiple times will give different results.
>
>WRT to the original question, I'd use SQL, assuming the three values of
>"activity_cd" are correct:
>
>proc sql;
> create table want as
> select email
> , sum(case when activity_cd = 3 then 1 else 0 end) as browsed
> , sum(case when activity_cd = 4 then 1 else 0 end) as bought
> , sum(case when activity_cd = 5 then 1 else 0 end) as searched
> from have
> where date between today()-6 and today()
> group by 1;
>quit;
>
>Bob
>
>-----Original Message-----
>From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
>Arthur Tabachneck
>Sent: Tuesday, February 01, 2011 3:11 PM
>To: SAS-L@LISTSERV.UGA.EDU
>Subject: Re: How to count via Loop Counting
>
>Joe,
>
>
>
>Almost, but where the view simply gets updated with new data. It's
>quite
>possibly a feature of views I just never realized was there.
>
>
>
>For example, say "whatever" in your example is a sqlserver database.
>Once
>today_view is created does it automatically reflect the changes and
>additions that are made to "whatever"?
>
>
>
>Art
>
> _____
>
>From: Joe Matise [mailto:snoopy369@gmail.com]
>Sent: Tuesday, February 01, 2011 5:54 PM
>To: Arthur Tabachneck
>Cc: SAS-L@listserv.uga.edu
>Subject: Re: How to count via Loop Counting
>
>
>
>You mean a datastep view?? Or something else?
>
>data today_view/view=today_view;
>set whatever;
>if dayvar > today() - 7 then week=1; else week=0;
>if dayvar > today() - 30 then month=1; else month=0;
>if dayvar > today() - 365 then year=1; *obviously intck is better for
>these
>two, just being simplistic;
>run;
>
>proc means data=today_view;
>class week month year;
>var datavar;
>run;
>
>That would approximately do what you're describing, if I understand it
>correctly. Of course PROC SQL can also do a lot with views.
>
>-Joe
>
>On Tue, Feb 1, 2011 at 4:49 PM, Arthur Tabachneck <art297@rogers.com>
>wrote:
>
>Bill,
>
>This isn't an answer but, rather, a secondary question for the list.
>
>When I first saw this question I was going to recommend doing it all
>within
>a view so that the calculations would ALWAYS look up the current day.
>
>Is there a way to create a view of a database or at least of a file that
>can
>be updated?
>
>That happens to be an area of SAS that I've never had to work with, but
>a
>fascinating concept.
>
>Just curious,
>Art
>-------
>
>On Tue, 1 Feb 2011 16:31:43 -0600, Joe Matise <snoopy369@GMAIL.COM>
>wrote:
>
>>No reason to use loop counting unless you are either using this in some
>>other analysis or doing it for a class... PROC MEANS or PROC FREQ or
>PROC
>>TABULATE etc. will all happily count things for you. Just define a
>variable
>>(or variables) for each time period (say, WEEK, MONTH, YEAR variables,
>each
>>with the week #, month #, and year #) , then use those in the TABLES or
>>CLASS statement to get values for each period.
>>
>>-Joe
>>
>>On Tue, Feb 1, 2011 at 3:33 PM, Bill Westman <bdwebman@gmail.com>
>wrote:
>>
>>> I have the following data (millions of records):
>>>
>>> email activity_cd activity date
>>> joe@hotmail.com, 5, search, 02/01/11,
>>> joe@hotmail.com, 5, search, 02/01/11,
>>> joe@hotmail.com, 5, search, 01/28/11,
>>> joe@hotmail.com, 4, buy, 01/28/11,
>>> joe@hotmail.com, 4, buy, 01/11/11,
>>> joe@hotmail.com, 3, browse, 01/05/11
>>>
>>> (multiple email addresses)
>>>
>>> What I'd like SAS to do is tell me the following:
>>>
>>> is the past week (-7day period), [joe@hotmail.com] searched 3 times
>and
>>> bought 1 time. within the past month I've searched 3 times, bought 2
>>> times and browsed once.
>>>
>>> How would I program that across the entire data set?
>>>
>>> Thanks!
>>>
| 
