Creating a new variable based on multiple conditions

[lm12abh]

Hi I require some help on the below. Want is the field I wish to create, I have also put a description of what I am trying to obtain:

ID	FLAG	VAR1	VAR2	WANT	Description for explanation
123	1	12.1	1	0	=FIRST.ID=0
123	0	12.1	2	0	=WANT RECORD BEFORE
123	2	12.1	3	12.1	=VAR1
123	2	12.9	4	12.9	=VAR1
123	0	12.9	5	12.9	=WANT RECORD BEFORE
123	0	13.5	6	12.9	=WANT RECORD BEFORE
123	1	14.5	7	7.5	=VAR1-VAR2
456	2	92	10	0	=FIRST.ID=0
456	0	97	8	0	=WANT RECORD BEFORE
456	0	97	12	0	=WANT RECORD BEFORE
456	2	97	0	97	=VAR1-VAR2

Current SAS code:

data data2;
	set data1;

	by id;

	retain want;
	if first.id then want=0.00;
	else if flag=2 then want=var1;
	else if flag=1 then want=var1-var2;
	else want+0; /* without just get . , with get 0 */
run;

[PeterClemmensen]

How does your current result differ from the wanted result you posted?

[lm12abh]

Hi sorry I for got to mention. In the case where there is a flag=0 but the bucket before is non zero, Ishould have the same value as above. Instead I have a zero. (Issue highlighted in red).

What I have now:

ID	FLAG	VAR1	VAR2	WANT	Description for explanation	Have now
123	1	12.1	1	0	=FIRST.ID=0	0
123	0	12.1	2	0	=WANT RECORD BEFORE	0
123	2	12.1	3	12.1	=VAR1	12.1
123	2	12.9	4	12.9	=VAR1	12.9
123	0	12.9	5	12.9	=WANT RECORD BEFORE	0
123	0	13.5	6	12.9	=WANT RECORD BEFORE	0
123	1	14.5	7	7.5	=VAR1-VAR2	7.5
456	2	92	10	0	=FIRST.ID=0	0
456	0	97	8	0	=WANT RECORD BEFORE	0
456	0	97	12	0	=WANT RECORD BEFORE	0
456	2	97	0	97	=VAR1-VAR2	97

[ed_sas_member]

Hi @lm12abh 

Correct me if I'm wrong but it seems that you want to display 2 decimals (e.g. not 0 but 0.00).

You can do that with a FORMAT Statement.

NB: There is no need to write want = 0.00 as it is exactly the same as want=0.

Idem for else want+0 as you have the value is already retained.

My best,

data data2;
	set data1;

	by id;
	format want 8.2;
	retain want;
	if first.id then want=0;
	else if flag=2 then want=var1;
	else if flag=1 then want=var1-var2;
run;

Capture d’écran 2020-02-07 à 10.43.51.png 

[lm12abh]

Thanks, Yes I already have a format statement (I didn't include that part of the code) good to know it wont affect it.