I am on version 9.4 . ... View more

Thanks Patrick. It doesn't look like my post went through. I tried your code but I am getting an error message. It doesn't seem to like the next() function. Can you see any other reason it might be getting that message?     2602 proc format; 2603 invalue color_interval (just upcase) 2604 'RED' = 45 2605 other = 30 2606 ; NOTE: Informat COLOR_INTERVAL is already on the library WORK.FORMATS. NOTE: Informat COLOR_INTERVAL has been output. 2607 quit; NOTE: PROCEDURE FORMAT used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 2608 2609 data want(drop=_:); 2610 set carc.colors; 2611 by id date; 2612 length Combo $ 400; 2613 2614 if _n_=1 then 2615 do; 2616 2617 if 0 then _color=color; 2618 format _date date9.; call missing(_date); 2619 dcl hash h1_colors(multidata:'n', ordered:'y', hashexp:4); 2620 dcl hiter hh1_colors('h1_colors'); 2621 h1_colors.defineKey('id','color'); 2622 h1_colors.defineData('_color','_date'); 2623 h1_colors.defineDone(); 2624 2625 dcl hash h1_rowsSameDate(multidata:'y', dataset:"carc.colors(obs=1)", hashexp:2); 2626 h1_rowsSameDate.defineKey('id','date'); 2627 h1_rowsSameDate.defineData(all:'y'); 2628 h1_rowsSameDate.defineDone(); 2629 2630 end; 2631 2632 /* new id: clear hash h1_colors() */ 2633 if first.id then 2634 h1_colors.clear(); 2635 2636 /* new date: clear hash h1_rowsSameDate() */ 2637 if first.date then 2638 h1_rowsSameDate.clear(); 2639 2640 /* keep hashes up to date if required */ 2641 h1_colors.replace(key:id, key:color, data:color, data:date); 2642 if not first.date or not last.date then h1_rowsSameDate.add(); 2643 2644 /* populate combo */ 2645 if last.date then 2646 do; 2647 _rc = hh1_colors.first(); 2648 do while (_rc = 0); 2649 if intck('day',_date,date)<=input(color,color_interval.) then 2649! combo=catx('-',combo,_color); 2650 else h1_colors.delete(); 2651 _rc = hh1_colors.next(); 2652 end; 2653 end; 2654 2655 if first.date and last.date then 2656 /* only 1 color for the date */ 2657 do; 2658 output; 2659 end; 2660 else if last.date then 2661 do; 2662 /* multiple colors for the date */ 2663 h1_rowsSameDate.reset_dup(); 2664 do while(h1_rowsSameDate.do_over() eq 0); 2665 output; 2666 end; 2667 end; 2668 2669 run; NOTE: There were 1 observations read from the data set CARC.COLORS. ERROR: Invalid hash object for iterator operation at line 2651 column 15. ERROR: DATA STEP Component Object failure. Aborted during the EXECUTION phase. NOTE: The SAS System stopped processing this step because of errors. NOTE: There were 226 observations read from the data set CARC.COLORS. WARNING: The data set WORK.WANT may be incomplete. When this step was stopped there were 224 observations and 4 variables. WARNING: Data set WORK.WANT was not replaced because this step was stopped. NOTE: DATA statement used (Total process time): real time 0.24 seconds cpu time 0.07 seconds       ... View more

thank you. I definitely need work on Hash objects and I've been reading up on hash and thought I understood them as lookup tables but haven't used it like this as placeholders for data of the current dataset.    I am closer to what I need. In a couple of cases colors don't seem to be able to re-enter, especially when it is on the same day as one or  more colors. I have large dataset and for the most part the code works even though it's not getting them all right. At this point I think I could have data entered all of them!! I'm going to have to cut my losses soon but I've learned alot and appreciate your help.    Thanks again. ... View more

@mkeintz Hi Mark, I have been asked to revise the program so that color red drops if the prior entrance into the dataset is more than 45 days while all other colors drop after 30 days. I tried revising the data but red drops when it is the same date as another color (I am grouping by id and date so that the newcombo is the same for each drug entered on the same day).    if index(color,"red") > 0 then do ;                                                   if date-start>45 then colors{d}=' ' ;                                                end; else if date-start > 30 then colors{d}=' ';   Any thoughts on why this wouldn't work ?   So, I think I realized the problem. I had color=current_color; commented out which seemed to have caused the problem. the above condition seems to work as well.  I guess that the hash table overwrites color and it needs to assign it back . Not sure how/why but will try to step through the pdv to see how the hash is working. ... View more

Ok, so it looks like I needed to add  'or first.id' to the statement that looks for a new color .    /* If it's a new color, add it to beginning of colors array, which will be blank */ if findw(trim(newcombo),trim(color),'-')=0 or first.id then colors{1}=color;     here is the whole code which seems to work with my drug data as well. Thank you so much!!!!   data have ; informat date mmddyy10.; input id $ date color $ expectedCombo $; format date mmddyy10.; datalines; 1321 07/10/2015 blue blue 1321 07/11/2015 blue blue 1321 07/12/2015 blue blue 1321 07/13/2015 blue blue 1321 08/18/2015 red blue-red 1321 08/19/2015 blue blue-red 1321 08/20/2015 blue blue-red 1321 08/21/2015 blue blue-red 1321 08/22/2015 blue blue-red 1321 09/15/2015 red blue-red 1321 09/16/2015 blue blue-red 1321 09/17/2015 blue blue-red 1321 09/18/2015 blue blue-red 1321 01/12/2016 red blue-red 1321 01/12/2016 blue blue-red 1321 01/13/2016 blue blue-red 1321 01/14/2016 blue blue-red 1321 01/15/2016 blue blue-red 1321 01/16/2016 blue blue-red 1321 01/17/2016 blue blue-red 1321 01/18/2016 blue blue-red 1321 01/19/2016 green blue-red-green 1321 01/19/2016 blue blue-red-green 1321 01/20/2016 blue blue-red-green 1321 01/21/2016 blue blue-red-green 1321 01/22/2016 blue blue-red-green 1321 01/23/2016 blue blue-red-green 1321 01/24/2016 blue blue-red-green 1321 01/25/2016 blue blue-red-green 1325 01/16/2016 blue blue-red 1325 01/17/2016 blue blue-red 1325 01/18/2016 blue blue-red 1325 01/19/2016 green blue-red-green 1325 01/19/2016 blue blue-red-green 1325 01/20/2016 blue blue-red-green 1325 01/21/2016 blue blue-red-green 1325 01/22/2016 blue blue-red-green 1325 01/23/2016 blue blue-red-green 1325 01/24/2016 blue blue-red-green 1325 01/25/2016 blue blue-red-green ; data want; if _n_=1 then do; if 0 then set have; attrib start format=mmddyy10. length=8; declare hash history(ordered:'a'); history.definekey('color'); history.definedata('color','start'); history.definedone(); end; array colors{20} $20 _temporary_; attrib newcombo length=$400; retain newcombo; do until (last.date); set have; by id date; /* Update this color item in the history table */ rc=history.find(); start=date; rc=history.replace(); /* If it's a new color, add it to beginning of colors array, which will be blank */ if findw(trim(newcombo),trim(color),'-')=0 or first.id then colors{1}=color; /* Use hash table to see if any colors are over 30 days and should be removed*/ /* Start at right-hand end of array because it is sorted (therefore left end */ /* is blank except for the new color) */ current_color=color; do d=dim(colors) to 1 by -1 while(colors{d}^=' '); rc=history.find(key:colors{d}); if date-start>30 then colors{d}=' '; end; color=current_color; call sortc(of colors{*}); end; newcombo=catx('-',of colors{*}); do until (last.date) ; set have ; by id date ; output ; end; if last.id then do; rc=history.clear(); call missing(of colors{*}); end; run; proc print ; run;   ... View more

thanks so much. I ended up leaving the 'do until' there so that the first entry with the same date gets the same newcombo as the next color with the same date. I just added another do until loop at the end to output all the rows:   do until (last.date) ; set have ; by id date ; output ; end;     I am having one problem though if you duplicate some of the datalines and change the id, the first row of the new id has a blank newcombo. Any idea why this might be happening? I think it's carrying over the newcombo which is blank but it shouldn't do that. do you think newcombo should equal color before the first do until loop?   ... View more

This should be my last question. why is it necessary to change the color =current color? This is what orignally made me think that the original data will be changed. It doesn't seem to do anything in my real drug data but I'm not sure if I'm missing something.    current_color=color; do d=dim(colors) to 1 by -1 while(colors{d}^=' '); rc=history.find(key:colors{d}); if date-start>30 then colors{d}=' '; end; color=current_color; ... View more

also, how would I add a counter to keep track of each time the color combination changes?  ... View more

also observation 4 should be blue - red because both administered on the same day. That is 7/13/2015 should be blue-red in both instances.    oops - forget it. I was looking at the new 'start' variable in the print out. 1/12/2016 is giving the correct combination but it removed the 'red' row. As stated above I want to keep all rows and the original variable in tact.  ... View more

Thank you @Patrick and @mkeintz . This is very helpful. I have a few questions for @mkeintz :   1) what is the purpose of current_color? Right now the color variable gets cleared but replaced with current_color. I'd like to keep the original data.  2) I'd like to keep the same number of rows I started with even if the newcombo and date combination might be duplicate. This code seems to be giving me unique newcombo/date rows.    ... View more

I added to my code but still having problems. When the interval is >30 for a particular color then the color should not be concatenated.  Also, I'd like a way to figure out how to concatenate without hardcoding in the colors because it is possible for the real dataset to have over 25 different colors (or drugs in the real database).   If you are willing to take another look, here is the code which might answer some of your questions and I have attached a larger sample file.    proc sort data=colors; by id date ; run; data revised1 (drop=var8 octstart lanstart); set colors; by id date; format redStart greenStart date9. redInterval greenInterval 8. ; retain redStart greenStart redcounter 0 greencounter 0 redinterval greeninterval; if color='red' then do ; if redcounter>0 then redInterval=date - redStart ; redStart=date ; redcounter+1 ; end ; if color='green' then do ; if greencounter>0 then greenInterval=date - greenStart ; greenStart=date ; greencounter+1 ; end ; if last.id then do ; redStart=. ; greenStart=. ; end; redInterval=redInterval ; greenInterval=greenInterval ; run; proc print; run; PROC SORT DATA=REVISED1 ; BY ID Date ; RUN; /*concatenate for colors entered on the same date*/ data revised2 ; length newCombo $400 ; do until (last.date) ; set revised1 ; by id date ; if newCombo="" then newCombo=color; else newCombo = Catx(" - ",newcombo, color) ; end; do until (last.date) ; set revised1 ; by id date ; /*CONTINUE newCombo BASED ON PRIOR CONCATENATED newCombo IF THE INTERVAL IS > 30 FOR TESTING PURPOSES (VALUE MAY CHANGE TO 45 )*/ /*PROBLEM WITH THIS IS THAT THE Color NEEDS TO BE HARDCODED IN FOR EACH DRUG and create a variable in order to incorporare all colors*/ /*Can this be done without knowing all the colors that might show up in the database? */ if index(newcombo, 'red') = 0 and (redcounter > 0 OR 0 < redinterval < 30) then newCombo2=Catx(" - ",newcombo, 'red') ; else newcombo2=newcombo ; if index(newCombo2, 'green') = 0 and (greencounter > 0 OR 0 < greeninterval < 30) then newCombo3=Catx(" - ",newCombo2, 'green') ; else newcombo3=newCombo2; output; end; run; /*alphabetize combos*/ Data revised3 ; set revised2; length finalCombo $400; /*create an array to store each separate drug*/ array colorlist[30] $50 _temporary_; /*initialize all array elements to missing*/ call missing(of colorList[*]); /*parse the colorList and place into array elements*/ do i = 1 to dim(colorList) until(p eq 0); call scan(newcombo3,i,p,l,"-"); /*p is the position of word returned; l is the length of the word returned*/ /*account for legitimate space between color names such as light blue*/ if p=1 then colorList[i] = substrn(newcombo3,p,l-1); else colorList[i] = substrn(newcombo3,p+1,l-1); /*substrn is like substr but can return a length of 0 needed for the until clause*/ end; /*sort array elements*/ call sortc(of colorList[*]);   /*Remove duplicate colorList */ do w=1 to dim(colorList); do x=1 to dim(colorList); /*IF logic to determine if the values are the same and if so, set the repeat value to missing*/ if w ne x and colorList(x)=colorList(w) then colorList(x)=' '; end; end; /*this section of the code checks to see if a value is missing and if so, shifts the non-missing values to the left*/ do y=1 to dim(colorList) ; if colorList[y] = '' then do; do z= y+1 to dim(colorList); if colorList[z] ne '' then do; colorList[y] = colorList[z]; colorList[z] = ''; leave; end; end; end; end; finalCombo = catx(" - ",of colorList[*]); drop i p l w x y z ; run; proc print ; run;   ... View more

Thank you. The only data I have to work with are the id, drug name/color and the date of administration. I included the expected column in the sample data to show what I was looking for. I calculated the the days between entry of the red color (which could be a specific drug name) which I wanted to use to determine the length of time between drug/color administrations which might help to determine when to stop the concatenation.  ... View more

Thank you. Yes that helps with the date but I added informats to the other variables.    data have ; informat id 8. date mmddyy10. color $10. expectedcombo $400.; infile datalines dsd ; input id date color $ expectedCombo $; format date mmddyy10. color $10. expectedCombo $400.; datalines   Also, if it will help understand what I want to do you can assume that the colors are actually drug names and the date is the date the drug was administered. If the drugs were taken on the same day then I want one field that shows all the drugs taken at the same time. Since the newly added drug/color will continue but is taken once every month or so then it should still be considered a concurrrent drug. This is why I want to extend the concatenation of the prior concatenation down. Once I can figure how to do that I will add a condition so that if it reaches the drug/color with an interval >30 then the concatenation stops since it is likely that drug is not longer being administered.  Hopefully that helps and doesn't confuse the issue.    Thanks     ... View more