BookmarkSubscribeRSS Feed
vincentvl
Calcite | Level 5

Hi all,

I have data that looks like this:

Status_start_date     Status_end_date     Status     ID

01JAN2001               17DEC2001          one          0001

18DEC2001               23JAN2002          two          0001

23JAN2002               15MAR2015          three        0001

01JAN2001               17MAY2005          three        0002

18MAY2005               23JAN2007          two          0002

23JAN2007               15MAR2015          one          0002

What I want to do is to create a distinct count of ID on monthly basis by status (therefore this should be an average count, since the status isn't always for a complete month..)

Output should look like (let's say I have 1000 ID's):

Monthly_date     Status     Average_count

01JAN2001        One               300.3

01JAN2001        Two               478.7

01JAN2001        Three              221,0

01FEB2001        One

...

01MAR2015       Three

Can somebody help?

Thanks in advance

2 REPLIES 2
LinusH
Tourmaline | Level 20

What do you mean by average count?

Isn't that more of a weighed count?

Would 1 mean the same status for on ID a whole month?

If it's tricky to explain, provide sample data that match (have - want).

Data never sleeps
vincentvl
Calcite | Level 5

Weighted count is more correct indeed.

WHAT I HAVE

What I did before was generating for every date range (status_start_date to status_end_date) all the in between lying months.

For the first line (of my example above) it results in

Status_start_date     Status_end_date     Status     ID         Month

01JAN2001               17DEC2001          one          0001     01JAN2001

01JAN2001               17DEC2001          one          0001     01FEB2001

..

01JAN2001               17DEC2001          one          0001     01DEC2001


Based on this table, I did a distinct count on ID by month and status.


WHAT I WANT

the previous isn't sufficient, because DEC2001 is counted as a complete month for status=one and ID=0001, while that status was only active for the first 17 days of DEC2001. I want to account for that problem..



sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 325 views
  • 0 likes
  • 2 in conversation