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;
How does your current result differ from the wanted result you posted?
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 |
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;
Thanks, Yes I already have a format statement (I didn't include that part of the code) good to know it wont affect it.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: