Date:         Fri, 5 Feb 1999 12:13:53 -0500
Reply-To:     murphym2@NATIONWIDE.COM
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         Mike Murphy <murphym2@NATIONWIDE.COM>
Subject:      Re: Re[2]: counter
Comments: To: WHITLOI1 <WHITLOI1@westat.com>
Comments: cc: Bert.Ventresca@cciw.ca
Content-Type: text/plain; charset=us-ascii

Ian
 
If you group by location, you get this:
 
aaa  1  4  5
aaa  2  3  5
aaa  2  6  5
aaa  3  9  5
aaa  3  1  5
bbb  8  7  1
 
while Bert Ventresca wanted this:
 
aaa  1  4  1
aaa  2  3  2
aaa  2  6  2
aaa  3  9  2
aaa  3  1  2
bbb  8  7  1
 
That you can get with grouping by
group by COUNTER1.FISCAL
 
Also, the following line in my SQL is unnecessary:
COUNT(DISTINCT COUNTER1.FISCAL) as FISCAL1 label="COUNT(DISTINCT FISCAL)",
 
The SQL then looks like this:
PROC SQL;
Select
COUNTER1.LOCATION,
COUNTER1.VALUE,
COUNT(*) label="COUNT(*)"
from WORK.COUNTER1
group by COUNTER1.FISCAL
;
 
 
 
 
 
 
 
WHITLOI1@westat.com (WHITLOI1) on 02/05/99 07:19:47 AM
 
Sent by:  WHITLOI1@westat.com (WHITLOI1)
 
To:   Mike Murphy <murphym2@NATIONWIDE.COM>
cc:   (bcc: Michael F Murphy/Nationwide/NWIE)
Subject:  Re[2]: counter
 
 
 
 
Mike,
 
Don't you mean to group by LOCATION?
 
Ian Whitlock <whitloi1@westat.com>
 
____________________Reply Separator____________________
Subject:    Re: counter
Author: Mike Murphy <murphym2@NATIONWIDE.COM>
Date:       2/4/1999 4:35 PM
 
The code below will do it.
The easiest way to do this is to use the Query Window.
The trick is to use the count(*), count distinct and group by
commands in PROC SQL.
 
data counter1;
input Location $ fiscal value;
cards;
aaa          1      4
aaa          2      3
aaa          2      6
aaa          3      9
aaa          3      1
bbb          8      7
;;
run;
PROC SQL;
Select
COUNTER1.LOCATION,
COUNT(DISTINCT COUNTER1.FISCAL) as FISCAL1 label="COUNT(DISTINCT FISCAL)",
COUNTER1.VALUE,
COUNT(*) label="COUNT(*)"
from WORK.COUNTER1
group by COUNTER1.FISCAL
;
 
 
 
 
 
Bert Ventresca <Bert.Ventresca@cciw.ca> on 02/05/99 12:49:04 AM
 
Please respond to Bert Ventresca <Bert.Ventresca@cciw.ca>
 
Sent by:  Bert Ventresca <Bert.Ventresca@cciw.ca>
 
To:   SAS-L@UGA.CC.UGA.EDU
cc:   (bcc: Michael F Murphy/Nationwide/NWIE)
Subject:  counter
 
 
 
 
We have a data set as below
 
Location fiscal value
aaa          1       4
aaa          2       3
aaa           2      6
aaa           3      9.
aaa           3      1
bbb           8      7
we need a data set that looks like
aaa  1  4  1(ie. The number of obs by location and fiscal)
aaa  2  3  2
aaa  2  6  2
aaa  3  9  2
aaa  3  1  2
bbb  8  7  1