Hello, I have made a similar query earlier, but since the topic has been closed off, i have to restart again as I found even more complications. DATA: The input data is unsorted and hence I am using hash tables to take the input data, do some iterations, sort and then output. What I want: I am trying to enumerate a table variable "answer" with binary values (0/1) if variable filter = "Y" for the next 6 month observations with the same client. In some instances, the client is missing from some monthly observations eg: client FG5151 is missing from September and October 2006. In short if variable filter "Y" then this observation and the next 6 months observations for same client should be assigned variable "answer" eq 1, else 0. Also, I would like to highlight which end of month observations the client is missing (table : WANT2). eg: for client FG5151, i should have a table noting what months the client is missing. data have; input client $ dates date9. filter $; datalines ; Fg5151 28.Feb.06 N Fg5151 31.Mar.06 N Fg5151 30.Apr.06 N Fg5151 31.May.06 Y Fg5151 30.Jun.06 N Fg5151 31.Jul.06 Y Fg5151 31.Aug.06 N Fg5151 30.Nov.06 N Fg5151 31.Dec.06 N Fg5151 31.Jan.07 N A101 28.Feb.06 N A101 31.Mar.06 N A101 30.Apr.06 Y A101 31.May.06 N A101 30.Jun.06 N A101 31.Jul.06 N ABC123 31.Mar.06 N ; data want; input client $ dates date9. filter $ answer $; datalines ; A101 28.Feb.06 N 0 A101 31.Mar.06 N 0 A101 30.Apr.06 Y 1 A101 31.May.06 N 1 A101 30.Jun.06 N 1 A101 31.Jul.06 N 1 ABC123 31.Mar.06 N 0 Fg5151 28.Feb.06 N 0 Fg5151 31.Mar.06 N 0 Fg5151 30.Apr.06 N 0 Fg5151 31.May.06 Y 1 Fg5151 30.Jun.06 N 1 Fg5151 31.Jul.06 Y 1 Fg5151 31.Aug.06 N 1 Fg5151 30.Nov.06 N 1 Fg5151 31.Dec.06 N 1 Fg5151 31.Jan.07 N 0 ; data want2; input client $ dates date9. filter $; datalines ; Fg5151 30.Sep.06 N 1 Fg5151 30.Oct.06 N 1 ; User RW9 had solved a similar issue earlier, but by counting the number of observations instead of months:   data want; set have; retain answer c; if _n_=1 or lag(client) ne client then do; answer=0; c=0; end; if filter="Y" then do; call symput('xdate',dates); answer=1; c=1; end; else if answer=1 then c=c+1; if (intnx("month",dates,6,"same")) then do; answer=0; c=0; end; run;     My brain is totally fried from trying a solution. I would be really grateful if someone could help me out in this endeavour. thanks. sebastian ... View more

Hello, I hope you guys are well. DATA: The data is unsorted and hence I am using hash tables. Sorting this table would be a time-consuming effort. If there is no other option, then I will need to sit down for the gruesome sorting approach. What I want: I am trying to enumerate a table variable "answer" with binary values (0/1) if variable filter = "Y" for the next 6 observations with the same client. In short if variable filter "Y" then this observation and the next 5 observations for same client should be assigned variable "answer" eq 1, else 0.   I have presented the "have" and "want" data below: data have; input client $ dates date9. filter $; datalines ; Fg5151 28.Feb.06 N Fg5151 31.Mar.06 N Fg5151 30.Apr.06 N Fg5151 31.May.06 Y Fg5151 30.Jun.06 N Fg5151 31.Jul.06 Y Fg5151 31.Aug.06 N Fg5151 30.Sep.06 N Fg5151 31.Oct.06 N Fg5151 30.Nov.06 N Fg5151 31.Dec.06 N Fg5151 01.Jan.07 N A101 28.Feb.06 N A101 31.Mar.06 N A101 30.Apr.06 Y A101 31.May.06 N A101 30.Jun.06 N A101 31.Jul.06 N ABC123 31.Mar.06 N ; data want; input client $ dates date9. filter $ answer 8; datalines ; A101 28.Feb.06 N 0 A101 31.Mar.06 N 0 A101 30.Apr.06 Y 1 A101 31.May.06 N 1 A101 30.Jun.06 N 1 A101 31.Jul.06 N 1 ABC123 31.Mar.06 N 0 Fg5151 28.Feb.06 N 0 Fg5151 31.Mar.06 N 0 Fg5151 30.Apr.06 N 0 Fg5151 31.May.06 Y 1 Fg5151 30.Jun.06 N 1 Fg5151 31.Jul.06 Y 1 Fg5151 31.Aug.06 N 1 Fg5151 30.Sep.06 N 1 Fg5151 31.Oct.06 N 1 Fg5151 30.Nov.06 N 1 Fg5151 31.Dec.06 N 1 Fg5151 01.Jan.07 N 0 ;     I have compiled the HASH code below - The code works, but variable "answer" provides unsatisfactory results: data _null_; set have end=last; retain counter; if _n_ = 1 then do; length newdate 8 answer 8; format newdate ddmmyy10.; declare hash hs(ordered: "Y", hashexp: 9); hs.defineKey("client","dates"); hs.defineData("client","dates","filter","answer","newdate"); hs.defineDone(); end; rc = hs.find(); if rc ne 0 then do; if filter eq "Y" then do; counter+0; by CLIENT filter notsorted; if first.filter then do; answer=1; newdate=intnx('month',dates,6,"same"); end; end; hs.add(); end; else do; counter=0; end; if last eq 1 then do; hs.output(dataset: "want"); end; run; ... View more

Hi, I am trying to see if values from one row of a certain ID group exists in a row of another dataset by the same ID group. the IDs are unique. I have two tables: Table 1: Estimates ID ans1 ans2 ans3 ans4 A1 8 15 40 30 A2 9 10 11 46 A3 38 39 40 42 A4 5 25 35 41 Table 2: Reference   ID col1 col2 col3 col4 A1 5 15 20 40 A2 11 12 13 37 A3 38 39 40 42 A4 5 25 35 41     I would like to get the following table (0 marks the point where the values match) eg: the code should check for all variables (where ID=A1) in table1: estimates match against all variables in table 2 (ID=A1): reference. If any value in table 1: estimates match with the same ID from table 2: then the matched variable, should be marked with 0. Table: want mentioned below: (the 0 means that the values match) ID match1 match2 match3 match4 A1 1 0 0 1 A2 1 1 0 1 A3 0 0 0 0 A4 0 0 0 0     I have adoped this code from a similar code written by Kshap earlier, but could not change it to suit my needs. data reference; input id $ col1 col2 col3 col4; datalines; A1 5 15 20 40 A2 11 12 13 37 A3 38 39 40 42 A4 5 25 35 41 ; data estimates; input id $ ans1 ans2 ans3 ans4; datalines; A1 8 15 40 30 A2 9 10 11 46 A3 38 39 40 42 A4 5 25 35 41 ; data hash(keep=id val); set reference; array c{*} col: ; do i=1 to dim(c); val=c{i};if not missing(val) then output; end; run; proc sql ; select distinct id into : list separated by ' ' from reference; quit; data want (drop=i j k rc val id); if _n_ eq 1 then do; if 0 then set hash; declare hash ha(dataset:'hash'); ha.definekey('val'); ha.definedata('id'); ha.definedone(); end; set estimates; by id; array g{*} &list.; array v{*} ans: ; do k=1 to dim(g); g{k}=0; end; do i=1 to dim(v); val=v{i}; rc=ha.find(); if rc=0 then do; do j=1 to dim(g); if id=vname(g{j}) then g{j}=1; end; end; end; run; Please help me.   Many thanks. regards, Sebastian ... View more

Hello All, I am trying to create a list where a certain string is deleted from a string list. for example: considering the third row in the table below where year=2007M,2011M, the value 2007M,2011M should be deleted from year_list and with end result displayed in variable "want" being "2008,2009,2010". for this same example, the start should be 2007M, as that is the first observation in variable "year". THe FINAL table that i want should be as follows: year year_list want 2005M 2005M,2006,2007M,2008,2009,2010,2011M 2006,2007M,2008,2009,2010,2011M 2005M,2007M 2005M,2006,2007M,2008,2009,2010,2011M 2006,2008,2009,2010,2011M 2007M,2011M 2005M,2006,2007M,2008,2009,2010,2011M 2008,2009,2010 I tried arrays to solve this problem, but not able to get anywhere close to the first step. I list below my code: proc sql; create table have (year VARCHAR(100), year_list VARCHAR(100)); insert into have values('2005M','2005M,2006,2007M,2008,2009,2010,2011M'); insert into have values('2005M,2007M','2005M,2006,2007M,2008,2009,2010,2011M'); insert into have values('2007M,2011M','2005M,2006,2007M,2008,2009,2010,2011M'); run; data trial (keep=x); set have(obs=1); cnt=countw(year_list,","); i=0; do while (i<cnt); i+1; x=cats("dlv",scan(strip(year_list),i,",")); output; end; run; proc sql ; select distinct cats("mart",max(countw(year,","))), max(countw(year,",")) into :arraylist,:arraycnt from have; select distinct cats("dlv",max(countw(year_list,","))), max(countw(year_list,",")) into :dlv_array, :dlvcnt from have; select distinct x into :array_list separated by ' ' from trial; run; data want ; set have; length &array_list. $100.; array dlv(&dlvcnt.) $ &array_list.; do _j=1 to dim(dlv); if index(upcase(vname(dlv(_j))),upcase(cats("DLV",dlv[_j]))) then do; dlv[_j]=scan(year,_j,','); end; end; run;   The array code in the last data step does not display the result i want for the first step of arriving to the final table. The table should look more like: year year_list dlv2005M dlv2006 dlv2007M dlv2008 dlv2009 dlv2010 dlv2011M 2005M 2005M,2006,2007M,2008,2009,2010,2011M 2005M             2005M,2007M 2005M,2006,2007M,2008,2009,2010,2011M 2005M   2007M         2007M,2011M 2005M,2006,2007M,2008,2009,2010,2011M     2007M       2011M After getting this table, I look at finding all missing values in the table and listing these out. Still left with the dilemma of how to determine the first observation 😞 Any pointers, help, tip would be appreciated. thanking you ... View more