Select only error records in one variable

[David_Billa]

I want to capture only the error records based on some condition in one seperate variable. So I tried the code as below, but it is not producing the desired Output.

data have;
infile datalines;
input id $ sex $ age $;
datalines;
. M 21
2 F 23
23 M 21
;
run;

data want (drop=separator);
length Faulty_Record $ 200;
retain id sex age Faulty_Record;
set have;
if id = " " then do;
    separator=' ';
    Faulty_Record = catx(separator,of _all_);
    err_message = "ID Variable cannot be missing";
end;
run;

Output of WANT dataset is,

Faulty_Record	id	sex	age	err_message
M 21		M	21	ID Variable cannot be missing
M 21	2	F	23
M 21	23	M	21

Desired Output WANT dataset should be,

Faulty_Record	id	sex	age	err_message
M 21		M	21	ID Variable cannot be missing
	2	F	23
	23	M	H

On a seperate note, I'm not sure why retain Statement is not arranging the variables in the order which I gave in retain Statement.

[s_lassen · Accepted solution]

The RETAIN statement is used to retain variables, so that they are not set to missing at each iteration of the datastep. That is why your Faulty_Record is set for all records. But it is true that you can also use the RETAIN statement to change the order of variables.

You can get the result you want like this:

data want;
set have;
length Faulty_Record $200;
if id = " " then do;
  Faulty_Record = catx(' ',of _all_);
  err_message = "ID Variable cannot be missing";
end;
run;

In other words: Declare the faulty record after reading the input, so that the input data variables are created first. And no need for a special variable for the CATX separator, just use a constant.