DATA step issue in merge

[againreddy]

Hello All,

Below code is not producing the anticipated results

data temp1;
input miles cnt Pcode $;
datalines;
0 0 BLD
999999 0 YOS
999999 24605 MNT
0 10 MNT
999999 0 TLP
;
quit;

data temp2;
input cnt flg $ Pcode $;
datalines;
1 O BLD
2 N YOS
3 O MNT
4 O MNT
5 N MNT
6 O SHT
9 S TLP
;
quit;

DATA TEMP3;
SET temp1 TEMP2;
if Pcode = 'MNT';
if flg = 'O' then miles = 999999;
RUN;

CNT =5  row should not have value of 999999 in miles        . It is suppose to be missing.  Please let me know your thoughts

miles      cnt     Pcode flg
999999  24605 MNT
0            10       MNT
999999  3        MNT O
999999  4       MNT O
999999  5       MNT N

[Astounding]

Variables that come from a SAS data set (such as MILES) are automatically retained.  So the MILES value you are noticing (when CNT=4) is retained and appears again on the next observation (when CNT=5).

For your purposes, here is a way to code around that:

DATA TEMP3;
SET temp1 TEMP2 (in=from_TEMP2);
if Pcode = 'MNT';

if from_TEMP2 then do;
    if flg = 'O' then miles = 999999;

    else miles = .;

end;
RUN;

[againreddy · Accepted solution]

Redundancy

It is redundant to name any of these items in a RETAIN statement, because their values are automatically retained from one iteration of the DATA step to the next:

• variables that are read with a SET, MERGE, MODIFY or UPDATE statement

• a variable whose value is assigned in a sum statement

• the automatic variables _N_, _ERROR_, _I_, _CMD_, and _MSG_

• variables that are created by the END= or IN= option in the SET, MERGE, MODIFY, or UPDATE statement or by options that create variables in the FILE and INFILE statements

• data elements that are specified in a temporary array

• array elements that are initialized in the ARRAY statement

• elements of an array that have assigned initial values to any or all of the elements in the ARRAY statement.

You can, however, use a RETAIN statement to assign an initial value to any of the previous items, with the exception of _N_ and _ERROR_.