## Increment a variable value and copy/ retain the remaining variables

Solved
Frequent Contributor
Posts: 106

# Increment a variable value and copy/ retain the remaining variables

[ Edited ]
 Have: UID v1 v2 v3 v4 1 0 0.01 0.1 1 1 1 0.01 . 1 1 10 0.01 . 1 2 0 0.01 1 0 2 1 0.01 . 0 2 10 0.01 . 0 want: UID v1 v2 v3 v4 1 0 0.01 0.1 1 1 1 0.01 . 1 1 10 0.01 . 1 1 0 0.01 0.1 0 1 1 0.01 . 0 1 10 0.01 . 0 1 0 0.01 0.2 1 1 1 0.01 . 1 1 10 0.01 . 1 1 0 0.01 0.2 0 1 1 0.01 . 0 1 10 0.01 . 0 2 0 0.01 1 0 2 1 0.01 . 0 2 10 0.01 . 0 2 0 0.01 1 1 2 1 0.01 . 1 2 10 0.01 . 1 2 0 0.01 2 0 2 1 0.01 . 0 2 10 0.01 . 0 2 0 0.01 2 1 2 1 0.01 . 1 2 10 0.01 . 1

So. V4 need to be modified for each value of v3 (as you see in the input first 3 observations of ID 1 change from 1 to 0  and then  V3 need to be incremented 10 times its original value per ID. so, v3=0.1,0.2,0.3........1 per ID.

Any help to achieve this in SAS?

Thanks

Accepted Solutions
Solution
‎08-17-2016 08:20 AM
Super User
Posts: 10,761

## Re: Increment a variable value and copy/ retain the remaining variables

```data have;
infile cards expandtabs truncover;
input UID	v1	v2	v3	v4;
cards;
1	0	0.01	0.1	1
1	1	0.01	.	1
1	10	0.01	.	1
2	0	0.01	1	0
2	1	0.01	.	0
2	10	0.01	.	0
;
run;

data temp;
set have(where=(v3 is not missing));
output;
v3=2*v3;output;
keep uid v3;
run;
proc sql;
create table want(drop=a_v3 b_v3) as
select a.uid,a.v1,a.v2,a.v3 as a_v3,b.v3 as b_v3,
case when(not missing(a.v3) and not missing(b.v3)) then b.v3
else a.v3
end as v3
,c.v4
from have as a,temp as b,(select distinct v4 from have) as c
where a.uid=b.uid
order by a.uid,b.v3,v4 desc,a.v1;
quit;

```

All Replies
Valued Guide
Posts: 533

## Re: Increment a variable value and copy/ retain the remaining variables

[ Edited ]

Hi,

``````data want;
do v3r=0.1 to 1 by 0.1;
do p=1 to nobs;
set have point=p nobs=nobs;
if not missing(v3) then v3=v3r;
output;
end;
end;
drop v3r;
stop;
run;
``````

You basically loop through dataset HAVE and replace r3 with the value according to your rule. The STOP is important or your datastep will loop forever (or until some resource gets exhausted).

Dataset WANT has some pattern also that could be applied to the code also alleviating the need for WANT. But that's not your question.

Hope this helps,

- Jan.

* Edited to use NOBS=

Frequent Contributor
Posts: 106

## Re: Increment a variable value and copy/ retain the remaining variables

Thanks Jan. My original dataset contians several id's such as this. Is there a way to make the script work for all id's??

Have:
UID v1 v2 v3 v4
1 0 0.01 0.1 1
1 1 0.01 . 1
1 10 0.01 . 1
1 0 0.01 0.1 0
1 1 0.01 . 0
1 10 0.01 . 0
2 0 0.01 0.1 1
2 1 0.01 . 1
2 10 0.01 . 1
2 0 0.01 0.1 0
2 1 0.01 . 0
2 10 0.01 . 0
Valued Guide
Posts: 533

## Re: Increment a variable value and copy/ retain the remaining variables

I think it already does. Just the rows in WANT are sorted differently from your example. Is that a problem?

Valued Guide
Posts: 533

## Re: Increment a variable value and copy/ retain the remaining variables

If order matters, this would be a (slightly unelegant) way:

``````data want;
do v3r=0.1 to 1 by 0.1;
do p=1 to nobs;
set have point=p nobs=nobs;
if not missing(v3) then v3=v3r;
row=p;
output;
end;
end;
stop;
run;
proc sort data=want out=want(drop=v3r row);
by uid v3r row;
run;
``````

Regards,

- Jan.

Frequent Contributor
Posts: 106

## Re: Increment a variable value and copy/ retain the remaining variables

Thanks Jan.
Solution
‎08-17-2016 08:20 AM
Super User
Posts: 10,761

## Re: Increment a variable value and copy/ retain the remaining variables

```data have;
infile cards expandtabs truncover;
input UID	v1	v2	v3	v4;
cards;
1	0	0.01	0.1	1
1	1	0.01	.	1
1	10	0.01	.	1
2	0	0.01	1	0
2	1	0.01	.	0
2	10	0.01	.	0
;
run;

data temp;
set have(where=(v3 is not missing));
output;
v3=2*v3;output;
keep uid v3;
run;
proc sql;
create table want(drop=a_v3 b_v3) as
select a.uid,a.v1,a.v2,a.v3 as a_v3,b.v3 as b_v3,
case when(not missing(a.v3) and not missing(b.v3)) then b.v3
else a.v3
end as v3
,c.v4
from have as a,temp as b,(select distinct v4 from have) as c
where a.uid=b.uid
order by a.uid,b.v3,v4 desc,a.v1;
quit;

```
☑ This topic is solved.