Hello, Using Arthur's data example and a hash solution: proc sort data=have;   by id trt days; run; data want; if 0 then set have; if _n_=1 then do;   declare hash a(multidata: 'Y');   a.definekey('id', 'trt');   a.definedone(); end; set have end=last; by id trt; if dosmod ne 'REDUCTION' then count=0; else if count<3 then count+1; if count >= 3 then a.add(); if last then do until(new_last); set have end=new_last; if a.find()=0 then output; end; drop count; run; ... View more

Hello, One solution: data master; input POLICY_NR INVOICE_NR AMOUNT_TYPE $ STATUS $; datalines; 8521 12345 ECO O 8521 12345 FDM O 8521 4567 ECO C 8521 4567 FDM C 8522 4567 FDM C 8523 4567 FDM C ; data updatee; input POLICY_NR AMOUNT_TYPE $ AMOUNT ; datalines; 8521 D_COST 100 8522 D_COST 100 ; proc sort data=master; by POLICY_NR INVOICE_NR; run; data want; set master; length amount 8; if _n_=1 then do;   call missing(amount);   declare hash u(dataset:"updatee");   u.definekey('POLICY_NR');   u.definedata('AMOUNT_TYPE','AMOUNT');   u.definedone();   end; by POLICY_NR INVOICE_NR; if last.INVOICE_NR then do; output; if u.find() eq 0 then output; end; else output; run; ... View more

Hello, Continuing a little bit data _null_ explanation , when you have switched to FORMATTED INPUT , SAS reads from where the pointer is therefore it does not scan for the next value as the case when reading data with LIST INPUT. You may find these links useful: http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a003209907.htm http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000144370.htm ... View more

Hello, You can first fill-in the missing series and afterwards concatenate the datasets: data dataset1 (drop=date); input Code $ Date $ Count_2014; if missing(Date)=0 then date_num=mdy(input(substr(date,1,2),3.),input(substr(date,4,2),3.),2014); format date_num date7.; datalines; 1X1 07/01 1 1X1 07/03 1 1X1 07/06 0 2Y3 . 0 3J8 07/02 1 3J8 07/03 0 4J8 07/02 1 4J8 07/07 0 ; run; data dataset2 (drop=date); input Code $ Date $ Count_2012_2013; if missing(Date)=0 then date_num=mdy(input(substr(date,1,2),3.),input(substr(date,4,2),3.),2014); format date_num date7.; datalines; 1X1 07/01 0 1X1 07/02 1 1X1 07/04 0 2Y3 07/01 1 3J8 . 0 4J8 07/07 0 ; run; data have1; format date date7.; set dataset1; by Code; retain date; if first.Code then date="01JUL2014"d; do i=date to "07JUL2014"d while (date_num ne date); Observed_Count_2014=0; output; date+1; ; end; if date_num=date then do;Observed_Count_2014=count_2014;output;date+1;end; if last.Code and date_num not in(.,"07JUL2014"d)  and date le "07JUL2014"d then do; do until(date="08JUL2014"d) ;   Observed_Count_2014=0;   output;   date+1; end; end; keep code date Observed_Count_2014; run; data have2; format date date7.; set dataset2; by Code; retain date; if first.Code then date="01JUL2014"d; do i=date to "07JUL2014"d while (date_num ne date); Observed_Count_2012_2013=0; output; date+1; ; end; if date_num=date then do;Observed_Count_2012_2013=count_2012_2013;output;date+1;end; if last.Code and date_num not in(.,"07JUL2014"d)  and date le "07JUL2014"d then do; do until(date="08JUL2014"d) ;   Observed_Count_2012_2013=0;   output;   date+1; end; end; keep code date Observed_Count_2012_2013; run; proc sql; create table want as select h1.code,h1.date,Observed_Count_2014,Observed_Count_2012_2013 from have1 as h1 inner join have2 as h2 on h1.code=h2.code and h1.date=h2.date; quit; ... View more

hello, the sql returns CAPITAL LETTERS - YYMMDDN8. therefore you need to compare the same things. You have 2 options: 1) either change the macro -   %if (%upcase(&comp) ^= &fLength) %then %do; or 2) call the macro with capital letters for the last variable %check_format(&lib, &dsInOut, SMPLDATE, YYMMDDN8.); ... View more

Hello, data b; length nrn $ 20; if 0 then set a; if _n_=1 then do;   declare hash a(dataset:"a", multidata: 'Y');   a.definekey('nr');   a.definedata('n');   a.definedone(); end; set a; by nr; if first.nr then do;       if a.find() = 0 then do;     nrn=n;          a.has_next(result: r);          do while(r ne 0);              rc = a.find_next();              nrn=cats(nrn) || n;              a.has_next(result: r);          end;       end; output; end; run; ... View more

Hello, You can use the following macro statements: %if %symexist(cnt1) %then %do; %symdel cnt1; %put deleted macro variable cnt1; %end; ... View more