topic Increment a variable value and copy/ retain the remaining variables in SAS Programming

Increment a variable value and copy/ retain the remaining variables

ari — Tue, 16 Aug 2016 13:03:25 GMT

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

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

jklaverstijn — Tue, 16 Aug 2016 11:37:39 GMT

Hi,

How about this:

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=

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

ari — Tue, 16 Aug 2016 12:08:57 GMT

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

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

jklaverstijn — Tue, 16 Aug 2016 12:16:03 GMT

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

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

jklaverstijn — Tue, 16 Aug 2016 12:21:21 GMT

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.

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

Ksharp — Wed, 17 Aug 2016 02:21:54 GMT

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;

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

ari — Wed, 17 Aug 2016 12:21:03 GMT

Thanks Jan.