Using retain or lag

[ChuksManuel]

Hello programmers,

I am trying to use the retain or lag function to help me calculate the Incidence density ratios.

My IDR= Incidence density for value=1/ incidence density for value=0 and i want to output only the last observations.

Please i'll be glad if anyone can give me an idea on how to go about this? I want to calculate the respective Incidence density for I_DPD, I2_DPY, I_AnginaPD and I2_AnginaPY.

data one;
input	tc	tn $	value	I_DPD	I2_DPY	I_AnginaPD	I2_AnginaPY;
datalines;
	1	Exhaustion	0	0.016	6.07	0.002	0.904
	1	Exhaustion	1	0.016	5.91	0.003	1.265
	2	Problemwalking	0	0.016	6.140	0.002	0.970
	2	Problemwalking	1	0.015	5.71	0.004	1.004
	3	ProblemStanding	0	0.016	6.17	0.008	0.95
	3	ProblemStanding	1	0.014	5.43	0.005	0.98
; run;
proc print; run;

[PeterClemmensen]

So what does your desired result from this data look like?

[ChuksManuel]

Hello,

I basically want to get the incidence density ratio (Incidence in observation with value =1 / incidence in observation with value=0) by each 'tc' . 

I want to have an output with four incidence densities in the columns as IDR_DPD, IDR_DPY, IDR_AnginaPD, IDR_AginaPY.

I did this first code with a lag function to get the incidence density , IDR_DPD. And i can do that for the rest but i want to know how i can do this with a retain function.

data one1;
set one;
by tc;
lag_I_dpd=lag(I_dpd);
IDR_DPD= I_dpd/lag_I_dpd;
run;
proc print; run;

[Kurt_Bremser]

Don't describe the expected result, show it. As you can see, there seems to be some difficulty infering your requirements from the description alone.

[ScottBass]

---

@Kurt_Bremser wrote:

Don't describe the expected result, show it. As you can see, there seems to be some difficulty infering your requirements from the description alone.

---

Yes, what @Kurt_Bremser said.  I don't know what "incidence density" is.  I suppose I could Google it.  But I consider myself a good SAS programmer, so could probably help you if I knew what your target data was.

So, provide data steps as "have" (your source data) and "want" (your target results).  They should be self-contained data steps using the datalines statement, entered using the "Insert SAS code" icon (so the code does not get reformatted).  The code should be something we can cut-and-paste from here into SAS and it runs without error.

Once we have that information, we can code a solution that matches your target data.

Otherwise, if you describe your data, then we will describe the code you need to write.

P.S.: Click, read, and comprehend the last three links in Kurt's signature block.

[Tom]

If I am understanding this you just want to divide by the value of your measures on the non-ZERO rows with the value of the measure from the ZERO row.

So if you have data like:

data have ;
   input ID VALUE MEASURE ;
cards;
1 0 100
1 1 50
2 0 200
2 1 60
;

You want to get a result that looks like:

data want;
   input ID RATIO  ;
cards;
1 0.50 
2 0.30
;

Only you have more than one analysis variable.

[ChuksManuel]

Hello Tom,

That's exactly what i want to do. To find the ratio between the lower and the upper.

[ChuksManuel]

Thank you for the response.

Please final output would be something like this

data finaloutput;
input	tc	tn $	value	I_DPD	I2_DPY	I_AnginaPD	I2_AnginaPY IDR_DPD IDR_DPY IDR_AnginaPD IDR_AnginaPY;
datalines;
	1	Exhaustion	1	0.016	5.91	0.003	1.265 . . . .
	2	Problemwalking	1	0.015	5.71	0.004	1.004 . . . .
	3	ProblemStanding	1	0.014	5.43	0.005	0.98 . . . .
; run;
proc print; run;

[Kurt_Bremser]

So you just apply a where condition for value = 1 and add four variables that are always missing?

[Reeza · Accepted solution]

Rather than lag, why not do a merge?

data want;
merge one (where=value=0)
one(where=value=1 rename = (I_DPD = I_DPD1 I2_DPY = I2_DPY1 ....));

array values0(*) I_dpd i2_dpy i_anginapd i2_anginapy;
array values1(*) .....;
array want(4) diff1-diff4;
do i=1 to 4;
want(i) = values1(i) / values0(i);
end;
run;

At least that's one way, not quite dynamic. If you want a fully dynamic solution, it's likely worth transposing your data to a long format.