calcualate time value

[ari]

data have;

input ID ntime week time value;
datalines;
2 0 4 718 442
2 0 4 718 442
2 2 4 718 650
2 2 4 720 650
2 0 12 1424 700
2 0 12 1424 700
2 2 12 1424 853
2 2 12 1748 853
1 0 4 718 442
1 0 4 718 442
1 2 4 718 650
1 2 4 720 650
1 0 12 . .
1 2 12 1748 853
1 2 12 1748 853

;;

data want;
input ID ntime week time value time1;
datalines;
2 0 4 718 . 694
2 0 4 718 442 718
2 2 4 718 . 718
2 2 4 720 650 720
2 0 12 1424 . 1400
2 0 12 1424 700 1424
2 2 12 1748 . 1424
2 2 12 1748 853 1748
1 0 4 718 . 694
1 0 4 718 442 718
1 2 4 718 . 718
1 2 4 720 650 720
1 0 12 . . .
1 2 12 1748 . 1724
1 2 12 1748 853 1748

;;

I am trying to derive time1 variable using  week variable and ntime variable. 

each week value  sorted by ntime (0 and 2)

for a set of rows , if week remains the same, then

if  first row then time1 = time-24;

else time1=time;

in case if  time and value missing for ntime=0 in a week  then time1=.  but for ntime=2,  

if  first row then time1 = time-24;

else time1=time;

value will be set to missing for the  each of first occurances of ntime and also when time is missing.

Any help to achieve this output?

Thanks

[Kurt_Bremser]

Shouldn't that have gone into https://communities.sas.com/t5/Base-SAS-Programming/replace-value-based-on-condition/m-p/337015?

[sschleede]

I created this code. My output doesn't completely match your want data set. I am hoping it gives you a start on what you are trying to do.

I sorted by id week ntime - what other variable determines first and second observation for ntime?

In your want dataset:

     ID 2, week 12, ntime 0, first observation - why is time = 1400? I didn't see any requirement to change the value of time. Why is time1 = 1420 instead of 1424 - 24 = 1400?

    ID 2, week 12, ntime 2 - why isn't there a record in your output dataset with time = 1424 to match the record in the have dataset?

    ID 1, week 4, ntime 2, time 718 - why is value = 650?

    ID 2, week 4, ntime 2, time 718 - why is value = .? This isn't the first observation for the week.

/*I am trying to derive time1 variable using week variable and ntime variable. */

/*each week value sorted by ntime (0 and 2)*/

proc sort data = have out = havesorted;

by id week ntime;

run;

data maybewant;

set havesorted;

by id week ntime;

/* value will be set to missing for the each of first occurances of ntime and also when time is missing. */

if first.ntime or missing(time) then value = .;

/*I am trying to derive time1 variable using week variable and ntime variable. */

/*each week value sorted by ntime (0 and 2) for a set of rows , if week remains the same, then*/

/*if first row then time1 = time-24;*/

/*else time1=time;*/

if first.week then do;

if missing(time) then time1 = .; else time1 = time - 24;

end;

else time1 = time;

/* in case if time and value missing for ntime=0 in a week then time1=. but for ntime=2,

if first row then time1 = time-24;

else time1=time;

*/

if first.ntime and ntime = 2 and ^missing(time) then time1 = time - 24;

run;

proc sort data = want;

by id week ntime;

run;

proc compare base = want compare = maybewant;

run;

[ari]

@sschleede: Thanks, dataset want is updated as there were some typing errors.

ID 2, week 12, ntime 0, first observation - why is time = 1400? I didn't see any requirement to change the value of time. Why is time1 = 1420 instead of 1424 - 24 = 1400?

##  this is corrected in the want dataset

    ID 2, week 12, ntime 2 - why isn't there a record in your output dataset with time = 1424 to match the record in the have dataset?

## corrected

    ID 1, week 4, ntime 2, time 718 - why is value = 650?

   first occurance of ntime=2 and week=4 always contains missing vaule. but the subsequent row with same values of ntime(ntime=2) and week(week=4) contains non-missing values. it could be any value.

    ID 2, week 4, ntime 2, time 718 - why is value = .? This isn't the first observation for the week.

  ## value =. because that the first value for the combination of ntime=2 and week=4.

time week 

0      4

0      4

2      4

2      4

only for the first occurance of time and week time1 will be updated.

however, in the foloowing case, where time 0 is missing then time1 will calculated for the first occurnce of time=2 week=4

time week 

.       4

2      4

2      4

[sschleede]

You can make as many if then clauses as you want. You just need to work through what conditions you are handling.

So, you could add:

if week = 4 and ntime = 2 and first.ntime then time1 = .;

Sometimes if I have this many unique conditions, I just go ahead and list all of them to be sure I have handled all possibilities. Makes for verbose code, but I then know I handled everything because sometimes the test dataset doesn't have all possiblities for testing. Also, if 1 changes, you just find that line and change it instead of having to rationalize which if statement is applicable.

Do you see how the first.week and first.ntime are working? They get set to true each time the week or ntime change as the program is reading in the dataset.

[sschleede]

I reread your post - I see what you mean. If time = . on the first record, then treat the second record as the first record?

Depending where you are heading downstream of this program, I see two possibilities:

1. filter out records where time is missing in the set statement if you don't need them downstream of this step, then they won't be counted in the first. calculations. If you need them later, you could handle them separately and append them back in later.

2. you could have a variable that you retain to keep track of this situation - like firstnonmissingvalue