topic Re: copy values down in SAS Programming

copy values down

Ronein — Thu, 07 Jul 2022 10:08:08 GMT

Hello

I have a data set with 4 columns.

I need to copy the values of fields : X3 ,X4 down.

It means that for x1=1111 :

the values 1000,400 will copy down to 11 rows and values 3000,8000 will copy down to 11 rows.

In real life I have a data set with millions of rows and this action should be done for each group(that is defined by x1,x2)

Data have;
input X1 X2 X3 X4;
cards;
1111 2205 1000 4000
1111 2204 . .
1111 2203 . .
1111 2202 . .
1111 2201 . .
1111 2112 . .
1111 2111 . .
1111 2110 . .
1111 2109 . .
1111 2108 . .
1111 2107 . .
1111 2106 . .
1111 2205 3000 8000
1111 2204 . .
1111 2203 . .
1111 2202 . .
1111 2201 . .
1111 2112 . .
1111 2111 . .
1111 2110 . .
1111 2109 . .
1111 2108 . .
1111 2107 . .
1111 2106 . .

2222 2205 7000 2700
2222 2204 . .
2222 2203 . .
2222 2202 . .
2222 2201 . .
2222 2112 . .
2222 2111 . .
2222 2110 . .
2222 2109 . .
2222 2108 . .
2222 2107 . .
2222 2106 . .
2222 2205 1500 5500
2222 2204 . .
2222 2203 . .
2222 2202 . .
2222 2201 . .
2222 2112 . .
2222 2111 . .
2222 2110 . .
2222 2109 . .
2222 2108 . .
2222 2107 . .
2222 2106 . .
;
run;

Re: copy values down

PaigeMiller — Thu, 07 Jul 2022 10:37:13 GMT

Seems like a RETAIN statement is what you need.

data want;
    set have (rename=(x3=x3a x4=x4a));
    retain x3 x4;
    if not missing(x3a) then x3=x3a;
    if not missing(x4a) then x4=x4a;
    drop x3a x4a;
run;

Re: copy values down

AlexBennasar — Thu, 07 Jul 2022 10:41:04 GMT

data want;
	retain _x3 _x4;
	set have;

	if not missing(x3) and not missing(x4) then
		do;
			_x3=x3;
			_x4=x4;
		end;
	else
		do;
			x3=_x3;
			x4=_x4;
		end;
	drop _x3 _x4;
run;

Re: copy values down

andreas_lds — Thu, 07 Jul 2022 10:40:44 GMT

The line

1111 2106 . .2222 2205 7000 2700

seems to be not right.

Similar problems have been asked and answered many times. What have you tried?

If groups are defined by x1 and x2, which values do you expect for the second obs?

Re: copy values down

Ksharp — Thu, 07 Jul 2022 11:44:55 GMT

data want;
update have(obs=0) have;
by x1;
output;
run;

Re: copy values down

Kurt_Bremser — Thu, 07 Jul 2022 13:58:39 GMT

@Ronein wrote:

should be done for each group(that is defined by x1,x2)

Then there will never be a copy forward, as all x2 values are unique within a x1 group, so all these groups consist of exactly one observation.

Please review your post.