About C_andrade

C_andrade · ‎11-06-2015

Here is what I have coded so far. But I haven't worked how to incorporate the start date. data want; set have; array weeks{*} c0101--c1252; sum=0; d _i_ = 1 to 626 until (first_1 > .); if weeks{_i_} in ('1','2','3') then first_1=_i_; end; if (. lt first_1 le 626) then do _i_ = first_1 to min(first_1 + 77, dim(weeks)); sum + weeks{_i_}; end; count=0; do i=1 to 721 while(count<78); if weeks(i) in ('1','2','3') then count+1; end; run;

C_andrade · ‎11-03-2015

Hi Pearson, I am sorry. I will try to recapitulate. Dataset consists of several horizontal rows, each one representing an individual. First column identifies the individual. Then columns represent one week after another. Btw all the week-variables are texts - pondering about making these numerics. There are week variables from the first week of 2001 to the last week of 2012 - giving some 626 variables of these (10 * 52 weeks + 2 * 53 weeks - 2004 and 2009 have 53 weeks). To me they are named oddly so that the first 52 variables run from c0101 to c0152 and the next variable is c0201 and so on ... up to c1252 - pondering about renaming the variables in a way that makes more sense if I were to use array. For every week to every individual there can be a status code else it is empty. The status codes I am out for are 1, 2 or 3. I want to know how many individuals reached 78 of the status codes 1,2 or 3 over the time period. But the twist is that there may also be defined a startdate (last column) for when it makes sense to count from. If there is no startdate then it should simply just starting counting from when it meets the first 1,2 or 3. Hope it makes sense! Regards

C_andrade · ‎11-03-2015

Hi, So this is about counting horizontally from different points for each ID. Each row represent an individual over 12 years in week increments (52 sometimes 53 weeks in the year). For every time (week) there is a match with the code 1, 2 or 3 it is to count for that ID, but it must be depending on the startdate if there is one - if there is no startdate it just has to be counting from the first time it meets a 1, 2 or 3. I want to know how many had 78 counts (for 78 weeks - but not for 78 consecutive weeks, gaps are allowed: that is missings in between or other classifications than 1, 2, 3) from when it starts counting. So variables go from c0101 to c1252 (that's a lot - shown below in short form) ... that means from the year 2001 and the 1st week to the year 2012 and the 52nd week. c0833 would be the 33rd week of year 2008. I have a million rows. ID c0101 c0102 c0103 c0104 ... c0833 ... c1252 Startdate 1 1 1 1 1 01JAN01 2 2 2 3 5 5 5 1 1 29MAY08 4 1 1 15MAR04 5 6 7 3 1 8 9 99 99 99 25APR03 10 8 8 11 12 17FEB06 13 14 1 1 1 15 16 I hope someone could give me an idea of what to use. Was thinking of temporary arrays, but there will need to be many of them...

C_andrade · ‎11-02-2015

Wow! So short and simple and amazingly fast! Thanks

C_andrade · ‎11-01-2015

ID Payment Startdate Enddate 1 100 1/1-2012 1/31-2012 1 100 2/1-2012 2/29-2012 1 100 3/1-2012 3/31-2012 1 120 4/1-2012 4/30-2012 1 120 5/1-2012 5/31-2012 1 80 6/1-2012 6/30-2012 1 90 7/1-2012 7/31-2012 1 100 8/1-2012 8/31-2012 1 100 9/1-2012 9/30-2012 1 100 10/1-2012 10/31-2012 1 100 11/1-2012 11/30-2012 1 200 12/1-2012 12/31-2012 2 50 3/3-2012 3/31-2012 2 55 4/2-2012 4/30-2012 2 55 5/1-2012 5/31-2012 2 25 8/1-2012 8/31-2012 3 17 5/1-2012 5/17-2012 3 13 5/5-2012 5/10-2012 3 18 5/20-2012 5/28-2012 3 20 6/1-2012 6/22-2012 3 55 6/17-2012 6/26-2012 3 10 6/28-2012 6/30-2012 Just to give you an idea of the data. 34 mio. records and main problem - so far - is the overlapping periods. So either... 1. some way of counting the days but taking care of overlapping days of employment. So ID 3 would have 26 days in may and not 31 days, 29 days in june and not 35... Or 2. some way of collapsing records with consecutive days into on observation either on a) month basis or b) year basis: So ID 1 would be unleft or have a period running from 1/1-2012 to 12/31-2012. ID 3 then: 3 17 5/1-2012 5/17-2012 3 18 5/20-2012 5/28-2012 3 20 6/1-2012 6/26-2012 3 10 6/28-2012 6/30-2012

C_andrade · ‎11-01-2015

Hi LinusH, Thank you for your answer. First solution sounds like something that is workable. So if I understand it correctly you suggest finding all workperiods of consecutive days. An individual with 12 records may end up with 3 records that consist of exactly the days covering the year he was in employment indicated by a startdate and a enddate for that period? Sounds a bit tricky to code. Second solution sound like the macro approach, but without a macro, right? But that would give me 365 outputs? Not sure with using SQL to summing up for each ID, would that be faster than having SAS do it?

C_andrade · ‎11-01-2015

Hi PaigeMiller, Thank you for your answer! The days_worked=end_date–start_date+1 I already did. That was in fact my first and earliest go actually and I have the code giving me the variable of the number of days in the period for every observation, but problem comes up for individuals who have worked in overlapping periods. Say, someone worked 2/1 to 2/10, but also worked 2/5 to 2/15. That would give 10 + 10 days, but he really only worked for 15 days that month. And that is what I am interested in: how many days of the month in question is he actually employed. But maybe I could return to this approach if I could somehow collapse the two records with days overlapping to one that says 1/2 to 15/2. So for every ID (individuals) there exist overlapping time periods the earliest start day and latest end date will be the output. That would mean maybe going from 34 mio. to 30 mio. and then simply just counting the days.

C_andrade · ‎10-31-2015

Hi, first time posting here! 🙂 I don't know if this is the right location for the post... I have a dataset of 34 mio. records on a monthly basis. For every record there is ID, payment, startdate and enddate. There are 3 mio. unique ID's. Startdates usually go from e.g. 1/1 to 31/1, 5/1 to 5/30 and the like and cover for what period the individual was paid. So an individual who was employed all year will have 12 records, one for every month and the dates will all in all cover the whole year. Some individuals can have more than 12 because they had jobs on the side or multiple employments - and there may be overlaps. Some have less than 12 because they were unemployed for some time or for other reasons, but may still have overlaps. I would like to know for how many days in a year every indivdual was in employment. So it is about counting the days of every individual. My first solution was to create a dummy for every day of the year for every record. I made a macro in the form of: %macro days; %do i=1 %to 365; %let day=%eval(’1jan12’d + &i.) data want; set have; if startdate le day le enddate then v&i.=1; else v&i.=0; run; %end; %mend; %days; But this is very time consuming and inefficient. Going through 34 mio. records creating 365 new variables would be something like 2-3 hours if not more. Any ideas?

Online Status	Offline
Date Last Visited	‎10-05-2016 10:22 AM

Re: Counting number of weeks of matches with varying starting dates fo...

Re: Counting number of weeks of matches with varying starting dates fo...

Counting number of weeks of matches with varying starting dates for co...

Re: How many days of employment for every ID

Re: How many days of employment for every ID

Re: How many days of employment for every ID

Re: How many days of employment for every ID

How many days of employment for every ID

Re: How many days of employment for every ID

Re: Counting number of weeks of matches with varying starting dates fo...

Re: Counting number of weeks of matches with varying starting dates fo...

Counting number of weeks of matches with varying starting dates for co...

Re: How many days of employment for every ID

Re: How many days of employment for every ID

Re: How many days of employment for every ID

Re: How many days of employment for every ID

How many days of employment for every ID