topic Re: Copy values in variable dependent on value in another variable in SAS Programming

Copy values in variable dependent on value in another variable

Sarahkl25 — Thu, 07 Dec 2017 11:31:10 GMT

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

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

PeterClemmensen — Thu, 07 Dec 2017 11:42:22 GMT

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;

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

Astounding — Thu, 07 Dec 2017 12:30:52 GMT

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;

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

Ksharp — Thu, 07 Dec 2017 12:37:44 GMT

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;

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

Sarahkl25 — Thu, 07 Dec 2017 13:32:05 GMT

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!!!