topic split data based on conditions in SAS Programming

split data based on conditions

vraj1 — Thu, 01 Nov 2018 15:31:32 GMT

Probably i am asking the same question manytimes but i still face challenge in getting the result in another scenario.

data have;
  infile datalines dsd truncover;
  input id:32. num:32. std:DATE9. endt:DATE9.;
  format std endt yymmddd10.;
datalines4;
101,2,10MAY2016,15MAY2016
101,3,16MAY2016,22MAY2016
101,4,23MAY2016,05JUN2016
101,5,06JUN2016,19JUN2016
101,6,20JUN2016,20JUN2016
;;;;
run;

data want;
set have (rename=(std=_std endt=_endt));
by id;
retain obscount;
if first.id
then obscount = 0;
format std endt yymmddd10.;
std = _std;
endt = std + 1;
do while (obscount < 2 and endt < _endt);
  output;
  std = std + 2;
  endt = min(endt + 2,_endt);
  obscount + 1;
end;
if endt < _endt or std = _std
then do;
  endt = _endt;
  output;
  obscount + 1;
end;
drop obscount _std _endt;
run;

I am getting

data getting;
  infile datalines dsd truncover;
  input id:32. num:32. std:DATE9. endt:DATE9.;
  format std endt yymmddd10.;
datalines4;
101,2,10MAY2016,11MAY2016
101,2,12MAY2016,13MAY2016
101,3,16MAY2016,22MAY2016
101,4,23MAY2016,05JUN2016
101,5,06JUN2016,19JUN2016
101,6,20JUN2016,20JUN2016
;;;;
run;

In this case i am missing 14MAY2016 to 15MAY2016 and the endt from num=2 was 13MAY2016 and num=3 goes to 16MAY2016. can i get the missing(remainng ) date duration as the split should only be done for first 2 but in this case it is not showing the rest as the duration remaining for num=3 is 2.

data want;
infile datalines dsd truncover;
input id:32. num:32. std:DATE9. endt:DATE9.;
format std endt yymmddd10.;
datalines4;
101,2,10MAY2016,11MAY2016
101,2,12MAY2016,13MAY2016
101,3,14MAY2016,15MAY2016
101,3,16MAY2016,22MAY2016
101,4,23MAY2016,05JUN2016
101,5,06JUN2016,19JUN2016
101,6,20JUN2016,20JUN2016
102,2,12JAN2018,13JAN2018
102,2,14JAN2018,15JAN2018
102,2,16JAN2018,19JAN2018
102,3,20JAN2018,28JAN2018
102,4,29JAN2018,12FEB2018
102,5,13FEB2018,18FEB2018
102,6,19FEB2018,28FEB2018
;;;;
run;

Can anyone help me in this

Re: split data based on conditions

DanielLangley — Tue, 06 Nov 2018 12:55:24 GMT

Assuming that the 3rd row in your want (I named this as ActualWant) where num = 3 is actually supposed to be num=2 then this might work for you.

It would have been more helpful to have an idea of what exactly you wanted the code to do and had a 'data have' that contains enough examples (such as id 102) for your 'data want'.

If this doesn't work as intended please explain why and it can then be fixed.

data have;
  infile datalines dsd truncover;
  input id:32. num:32. std:DATE9. endt:DATE9.;
  format std endt yymmddd10.;
datalines4;
101,2,10MAY2016,15MAY2016
101,3,16MAY2016,22MAY2016
101,4,23MAY2016,05JUN2016
101,5,06JUN2016,19JUN2016
101,6,20JUN2016,20JUN2016
;;;;
run;

data want;
set have (rename=(std=_std endt=_endt));
by id;
format std endt yymmddd10.;

if first.id then do;
/*Potential problem if _std and _endt are on the same day here. Just modify the if condition to
if first.id and _endt - _std GE 1 then do;
which also checks that the enddate is after the startdate
*/
	std = _std;
	endt = std + 1;
	output;
	Iterator = 0;
	do until (endt ge _endt or Iterator ge 2);
		Iterator = 	sum(Iterator,1);
  		std = std + 2;
  		endt = min(_endt,endt + 2);
		if Iterator eq 2 then endt = _endt; *Only want 2 iterations and then put the solution;
	  	output;
	end; *end until;
end; *end if;

else do;
	std = _std;
	endt = _endt;
	output;
end; *end else if;


drop Iterator _std _endt;
run;

data Actualwant;
infile datalines dsd truncover;
input id:32. num:32. std: DATE9. endt: DATE9.;
format std endt yymmddd10.;
datalines4;
101,2,10MAY2016,11MAY2016
101,2,12MAY2016,13MAY2016
101,3,14MAY2016,15MAY2016
101,3,16MAY2016,22MAY2016
101,4,23MAY2016,05JUN2016
101,5,06JUN2016,19JUN2016
101,6,20JUN2016,20JUN2016
102,2,12JAN2018,13JAN2018
102,2,14JAN2018,15JAN2018
102,2,16JAN2018,19JAN2018
102,3,20JAN2018,28JAN2018
102,4,29JAN2018,12FEB2018
102,5,13FEB2018,18FEB2018
102,6,19FEB2018,28FEB2018
;;;;
run;