Hello,
I am new in working with SAS and hope someone can help me with my problem.
I have to subtract var2 from var1 and so on in my example but the problem is, that the values are in different lines although the ID is similar. So the question is how can I copy the values for one varibáble into the other lines for the same ID. So to copy the values depentent on the value in the variable ID (see second table how the result should look like).
ID | var1 | var2 | var3 | result1=var1-var2 | result2=var2-var3 |
1 | . | 4 | . | ||
1 | . | 4 | . | ||
1 | 2 | . | . | ||
1 | 2 | . | 2 | ||
1 | . | . | 2 | ||
2 | 9 | . | . | ||
2 | 9 | . | . | ||
2 | . | . | . | ||
2 | . | . | 1 | ||
2 | . | . | 1 | ||
3 | 3 | . | . | ||
3 | 3 | . | . | ||
3 | . | 2 | . | ||
3 | . | 2 | . | ||
3 | . | . | 7 | ||
3 | . | . | 7 | ||
ID | var1 | var2 | var3 | result1=var1-var2 | result2=var2-var3 |
1 | 2 | 4 | 2 | -2 | 2 |
1 | 2 | 4 | 2 | -2 | 2 |
1 | 2 | 4 | 2 | -2 | 2 |
1 | 2 | 4 | 2 | -2 | 2 |
1 | 2 | 4 | 2 | -2 | 2 |
2 | 9 | . | 1 | . | . |
2 | 9 | . | 1 | . | . |
2 | 9 | . | 1 | . | . |
2 | 9 | . | 1 | . | . |
2 | 9 | . | 1 | . | . |
3 | 3 | 2 | 7 | 1 | -5 |
3 | 3 | 2 | 7 | 1 | -5 |
3 | 3 | 2 | 7 | 1 | -5 |
3 | 3 | 2 | 7 | 1 | -5 |
3 | 3 | 2 | 7 | 1 | -5 |
3 | 3 | 2 | 7 | 1 | -5 |
I only found the function to retain values dependent on the previous value but that wouldn help in my case. So I would be very happy if someone yould help!
Thank you so much and best regards!
Sarah
The solution from @PeterClemmensen is a step in the right direction. However, it gives you just one observation per ID. That might actually be a better result than the one that you asked for, but if you really want 6 observations per ID, you could modify it accordingly:
data want;
update have(obs=0) have;
by ID;
result1=var1-var2;
result2=var2-var3;
if last.ID then do _n_=1 to 6;
output;
end;
run;
If it's possible that you might not have 6 observations for every ID, and you want the number of observations in the output to match the number in the input:
data want;
update have(obs=0) have;
by ID;
n_records + 1;
if last.id;
result1=var1-var2;
result2=var2-var3;
do _n_=1 to n_records;
output;
end;
n_records = 0;
drop n_records;
run;
Why do you want to have 5 or 6 dublicate observations? Why not have one observation for each ID when they are exactly the same anyway?
If that will work for you, do something like this
data have;
input ID var1-var3;
datalines;
1 . 4 .
1 . 4 .
1 2 . .
1 2 . 2
1 . . 2
2 9 . .
2 9 . .
2 . . .
2 . . 1
2 . . 1
3 3 . .
3 3 . .
3 . 2 .
3 . 2 .
3 . . 7
3 . . 7
;
data want;
update have(obs=0) have;
by ID;
result1=var1-var2;
result2=var2-var3;
run;
The solution from @PeterClemmensen is a step in the right direction. However, it gives you just one observation per ID. That might actually be a better result than the one that you asked for, but if you really want 6 observations per ID, you could modify it accordingly:
data want;
update have(obs=0) have;
by ID;
result1=var1-var2;
result2=var2-var3;
if last.ID then do _n_=1 to 6;
output;
end;
run;
If it's possible that you might not have 6 observations for every ID, and you want the number of observations in the output to match the number in the input:
data want;
update have(obs=0) have;
by ID;
n_records + 1;
if last.id;
result1=var1-var2;
result2=var2-var3;
do _n_=1 to n_records;
output;
end;
n_records = 0;
drop n_records;
run;
data have;
input ID var1-var3;
datalines;
1 . 4 .
1 . 4 .
1 2 . .
1 2 . 2
1 . . 2
2 9 . .
2 9 . .
2 . . .
2 . . 1
2 . . 1
3 3 . .
3 3 . .
3 . 2 .
3 . 2 .
3 . . 7
3 . . 7
;
proc sql;
create table want as
select *,max(var1) as m1,max(var2) as m2,max(var3) as m3,
calculated m1-calculated m2 as x1,
calculated m2-calculated m3 as x2
from have
group by id
order by id;
quit;
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 25. 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.