topic Re: Manipulate data by ID in SAS Data Management

Manipulate data by ID

blackandwhite — Thu, 19 Dec 2019 12:05:01 GMT

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!

Re: Manipulate data by ID

Kurt_Bremser — Thu, 19 Dec 2019 12:15:34 GMT

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-p/583609

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

Re: Manipulate data by ID

ed_sas_member — Thu, 19 Dec 2019 12:38:40 GMT

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;

Re: Manipulate data by ID

blackandwhite — Thu, 19 Dec 2019 13:47:29 GMT

works great- thanks very much!!

Re: Manipulate data by ID

blackandwhite — Thu, 19 Dec 2019 13:48:25 GMT

Ok - will do next time, thanks.

Re: Manipulate data by ID

novinosrin — Thu, 19 Dec 2019 15:18:20 GMT

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;

Re: Manipulate data by ID

blackandwhite — Thu, 19 Dec 2019 15:38:06 GMT

Hi @novinosrin, works great, thanks very much.

Can't complain about having several options 🙂