Copy values in variable dependent on value in another variable

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Copy values in variable dependent on value in another variable

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).

 

      
IDvar1var2var3result1=var1-var2result2=var2-var3
1.4.  
1.4.  
12..  
12.2  
1..2  
29..  
29..  
2...  
2..1  
2..1  
33..  
33..  
3.2.  
3.2.  
3..7  
3..7  
      
IDvar1var2var3result1=var1-var2result2=var2-var3
1242-22
1242-22
1242-22
1242-22
1242-22
29.1..
29.1..
29.1..
29.1..
29.1..
33271-5
33271-5
33271-5
33271-5
33271-5
33271-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


Accepted Solutions
Solution
‎12-07-2017 08:32 AM
Super User
Posts: 6,629

Re: Copy values in variable dependent on value in another variable

[ Edited ]

The solution from @draycut 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;

 

View solution in original post


All Replies
PROC Star
Posts: 1,209

Re: Copy values in variable dependent on value in another variable

Posted in reply to Sarahkl25

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;
Solution
‎12-07-2017 08:32 AM
Super User
Posts: 6,629

Re: Copy values in variable dependent on value in another variable

[ Edited ]

The solution from @draycut 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;

 

New Contributor
Posts: 2

Re: Copy values in variable dependent on value in another variable

Posted in reply to Astounding
Thank all of you so much for your quick response!!!
The reason why I have doube observations is that there plenty other variables with different information for each ID (e.g. monthly values, etc.). But all solution are very very helpful for me and I was able to solve my problem!
Thank you very much!!!
Super User
Posts: 10,689

Re: Copy values in variable dependent on value in another variable

Posted in reply to Sarahkl25
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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 145 views
  • 1 like
  • 4 in conversation