Date: Fri, 2 Jun 2006 13:55:39 -0700
Reply-To: "Terjeson, Mark (IM&R)" <Mterjeson@RUSSELL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Terjeson, Mark (IM&R)" <Mterjeson@RUSSELL.COM>
Subject: Re: Question on data manipulation
In-Reply-To: A<76a10b6e0606021235q1e8e2456kb50b472e6935dace@mail.gmail.com>
Content-Type: text/plain; charset="us-ascii"
Hi Ram,
To get the layout you specify, a template
with all ids and weeks per city is needed.
The following queries give your desired
result.
data sample;
input Custid City $14. week count;
cards;
1 Los Angeles 1 2
2 Los Angeles 1 1
3 Los Angeles 1 2
4 Los Angeles 1 1
5 Los Angeles 1 3
1 Los Angeles 2 1
2 Los Angeles 2 1
3 Los Angeles 2 1
1 Los Angeles 3 2
2 Los Angeles 3 2
6 Boston 1 2
7 Boston 1 1
8 Boston 1 2
6 Boston 2 1
7 Boston 2 1
6 Boston 3 1
;
run;
**********************************;
* the two separate query version *
**********************************;
proc sql;
create table template as
select
template.City,
template.Week,
template.Custid,
coalesce(c.count,0) as count
from
(
select
*
from (select distinct Custid,City
from sample group by City),
(select distinct Week
from sample)
) as template
left join
(
select
a.Custid,
a.City,
a.Week,
a.count
from
sample as a
) as c
on
template.Custid eq c.Custid
and
template.City eq c.City
and
template.Week eq c.Week
order by
template.City desc,
template.Week,
template.Custid
;
quit;
proc sql;
create table result as
(
select
a.Custid,
a.City,
a.Week,
sum((select sum(count)
from template as b
where a.Custid ne b.Custid
and a.City eq b.city
and a.Week-1 eq b.Week
),0) as sum_last
from
template as a
)
order by
a.City desc,
a.Week,
a.Custid
;
quit;
*************************;
* the one query version *
*************************;
* NOTE: we could paste the template query into
the code twice and not have any macro code
or we can use the macro text substitution
to make it look cleaner ;
%macro template;
(
select
template.City,
template.Week,
template.Custid,
coalesce(c.count,0) as count
from
(
select
*
from (select distinct Custid,City
from sample group by City),
(select distinct Week
from sample)
) as template
left join
(
select
a.Custid,
a.City,
a.Week,
a.count
from
sample as a
) as c
on
template.Custid eq c.Custid
and
template.City eq c.City
and
template.Week eq c.Week
)
%mend;
proc sql;
create table result as
(
select
a.Custid,
a.City,
a.Week,
sum((select sum(count)
from %template as b
where a.Custid ne b.Custid
and a.City eq b.city
and a.Week-1 eq b.Week
),0) as sum_last
from
%template as a
)
order by
a.City desc,
a.Week,
a.Custid
;
quit;
Hope this is helpful.
Mark Terjeson
Senior Programmer Analyst, IM&R
Russell Investment Group
Russell
Global Leaders in Multi-Manager Investing
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Ram
.
Sent: Friday, June 02, 2006 12:35 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Question on data manipulation
>
>
> Dear SAS-ers,
>
> I have a question on data manipulation. I have a data set with four
> columns: custid, city, week and count. It looks something like this:
>
>
> Custid City week count
> -------------------------------------------------
> 1 Los Angeles 1 2
> 2 Los Angeles 1 1
>
> 3 Los Angeles 1 2
>
> 4 Los Angeles 1 1
>
> 5 Los Angeles 1 3
>
> 1 Los Angeles 2 1
> 2 Los Angeles 2 1
>
> 3 Los Angeles 2 1
>
> 1 Los Angeles 3 2
>
> 2 Los Angeles 3 2
>
> 6 Boston 1 2
> 7 Boston 1 1
>
> 8 Boston 1 2
>
> 6 Boston 2 1
> 7 Boston 2 1
>
> 6 Boston 3 1
>
>
>
>
>
> From this, I want to create a dataset where in I want to have three
> columns, namely custid, city, and sum_last, which is sum of count of
> other custids in the *same* city as the custid *from* the previous
> week.
>
>
>
> In other words, I would like to generate the following dataset from
> the above dataset:
>
>
>
> Custid City week sum_last
> -------------------------------------------------
> 1 Los Angeles 1 0
> 2 Los Angeles 1 0
>
> 3 Los Angeles 1 0
>
> 4 Los Angeles 1 0
>
> 5 Los Angeles 1 0
>
> 1 Los Angeles 2 7
> 2 Los Angeles 2 8
>
> 3 Los Angeles 2 7
>
> 4 Los Angeles 2 8
>
> 5 Los Angeles 2 6
>
> 1 Los Angeles 3 2
> 2 Los Angeles 3 2
>
> 3 Los Angeles 3 2
>
> 4 Los Angeles 3 3
>
> 5 Los Angeles 3 3
>
> 6 Boston 1 0
> 7 Boston 1 0
>
> 8 Boston 1 0
>
> 6 Boston 2 3
>
> 7 Boston 2 4
> 8 Boston 2 3
>
> 6 Boston 3 1
>
> 7 Boston 3 1
>
> 8 Boston 3 2
>
The number of custids varies across cities. I would appreciate if you
can let me know how to solve this. Thanks, -Ram
| 
