Adding one row based on condition per group

[noelle12]

If I group the data I have by name and id, I want to add a new row per group if the first code = last code, the new row need to have the same name and id, and the value of the 'amount' column is N/A, the values for the rest columns can just be the same as one of the rows in the group (can also just be empty, doesn't really matter).

data have;
input
name : $1.
id : 8.
code : $8.
amount : 8.
;

datalines;
A 10 12345678 100
A 10 09876543 30
B 9 88997700 20
B 9 88997700 -20
B 9 88997700 40
C 28 11223344 30
C 28 11223344 40
C 28 67676767 50

D 10 78654890 40
D 10 78654890 50
;

data want;
input
name : $1.
id : 8.
code : $8.
amount : 8.
;

datalines;
A 10 12345678 100
A 10 09876543 30
B 9 88997700 20
B 9 88997700 -20
B 9 88997700 40

B 9 88997700 N/A
C 28 11223344 30
C 28 11223344 40
C 28 67676767 50

D 10 78654890 40
D 10 78654890 50

D 10 78654890 N/A
;

[ballardw · Accepted solution]

If the "amount" is a numeric variable as shown in your data step you cannot have a value of "N/A". You can have a missing value. If you want to see a displayed value of "N/A" then you can create format that will do such but the value can't be text.

This makes a set that looks like that when the format MYNA is available. You would have to make sure the format code is run in any session you want to see N/A, or learn about format management to create and use formats stored in permanent libraries.

data have;
input
name : $1.
id : 8.
code : $8.
amount : 8.
;

datalines;
A 10 12345678 100
A 10 09876543 30
B 9 88997700 20
B 9 88997700 -20
B 9 88997700 40
C 28 11223344 30
C 28 11223344 40
C 28 67676767 50
D 10 78654890 40
D 10 78654890 50
;

proc format;
value myna
.N='N/A'
;
run;
data want;
   set have;
   by name id;
   length firstcode $ 8;
   retain firstcode;
   if first.id then firstcode=code;
   if last.id and code=firstcode then do;
      /* write the current values to the data set*/
      output;
      /* assign missing to amount*/
      amount = .N;
      output;
   end;
   else output;
   format amount myna. ;
   drop firstcode;
run;   

SAS has 27 "special" missing values from .A to .Z plus ._  in addition to the default . missing.  You can assign formats to display different meanings for each of them for a single variable. The special missing values will appear as A to Z or _ if a format is not assigned to display them differently.