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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.