Great this is exactly what I need to do. Thank you! ... View more

Thank you, it works great! ... View more

There are no rules, but I know there will be no more than 5 values that can be the same.  I'm actually looking for the top 3 values, but extended it to 5 in order to pick up a 5 way tie. ... View more

Or If you like pure dataset code [pre] data i; E4CONT=1; E4_AGE=2; E4_DATE=3; E5CONT=1; E5_AGE=2; E5_DATE=3; E6CONT=1; E6_AGE=2; E6_DATE=3; run; data _null_; set sashelp.vcolumn(where=(libname='WORK' and memname='I')) end=last; if _n_ = 1 then call execute('proc datasets library=work nolist;modify i;rename '); call execute(strip(name)||'='||translate(name,'123','456')); if last then call execute(';quit;'); run; [/pre] Ksharp ... View more

I have run into one more unanticipated problem to deal with. There are some instances with one particular code that I only want the amt1 to be summed one time, but want all of amt2 to be summed when an even occurs on the same date. Here is an example using the same data of one id: id date code amt1 amt2 2 12/4/2001 300 20 14.25 2 12/4/2001 100 90 44.85 2 12/4/2001 100 90 15 2 12/8/2001 300 20 0 2 12/8/2001 300 20 14.25 2 12/8/2001 200 350 175 2 12/8/2001 200 350 15 2 12/7/2001 200 350 175 2 12/7/2001 200 350 15 2 12/7/2001 300 20 14.25 2 12/10/2001 300 20 0 2 12/10/2001 300 20 14.25 2 12/10/2001 200 350 189.25 2 12/10/2001 200 350 0.75 2 12/12/2001 300 1275 892.5 2 12/12/2001 300 130 125.4 2 12/15/2001 500 600 420 for the example in bold, I only want to sum one instance of amt1 that falls on the same date. So I would need the raw data to look like this: id date code amt1 amt2 2 12/4/2001 300 20 14.25 2 12/4/2001 100 90 44.85 2 12/4/2001 100 90 15 2 12/8/2001 300 20 0 2 12/8/2001 300 20 14.25 2 12/8/2001 200 350 175 2 12/8/2001 200 350 15 2 12/7/2001 200 350 175 2 12/7/2001 200 350 15 2 12/7/2001 300 20 14.25 2 12/10/2001 300 20 0 2 12/10/2001 300 20 14.25 2 12/10/2001 200 350 189.25 2 12/10/2001 200 0 0.75 2 12/12/2001 300 1275 892.5 2 12/12/2001 300 130 125.4 2 12/15/2001 500 600 420 I only need to do this for one code and I this is something I should do prior to creating the totals. ... View more

Thanks Peter, I will give this code a try. ... View more

Yes, thank you! ... View more

Thank you to Peter and Scott for your help. You both led me in the right direction. I ended up using arrays, but your suggestions helped me to get it right. ... View more

Ok, the variables are correctly ordered, but I do have gaps between the groups of 10 so the array won't work this way. I'm probably just better off using the previous code. Ideally, this would be better in long format, but I have to send this data to be cleaned in wide format to be read across for each observation. The data is time based and literally reads across for 120 minutes. ... View more

With an output FORMAT of datetime20. you are telling SAS to truncate at the second -- suggest you first try using BEST. as an output format and you may see "seconds since 1/1/1960". Then explore using DATETIME format specified as datetime20.3 instead. Scott Barry SBBWorks, Inc. Suggested Google advanced search argument, this topic / post: import stata dta datetime site:sas.com ... View more

Thanks to everyone for your suggestions. I finally figured out something last night before I had a chance to check back here. I just used a calculation. timeactive=(('24:00't-ltime)+ltime2)/60; ltime=the time before midnight ltime2= the time after midnight. I decided not to incorporate the dates since the time span should never be longer than 8 hours, so there will only be two days involved. I will consider datetime in the future if it will span more days. ... View more

You can try something like this (untested), it tries to deal properly with missing matches in dataset b : proc sql; create table temp as select a.*, b.examdate as otherDate, b.var1, b.var2,      abs(intck("DAY", a.examdate, b.examdate)) as sepDays from a left join b on a.id = b.id where calculated sepDays <= 30 order by a.id, a.examdate, sepDays; quit; data want(drop=sepDays); set temp; by id examdate; if first.examdate; run; PG ... View more

I believe the code below does what you're looking for: data _null_; format StartDate RandomDate date9.; StartDate=today(); DateRange=60; do i=1 to 20; /* RandomDate within range StartDate to Startdate+59 days */ RandomDate=Startdate+floor(ranuni(0)*DateRange); put RandomDate=; end; run; If you need to calculate the date range more dynamically then look at Scott's code. HTH Patrick ... View more

Array handling can consume a lot of processing (for larger data volumes) but i/o has always been the issue for smaller platforms. For the systems where cpu time is under pressure, techniques have developed to deal with this kind of data model in different ways. For example this pseudo code would mark all weeks of a TRT (treatment?) in one statement, rather than mark each week of treatment separately : clear buffer (of 150 weeks buffer= repeat( 'N', 149) ) do until last of customer/ID get next event data determine start week(e.g. int(stdate/7) ) determine end week (e.g. ceil(endDate/7) ) adjust start and end-week to be within the buffer( within the 150 weeks) determine duration of event ( endw-startw ) flag buffer at startWeek for duration[pre] substr( buffer, startW, trt_durn ) = repeat( 'Y', trt_durn ) ;[/pre] repeat until last of customer/id write buffer this could involve overlaying the memory area of an array of 150 one character elements when if the flags need to be treated as separate variables. I made no allowance for aligning week start to Sunday/Monday/startInterval, because I'm not sure how much impact it would have. The model would be simpler and could operate at the day-level and wrap-up weeks later, if the TRTdate spans less than 89 years (1jan2000 +max buffer of days=32767) = 17Sep2089 If the target platform for the data model is not cpu constrained, then the methods offered by Scott and Flip may be the best alternative. PeterC ... View more

Thanks Peter for helping me to understand. ... View more