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
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;
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=
I think it already does. Just the rows in WANT are sorted differently from your example. Is that a problem?
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.