DATA Step, Macro, Functions and more

Increment a variable value and copy/ retain the remaining variables

Accepted Solution Solved
Reply
Frequent Contributor
Frequent Contributor
Posts: 101
Accepted Solution

Increment a variable value and copy/ retain the remaining variables

[ Edited ]
Have:    
UIDv1v2v3v4
100.010.11
110.01.1
1100.01.1
200.0110
210.01.0
2100.01.0
     
want:    
UIDv1v2v3v4
100.010.11
110.01.1
1100.01.1
100.010.10
110.01.0
1100.01.0
100.010.21
110.01.1
1100.01.1
100.010.20
110.01.0
1100.01.0
200.0110
210.01.0
2100.01.0
200.0111
210.01.1
2100.01.1
200.0120
210.01.0
2100.01.0
200.0121
210.01.1
2100.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: 9,681

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;


View solution in original post


All Replies
Super Contributor
Posts: 408

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

[ Edited ]

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=

Frequent Contributor
Frequent Contributor
Posts: 101

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
Super Contributor
Posts: 408

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?

 

 

Super Contributor
Posts: 408

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
Frequent Contributor
Posts: 101

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

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

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.

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

Discussion stats
  • 6 replies
  • 357 views
  • 0 likes
  • 3 in conversation