PROC SUMMARY can do that directly (as long as the number of values per BY group is small enough). proc summary data=have ; by col_a; output out=want(drop=_type_ _freq_) idgroup(out[3] (col_b)=) min(col_c)=; run; If you don't know if 3 is the right number of new COL_B variables then count first and put the number into macro variable. proc sql noprint; select max(nobs) into :nobs trimmed from (select count(*) as nobs from have group by col_a) ; quit; proc summary data=have ; by col_a; output out=want(drop=_type_ _freq_) idgroup(out[&nobs] (col_b)=) min(col_c)=; run; Read this classic paper:  https://support.sas.com/resources/papers/proceedings10/102-2010.pdf       ... View more

The following code works based on the data provided. This assumes the data is sorted by client and uses by group processing and the lag function.   data have2; set have; by client; p_end = lag(end); p_start = lag(start); p_client = lag(client); p_account = lag(account); diff = abs(p_end-start); format p_start p_end date9.; if 0<= diff <=4 then do; client = p_client; account = p_account; start = p_start; end; if first.client =0 then delete; keep client account start end; run; ... View more

Thanks! ... View more

@nbora Re-posting the SQL I shared already earlier. data dummy_data; input col_date :date9. col_X $; format col_date date9.; datalines; 08Jun2013 X 11Jun2013 X 18Jun2013 X 30Jun2013 X 30Jun2013 X 14Jul2013 X 31Jul2013 X 31Aug2013 X 30Sep2013 X 10Oct2013 X 10Nov2013 X 30Nov2013 X 10Dec2013 X 31Dec2013 X 31Dec2013 X 31Dec2013 X 31Jan2014 X 12Feb2014 X 28Feb2014 X 31Mar2014 X 11Apr2014 X 10May2014 X 22May2014 X 31May2014 X ; PROC SQL; /* create table wd.output_table as*/ SELECT col_date, COUNT(*) as n_rows FROM dummy_data WHERE col_date = intnx('month',col_date,0,'e') group by col_date ; QUIT;     ... View more