BookmarkSubscribeRSS Feed
ari
Quartz | Level 8 ari
Quartz | Level 8

data have;
input id ntime week time;
datalines;
1 0 1 0
1 0 4 718
1 0 4 718
1 2 4 720
1 2 4 720
1 0 12 .
1 0 12 .
1 2 12 1748
1 2 12 1748
;;

data want;
input id ntime week time;
datalines;
1 0 1 0
1 0 4 718
1 0 4 718
1 2 4 718
1 2 4 720
1 0 12 .
1 0 12 .
1 2 12 .
1 2 12 1748

;;

 

I want to replace the first value at ntime=2 & week=4 with the value from ntime=0 & week=4

similarly. replace the first value at ntime=2 & week=12 with the value from ntime=0 & week=12

 

Any ideas to to get want datset?

 

Thanks,

8 REPLIES 8
ari
Quartz | Level 8 ari
Quartz | Level 8

@Kurt_Bremser@RW9

 

There was a mistake in the text. i have edited it. 

Kurt_Bremser
Super User

Then my code suggestion looks like this:

data have;
input id ntime week time;
datalines;
1 0 1 0
1 0 4 718
1 0 4 718
1 2 4 720
1 2 4 720
1 0 12 .
1 0 12 .
1 2 12 1748
1 2 12 1748
;
run;

data want;
set have;
by id week; /* this is only to guarantee a correct order */
retain keepval flag;
if ntime = 0
then do;
  keepval = time;
  flag = 1;
end;
if ntime = 2 and flag
then do;
  time = keepval;
  flag = 0;
end;
drop keepval flag;
run;

proc print data=want;
run;

Result:

Obs    id    ntime    week    time

 1      1      0        1        0
 2      1      0        4      718
 3      1      0        4      718
 4      1      2        4      718
 5      1      2        4      720
 6      1      0       12        .
 7      1      0       12        .
 8      1      2       12        .
 9      1      2       12     1748
ari
Quartz | Level 8 ari
Quartz | Level 8

@Kurt_Bremser: Perfect, Thanks

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, odd request, doesn't seem much logic in it?  Anyways the want you gave does not match the logic you give to get it.  This code matches the logic you have given in your text:

data have;
  input id ntime week time;
datalines;
1 0 1 0
1 0 4 718
1 0 4 718
1 2 4 720
1 2 4 720
1 0 12 .
1 0 12 .
1 2 12 1748
1 2 12 1748
;
run;

proc sql;
  create table WANT as 
  select  *,
          (select distinct TIME from HAVE where NTIME=0 and WEEK=4) as REP
  from    HAVE;
quit;

data want;
  set want;
  by id ntime notsorted;
  if first.ntime and ntime=2 then time=rep;
run;

But you will see that 2/12 does not match as "." is not at 0/4, 718 is.

ari
Quartz | Level 8 ari
Quartz | Level 8

@RW9: Thanks but this is not what I wanted. I have updated the text. there was a mistake.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

You can change my original code per your update to the logic:

data have;
  input id ntime week time;
datalines;
1 0 1 0
1 0 4 718
1 0 4 718
1 2 4 720
1 2 4 720
1 0 12 .
1 0 12 .
1 2 12 1748
1 2 12 1748
;
run;

proc sql;
  create table WANT as 
  select  *,
          (select distinct TIME from HAVE where NTIME=0 and WEEK=4) as REP1,
          (select distinct TIME from HAVE where NTIME=0 and WEEK=12) as REP2
  from    HAVE;
quit;

data want;
  set want;
  by id ntime notsorted;
  if first.ntime and ntime=2 and week=4 then time=rep1;
  if first.ntime and ntime=2 and week=12 then time=rep2;
run;
ari
Quartz | Level 8 ari
Quartz | Level 8

@RW9: Perfect, thanks

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
  • 8 replies
  • 1035 views
  • 0 likes
  • 3 in conversation