BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ari
Quartz | Level 8 ari
Quartz | Level 8
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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

6 REPLIES 6
jklaverstijn
Rhodochrosite | Level 12

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=

ari
Quartz | Level 8 ari
Quartz | Level 8
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
jklaverstijn
Rhodochrosite | Level 12

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

 

 

jklaverstijn
Rhodochrosite | Level 12

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.

Ksharp
Super User
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;


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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