Manipulate data by ID

[blackandwhite]

Hello,

I am trying to manipulate the data by ID, so that the variable "start" always takes the value of lag "end", except for the first value which is 0. The "end" variable also has to be adjusted accordingly. Please see attached what I mean.

Thank you very much!

[Kurt_Bremser]

Please supply example data in a data step with datalines, as you have been shown in https://communities.sas.com/t5/SAS-Programming/Create-variable-by-ID-and-based-on-another-variable/m... 

Excel files can not convey important attributes of SAS data, and make it harder for us to recreate your data. Data steps are unambiguous.

[blackandwhite]

Ok - will do next time, thanks.

[ed_sas_member · Accepted solution]

Hi @blackandwhite 

You can try this. Let me know if that does correspond to what you want.

Best,

data want;
	set have;
	
	by ID;
	
	dif = end - start;
	
	if first.ID then end2 = 0;
	end2 + dif;
	
	start2 = lag(end2);
	if first.ID then start2 = 0;

	drop start end dif;
	rename start2 = start end2 = end;
	
run;

[blackandwhite]

works great- thanks very much!!

[novinosrin]

Hi @blackandwhite  Sorry late to the party as I missed to see this question. Oh well.

If i understand correctly, one temp variable gymnastic alone should do

data have;
input ID	start	end;
cards;
1	0	6
1	11	17
1	17	25
2	0	5
2	5	10
2	10	18
;

data want;
set have;
by id;
end=ifn(not first.id,sum(_iorc_,end-start,0),end);
start=ifn(not first.id,_iorc_,start);
_iorc_=end;
run;

[blackandwhite]

Hi @novinosrin, works great, thanks very much.

Can't complain about having several options 🙂