Hello, I've a dataset sorted by patient ID (PID), diagnosis code (dx), doctor specialty code (specialty), and service date (date) in descending order, and doctor ID (docID). I'd like to identify patients seeking more than second opinion of doctor for the same diagnosis and from doctors with the same specialty -- that is, seeing a third doctor of the same specialty -- prior to the the most doctor visit. A patient might go back and forth to different doctors. The data is collected between January 1, 2011 and June 30, 2012. There is no date restriction except that the most recent visit within the same dx and specialty (or first record) should take place April 1, 2012. Here are the data and SAS code to read in the data (a fiscal year variable fyear is calculated (going by April 1 as the start of a fiscal year): data dat; input PiD 1 Date $ 3-10 DocID 12-14 Fee_Code $ 16-19 Specialty 21-22 Dx 24-26 Amount 28-30 Notes $ 32-76; datalines; 1 20120501 101 A585 28 289  60 1st doc w/n same PID, specialty code, dx code 1 20120322 101 A585 28 289  60 1st doc 1 20120313 102 A585 28 289  60 2nd doc 1 20120306 101 A585 28 289  60 1st doc 1 20110830 111 A585 28 300  60 3rd doc w/n same PID, specialty code, dx code 1 20110524 101 A585 28 289  60 1 20110420 101 A585 28 289  60 1 20110411 101 A585 28 289  60 1 20110101 101 A585 28 289  60 1 20120515 104 A401 40 110 100 1st doc w/n same PID, specialty code, dx code 1 20120303 105 A401 40 110 100 2nd doc 1 20120101 104 A401 40 110 100 1st doc 2 20120101 101 A303 15 305  30 1st doc but 1st record date < April 1, 2012 2 20111130 105 A303 15 305  50 2nd doc 2 20111009 108 A303 15 305  30 3rd doc 2 20110707 103 A303 15 305  30 3 20120415 201 A220 30 211 116 1st doc w/n same PID, specialty code, dx code 3 20120401 205 A220 30 211 116 2nd doc 3 20120303 201 A220 30 211 116 1st doc 3 20111231 205 A220 30 211 116 2nd doc ; data dat (rename=(Date2=Date)); format PID Date2 Fyear; set dat;     format Date2 yymmdd10.;     Date2=input(date,yymmdd10.);     fyear = year(intnx("YEAR.4",Date2,0));     drop date; run; (The dataset "dat" is attached as well). Based on the above mentioned criteria, only PID=1 and specialty=28 and DX=289 should be outputted because there is a third visit to DocID=111 on Aug 30, 2011. PID=1, speciatlty=40, DX=110: there are 3 visits but only to 2 different doctors PID=2: there are 4 visits to 3 different doctors but the most recent visit took place in January 2010, which is fiscal year 2011 (< April 1, 2012) PID=3: there are 4 visits but to 2 different doctors So thte data output should look like: PiD Specialty Dx 1 28 289 I assume I should carry docID to the next record to compare and see if they are identical, but I need to compare not just the preceding one but all the preceding ones. I could transpose and compare docID1, docID2, docID3....., but I could have many records due to many visit entries (not manageable, just not preferable if I had other solutions). Thanks in advance for any help you could provide. ... View more

Hi, I grew to like using proc tabulate for output tables, but sometimes it's hard to follow the logic. Say if I want to run a crosstab of health condition by sex (so column %) for each year and each country. I tried the following but it's giving me the column percent across all cases, not within a country and for each year. How do I change the? I didn't make a sample data but if needed, I could do it. proc tabulate data=have;     class country year heath_status sex;     table (country year _status), sex* COLPCTN;     run; Many thanks. ... View more

Hello, Hope someone can help with this coding data step. I've a data set for 3 years of data (2009-2011) and it's aggregated by ID (just a combination of area, sex and AgeGroup) for visits, cases and amount. However not all IDs have the data for each year and I need to insert a line for the missing years - with repeating info ID, area, sex, and age group and assigning 0 for the variables visits, cases, and amount. I think I need to use retain to do it but got stuck. So much appreciated for any help from the experts on the forum. data data; input ID $ Year Area $ Area_desc $ sex $ AgeGroup $ AgeGroup_desc $ Visits Cases Amount ; datalines; 0100M 2009 01 Area1 M 00 00     2 2 109 0103M 2009 01 Area1 M 03 10-14     1 1 365 0103M 2010 01 Area1 M 03 10-14     1 1 365 0103M 2011 01 Area1 M 03 10-14     1 1 362 0104F 2011 01 Area1 F 04 15-19     1 1 13 0104M 2010 01 Area1 M 04 15-19     1 2 53 ; Desired output (I added a line 0100M 2009 01 Area1 M 00 00     2 2 109 0100M 2010 01 Area1 M 00 00     0 0  0 0100M 2011 01 Area1 M 00 00     0 0  0 0103M 2009 01 Area1 M 03 10-14     1 1 365 0103M 2010 01 Area1 M 03 10-14     1 1 365 0103M 2011 01 Area1 M 03 10-14     1 1 362 0104F 2009 01 Area1 F 04 15-19     0 0 0 0104F 2010 01 Area1 F 04 15-19     0 0 0 0104F 2011 01 Area1 F 04 15-19     1 1 13 0104M 2009 01 Area1 M 04 15-19     0 0 0 0104M 2010 01 Area1 M 04 15-19     1 2 53 0104M 2011 01 Area1 M 04 15-19     0 0 0 ; ... View more

Hi, Can I have inner join and left join in one step in PROC SQL, or I've to do in 2 steps or do with subquery? Here is an example. 1. I want ID in both tables A and B (so just ID=1 and 2), so it's inner join (in data step it's in A and B) 2. I then want to link the cases to table C to get their breakfast category. Since it's likely the category in C is not complete, I'd need to use left join to keep records (i.e. mushrrom) from step 1 (in data step it's in A). data a; input id x1; datalines; 1 11 2 22 ; data b; input id x2 breakfast $; datalines; 1 1 apple 2 1 carrot 2 1 mushroom 3 1 pepper ; data c; input breakfast $ category $; datalines; apple  fruit orange fruit carrot  vegetable pepper vegetable ; Data wanted: ID  breakfast    category 1    apple           fruit 2    carrot          vegetable 2    mushroom    . SAS code in 2 steps: proc sql; create table d as     select  a.id, b.breakfast        from a as a, b as b     where a.id=b.id;     select  d.id, d.breakfast, c.category        from d as d left join c as c     on d.breakfast=c.breakfast; quit; proc print data=want noobs; run; A related question is can I do inner join (in A and B) and in A but not C in one step? I read that EXCEPT can do the job, but when I copied the example code, it didn't seem right. For the fruit example above, the data set should look like   ID  breakfast    category    2   mushroom  . Many thanks in advance. ... View more

I've a data step question. Any help is appreciated. Basically I've a dataset with ID, entry date and exit date which can last as short as several days up to several years, and it can cut across two year points. What I like to do is allocate the days into yearly periods (say 5 days in year 1, 365 in year 2 and 100 days in year 3, for a total of 470 days). For the yearly period, I'd like to try April 1-March 31 as the cycle - I call it fiscal year. Say I've days from May 1 to May 9, 2008, then it's 8 days for 2008. If I've March 1 to April 15 for 2008 (total 45 days), then  I've 30 days for 2007 (31-1) and 15 for 2008 (I can't use 15 minus 1 because it's be short of 1 day. It's fine if it's 31 days for 2007 and 14 days for 2008 as long as the total is still 45 days). I tried the stupidest way as follows doing it by single year. But I ran into problems. 1. I tried to use macro but it didn't take yr1 and yr2 in the date condition (e.g. "31mar&yr2." "01apr&yr1.". 2. Most of all, the END-Start+1 for each year will give an extra day when I sum up all day variables for an ID.     To get around it I could tell SAS to find the variable that has the first days value and subtract 1 from it. But how do I do it? Someone gave me an idea about subtracting the days for partial year (the beginning or ending of the year or both) and leave everything in between as 365 or 366 days, etc. But it doesn't seem feasible. I thought I could use DO startyear TO endyear (along with function like year(intnx("YEAR.4",startdate,0)) to get around with the fiscal year). But how do I tell SAS to break the days by specific dates (say March 31 of each year). (If it's by every 365 days it's be earlier) Here are the data and my clumsy SAS code. The desired output is at the end. BTW, if the exit date is missing, I set to Mar 31 of 2012 as its the end of the study period. Many thanks. data test; format id best. entrydate exitdate yymmdd10.; input id @3 entrydate yymmdd10. @14  exitdate yymmdd10.; datalines; 1 2010-09-22    2 2008-09-22 2008-10-03 3 2007-03-19 2011-12-14 ; run; proc print; run; data want;     keep id entrydate exitdate start end days:;     set test ;     format start end yymmdd10.;     If entrydate < '1Apr2007'd and (exitdate GE '01Apr2006'd or exitdate = .) then do;          If exitdate = . then exitdate2 = '31Mar2007'd;         start = MAX('01Apr2006'd, entrydate);         end = MIN(exitdate, '31Mar2007'd);         days2006=end-start+1;      end;     If entrydate < '1Apr2008'd and (exitdate GE '01Apr2007'd or exitdate = .) then do;          If exitdate = . then exitdate2 = '31Mar2008'd;         start = MAX('01Apr2007'd, entrydate);         end = MIN(exitdate, '31Mar2008'd);         days2007=end-start+1;      end;     If entrydate < '1Apr2009'd and (exitdate GE '01Apr2008'd or exitdate = .) then do;          If exitdate = . then exitdate2 = '31Mar2009'd;         start = MAX('01Apr2008'd, entrydate);         end = MIN(exitdate, '31Mar2009'd);         days2008=end-start+1;      end;     If entrydate < '1Apr2010'd and (exitdate GE '01Apr2009'd or exitdate = .) then do;          If exitdate = . then exitdate2 = '31Mar2010'd;         start = MAX('01Apr2009'd, entrydate);         end = MIN(exitdate, '31Mar2010'd);         days2009=end-start+1;      end;     If entrydate < '1Apr2011'd and (exitdate GE '01Apr2010'd or exitdate = .) then do;          If exitdate = . then exitdate2 = '31Mar2011'd;         start = MAX('01Apr2010'd, entrydate);         end = MIN(exitdate, '31Mar2011'd);         days2010=end-start+1;      end;     If entrydate < '1Apr2012'd and (exitdate GE '01Apr2011'd or exitdate = .) then do;          If exitdate = . then exitdate2 = '31Mar2012'd;         start = MAX('01Apr2011'd, entrydate);         end = MIN(exitdate, '31Mar2012'd);         days2011=end-start+1;      end; run; proc print; run; /* Actual output ID    Entrydate    Exitdate                 FY2006  Fy2007  FY2008  FY2009  FY2010 FY2011 1    2010-09-22                                                                                         191     366 2    2008-09-22    2008-10-03                                              12            3    2007-03-19    2011-12-14                13         366           365     365        365      258 */ /* Desired output - I throw in LOS, just for reference and if exit date is missing, I set at Mar 31 2012: ID    Entrydate    Exitdate         LOS  FY2006  Fy2007  FY2008  FY2009  FY2010 FY2011 1    2010-09-22                         566                                                            190     366 2    2008-09-22    2008-10-03                                              11            3    2007-03-19    2011-12-14    1731     12         366           365      365         365     258 */ ... View more