Date:         Wed, 26 Nov 2003 07:44:00 -0800
Reply-To:     "Terjeson, Mark" <TERJEM@DSHS.WA.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Terjeson, Mark" <TERJEM@DSHS.WA.GOV>
Subject:      Re: Given monthly records,
              determine count of "id" for each month ( 01/2002 to 12/2002).
Comments: To: Howard Schreier <Howard_Schreier@ITA.DOC.GOV>
Content-Type: text/plain; charset=iso-8859-1

Hi,

It was intuitive to me that the original
course of thought process had a change of
direction between making the sample data
and dreaming up a sample result.  Seeing
that random dates were made and that there
were sequential id numbers was obvious
that going back to have some duplicate ids
got missed. We all do this from time to
time.

Indeed the code provided does look like
1's and missings (but with the original
example data).  The code does work fine
for the desired result -- assuming matching
source data were provided. (As seen below)


    * new corresponding sample data ;
data dates;
   input @1 itdat1 mmddyy8. @10 itdat2 mmddyy8. @19 id $4.;
cards;
01/16/02 01/31/02 id1
01/02/02 01/14/02 id1
02/05/02 02/22/02 id1
01/01/02 01/05/02 id2
01/02/02 01/06/02 id2
01/03/02 01/07/02 id2
01/04/02 01/08/02 id2
01/05/02 01/09/02 id2
01/01/02 02/21/02 id3
01/02/02 01/22/02 id3
01/03/02 02/23/02 id3
01/04/02 02/24/02 id3
01/05/02 01/25/02 id3
01/06/02 02/26/02 id3
01/07/02 01/27/02 id3
01/08/02 01/28/02 id3
01/09/02 01/29/02 id3
01/10/02 01/20/02 id3
;
run;

data dates2(keep=id yyyymm);
    set dates;
    lastmo = 'yyyymm';
    do i = itdat1 to itdat2;
        yyyymm = put(i,yymmn6.);
        if yyyymm ne lastmo then output;
        lastmo = yyyymm;
    end;
run;

proc sql;
    create table dates3 as
    select id,yyyymm,count(yyyymm) as count
    from dates2
    group by id,yyyymm
    order by id,yyyymm;
quit;

option missing=0;
proc transpose data=dates3 out=dates4(drop=_name_);
    by id;
    id yyyymm;
    var count;
run;

Does look like:

>IDs     Jan/02     Feb/02 ...  Dec/02
>---     ------     ------      ------
>
>id1        2           1
>id2        5           0
>id3       10           4
>id4       ...          ...     etc.
>


Hope this is helpful,
Mark Terjeson
Reporting, Analysis, and Procurement Section
Information Services Division
Department of Social and Health Services
State of Washington
mailto:terjem@dshs.wa.gov





-----Original Message-----
From: Howard Schreier [mailto:Howard_Schreier@ITA.DOC.GOV]
Sent: Wednesday, November 26, 2003 6:15 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Given monthly records, determine count of "id" for each
month ( 01/2002 to 12/2002).


Mark's code generates a dataset which has the desired shape (ID by month),
but it contains just ones and missing values, indicating whether or not any
part of a month is within the date span.

I don't know about anyone else, but I am absolutely unable to fathom the
logic by which David derives his desired results from the given data.

On Tue, 25 Nov 2003 15:17:55 -0800, Terjeson, Mark <TERJEM@DSHS.WA.GOV>
wrote:

>Hi David,
>
>Does this get you in the ballpark?
>
>
>data dates2(keep=id yyyymm);
>    set dates;
>    lastmo = 'yyyymm';
>    do i = itdat1 to itdat2;
>        yyyymm = put(i,yymmn6.);
>        if yyyymm ne lastmo then output;
>        lastmo = yyyymm;
>    end;
>run;
>
>proc sql;
>    create table dates3 as
>    select id,yyyymm,count(yyyymm) as count
>    from dates2
>    group by id,yyyymm
>    order by id,yyyymm;
>quit;
>
>proc transpose data=dates3 out=dates4(drop=_name_);
>    by id;
>    id yyyymm;
>    var count;
>run;
>
>
>Hope this is helpful,
>Mark Terjeson
>Reporting, Analysis, and Procurement Section
>Information Services Division
>Department of Social and Health Services
>State of Washington
>mailto:terjem@dshs.wa.gov
>
>
>
>
>-----Original Message-----
>From: David Collins [mailto:sasguy2003@YAHOO.COM]
>Sent: Tuesday, November 25, 2003 2:15 PM
>To: SAS-L@LISTSERV.UGA.EDU
>Subject: Given monthly records, determine count of "id" for each month (
>01/2002 to 12/2002).
>
>
>Dear SAS-L,
>
>I haven't found a solution for this... although I have read some
>solutions that might be similar... I have not been able to tweak the
>results to the point of monthly numbers in columns ( Jan2002,
>Feb2002...Dec2002) by "id" for the given ranges in each record.  (I
>have used SQL in some cases to join separate monthly pulls of data or
>in some cases separate reporting columns, altogether... but, never the
>opportunity to do this type of case, until now).
>
>Below is a simplified example of the data...
>
>data dates;
>   input @1 itdat1 mmddyy8. @10 itdat2 mmddyy8. @19 id $4.;
>cards;
>12/16/02 12/31/02 id1
>01/01/02 05/05/02 id2
>01/01/02 05/26/02 id3
>01/01/02 03/03/02 id4
>01/01/02 03/03/02 id5
>01/01/02 03/03/02 id6
>01/01/02 09/01/02 id7
>01/01/02 12/31/02 id8
>01/01/02 10/06/02 id9
>01/01/02 12/31/02 id10
>12/30/02 12/31/02 id11
>01/01/02 12/29/02 id12
>07/01/02 12/31/02 id13
>07/08/02 11/24/02 id14
>01/01/02 04/14/02 id15
>01/01/02 12/31/02 id16
>12/30/02 12/31/02 id17
>01/01/02 06/23/02 id18
>07/01/02 11/24/02 id19
>01/21/02 02/17/02 id20
>02/11/02 12/31/02 id21
>02/18/02 04/28/02 id22
>12/30/02 12/31/02 id23
>01/01/02 12/08/02 id24
>01/21/02 09/01/02 id25
>07/15/02 12/31/02 id26
>12/30/02 12/31/02 id27
>12/30/02 12/31/02 id28
>10/28/02 12/31/02 id29
>01/01/02 01/27/02 id30
>01/01/02 12/31/02 id31
>02/11/02 11/24/02 id32
>12/16/02 12/31/02 id33
>01/07/02 09/22/02 id34
>01/01/02 02/24/02 id35
>01/01/02 02/03/02 id36
>12/30/02 12/31/02 id37
>01/01/02 08/04/02 id38
>12/30/02 12/31/02 id39
>;
>run;
>
>
>The RESULTS should look similar to the following:
>
>IDs     Jan/02     Feb/02 ...  Dec/02
>---     ------     ------      ------
>
>id1        2           1
>id2        5           0
>id3       10           4
>id4       ...          ...     etc.
>
>
>Any feedback on this is welcome!
>
>David C.