one of my numeric error codes has been split in two and given a letter

[CJJC]

Good day everyone,

New EG user here.  I am working with a legacy program that looks for lab errors.   The numeric errors are assigned a number that represents a letter code.  

Error #14 has been changed at the lab into 14A and 14B, which results in log notes stating "Invalid numeric data, "14A", at line 115 column 78.

How can I edit the code to prevent this?

****import parameter files***;
%macro newdata (outfile, filename,param,param_g,param_error, param_a);
data first;
%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
infile &filename delimiter = ','
MISSOVER DSD lrecl=32767 firstobs=1 ;
informat year best32. ; informat month best32. ;
informat day best32. ; informat labid best32. ;
informat &param_g $2. ; informat &param best32. ;
informat &param_error best32. ; informat &param_a $2. ;
*informat labsignoff $3. ;

format year best12. ; format month best12. ;
format day best12. ; format labid best12. ;
format &param_g $2. ; format &param 7.3 ;
format &param_error best12. ; format &param_a $2. ;
*format labsignoff $3. ;

input year month day labid &param_g $ &param &param_error &param_a $ ;*labsignoff $ ;
if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
run;
proc sort; by labid year month day;

data &outfile;
set first;
if &param_error = 0 then do; &param_A = "";end;
if &param_error = 23 then do; &param_A = "QQ";end;
if &param_error = 1 then do; &param_A = "A"; &param = .; end;
if &param_error = 22 then do; &param_A = "JJ";end;
if &param_error = 18 then do; &param_A = "RR";end;
if &param_error = 6 then do; &param_A = "RR";end;
if &param_error = 9 then do; &param_A = "V";&param = .;end;
if &param_error = '14A' then do; &param_A = "FF";end;
if &param_error = '14B' then do; &param_A = "V"; end;
*proc print;
%mend;

Thanks for considering this puzzle.

[ballardw · Accepted solution]

Which would be more disruptive to other processes: Changing the variable represented by the &param_error macro variable from numeric to character (NOTE: ALL values would character) or creating a custom informat that maps "14A" and "14B" to a different numeric value such as 141 and 142?

This question is important because we have no idea what the data sets create by this macro are used for and whether changing the type of the variable, which would be required to compare to the value "14A", will cause problems with other code later.

Question: Is it correct to have "14B" set a value of "V" but NOT set &Param to be missng?

Simplest appears to be to change:

informat &param_error best32. ; 

to

informat &param_error $3. ; 

and corresponding Format.

Then all of the comparisons would use character comparisons:

if &param_error = '0' then do; &param_A = "";end;
if &param_error = '23' then do; &param_A = "QQ";end;
if &param_error = '1' then do; &param_A = "A"; &param = .; end;
if &param_error = '22' then do; &param_A = "JJ";end;
if &param_error = '18' then do; &param_A = "RR";end;
if &param_error = '6' then do; &param_A = "RR";end;
if &param_error = '9' then do; &param_A = "V";&param = .;end;
if &param_error = '14A' then do; &param_A = "FF";end;
if &param_error = '14B' then do; &param_A = "V"; end;

This however changes the type of the &param_error variable to character.  Which may affect things like appending the data to older data sets or any analysis or model code if any than that uses the variable.

Bad things will eventually come to people that write macros that do not state data set name such as:

proc sort; by labid year month day;

and do not end data steps or procedures with Run; (or quit for those procedures that require quit).

No need to assign $ formats in the code unless you want to display a different length than the default from the informat. Similar change the BEST32 to an appropriate informat. Do you really have a year with 32 characters to read?

I realize someone modified code generated by Proc Import (or perhaps a widget calling Proc import). It is still a good idea to rationalize the code generated.

[CJJC]

I'm so glad I asked!  This will append older data and I don't want to mess that up by trying to clean up one note in the log.  Thank you so much.

[ballardw]

---

@CJJC wrote:

I'm so glad I asked!  This will append older data and I don't want to mess that up by trying to clean up one note in the log.  Thank you so much.

---

If you are appending this to other data with the variable numeric then likely you don't want a character variable.

You can create custom informat that would read "14A" "14B" to specific code numeric values that you could use in your numeric If/then code.

Perhaps something like this:

proc format library=work;
invalue Parm_error (upcase)
'14A' = 140
'14B' = 141
other = [8.]
;
run;

data example;
   informat somevar Parm_error.;
   input somevar;
datalines;
0 
23
1 
22
18
6 
9 
14A
14B
99
;

Then use the Parm_error informat to read &parm_error. Still will be numeric.

The code for reassignment would change to

if &param_error = 140 then do; &param_A = "FF";end;
if &param_error = 141 then do; &param_A = "V"; end;

If your data might already have a meaning for 140 or 141 pick something that doesn't seem likely to occur.

Note the UPCASE on the Invalue statement means that "14a" will be upcased to "14A" when the value is checked. Which if this is manually entered and might mix 14a and 14A in the data you don't have to supply multiple values in the range. Or if "14a" would mean something else remove the UPCASE but you need to have an assignment for 14a or else you will be back to the invalid data problem.

[Tom]

If the field in the CSV file is now CHARACTER then you will need to read as such.

Let's try it.  First let's clean up your initial data step. Your code looks like you copied something that PROC IMPORT generated.  There is no need to attach either FORMAT or INFORMAT to any of those variables.

Then just change the comparisons to character values instead of numeric values.  Let's convert your series of IF statements into a SELECT/END block instead.

%macro newdata
(outfile  /* Output dataset name */
,filename /* Input fileref or quoted phusical filename */
,param    /* Name for PARAM input variable */
,param_g  /* NAme for PARAM_G input variable */
,param_error /* Name for PARAM_ERROR input variable */
,param_a     /* Name for PARAM_A input variable */
);
data first;
  infile &filename dsd truncover ;
  length year month day labid 8 
         &param_g $2 &param 8
         &param_error $3 &param_a $2 
  ;
  input year -- &param_a ;
run;

proc sort data=first out=&outfile;
  by labid year month day;
run;

data &outfile;
  set &outfile;
  select (&param_error);
    when ('0')   &param_A = ' ';
    when ('23')  &param_A = 'QQ';
    when ('1')   do; &param_A = 'A'; &param = .; end;
    when ('22')  &param_A = 'JJ';
    when ('18','6')  &param_A = 'RR';
    when ('9')   do; &param_A = 'V';&param = .;end;
    when ('14A') &param_A = 'FF';
    when ('14B') &param_A = 'V'; 
    otherwise;
  end;
run;

%mend;

Now let's make up a CSV file and try it.

options parmcards=csv ;
filename csv temp;
parmcards;
2000,1,1,1,GG,1,0,AA
2000,1,2,2,GG,2,23,AA
2000,1,3,3,GG,3,1,AA
2000,1,4,4,GG,4,22,AA
2000,1,5,5,GG,5,6,AA
2000,1,6,6,GG,6,9,AA
2000,1,7,7,GG,7,14A,AA
2000,1,8,8,GG,8,14A,AA
2000,1,9,9,GG,9,14B,AA
;
options mprint;
%newdata
(outfile=WANT /* Output dataset name */
,filename=CSV /* Input fileref or quoted phusical filename */
,param=PARAM    /* Name for PARAM input variable */
,param_g=PARAM_G  /* NAme for PARAM_G input variable */
,param_error=PARAM_ERROR /* Name for PARAM_ERROR input variable */
,param_a=PARAM_A     /* Name for PARAM_A input variable */
);

proc print data=first; run;
proc print data=want; run;

[CJJC]

Thank you Tom!