topic Re: Invalid Numeric data Q in SAS Programming

Invalid Numeric data Q

Baba9 — Wed, 28 Jul 2021 18:11:57 GMT

435   %run_dad(yr=19);
MPRINT(RUN_DAD):   data dad19;
MPRINT(RUN_DAD):   set dss.Dad_dss_core_2019;
MPRINT(RUN_DAD):   format AGE_GRP $20.;
MPRINT(RUN_DAD):   format PROVINCE $20.;
MPRINT(RUN_DAD):   if ANALYTICAL_INST_TYPE_CODE in ('1', 'A') ;
MPRINT(RUN_DAD):   if UPCASE(ENTRY_CODE) = "S" or "N" then delete;
MPRINT(RUN_DAD):   if UPCASE(ADMISSION_CATEGORY) = "R" then delete;
MPRINT(RUN_DAD):   if strip(submitting_prov_code) ne ('4') ;
MPRINT(RUN_DAD):   if strip(SUBMITTING_PROV_CODE) in ( "0" "1" "2" "3" )then PROVINCE =
"Atlantic";
MPRINT(RUN_DAD):   else if strip(SUBMITTING_PROV_CODE) in ("Y" "V" "N" "A" )then PROVINCE =
"Territories";
MPRINT(RUN_DAD):   else if strip(SUBMITTING_PROV_CODE) in ("4" "5" "6" "7" "8" "9" ) then
PROVINCE = "Seperate";
MPRINT(RUN_DAD):   if gender_code in ("F" , "M") then gender_include=1;
MPRINT(RUN_DAD):   if gender_include=1;
MPRINT(RUN_DAD):   if UPCASE(age_code) in ("D" ) and AGE_UNITS >29 then age_include = 1;
MPRINT(RUN_DAD):   if age_include = 1;
MPRINT(RUN_DAD):   if UPCASE(age_code) in ("Y" "E")and AGE_UNITS <30 then AGE_GRP = "Group 1: <30
yrs.";
MPRINT(RUN_DAD):   else if UPCASE(age_code) in ("Y" "E")and 30 <= AGE_UNITS <= 49 then AGE_GRP =
"Group 2: 30-49 yrs.";
MPRINT(RUN_DAD):   else if UPCASE(age_code) in ("Y" "E")and 50 <= AGE_UNITS <= 64 then AGE_GRP =
"Group 3: 50-64 yrs.";
MPRINT(RUN_DAD):   else if UPCASE(age_code) in ("Y" "E")and AGE_UNITS >= 65 then AGE_GRP = "Group
4: >= 65 yrs.";
MPRINT(RUN_DAD):   if ANALYTICAL_INST_TYPE_CODE in ("1") then inpatient = 1;
MPRINT(RUN_DAD):   if inpatient = 1;
MPRINT(RUN_DAD):   array _diag_code (25)$ DIAG_CODE_: ;
MPRINT(RUN_DAD):   do i=1 to 25;
MPRINT(RUN_DAD):   IF SUBSTR(_diag_code{i},1,4) in : ('B182')or SUBSTR(_diag_code{i}, 1,3) in
('C22','K70', 'K71', 'K72', 'K73', 'K74', 'K75', 'K76', 'K77', 'R18') then DIAG=1;
MPRINT(RUN_DAD):   end;
MPRINT(RUN_DAD):   if DIAG=1 ;
MPRINT(RUN_DAD):   array _cci_code (20)$ interv_cci_code_:;
MPRINT(RUN_DAD):   array _ooh (20)$ ooh_ind_:;
MPRINT(RUN_DAD):   array _status(20)$ status_attribute_code_:;
MPRINT(RUN_DAD):   do j=1 to 20;
MPRINT(RUN_DAD):   IF (_cci_code{j}) in :('1OA85LAXXK','1OA85VCXXK','1OA85WLXXJ', '1OA85WLXXK')
and _ooh[j] = ' ' and _status[j] ne 'A' then INTERV=1;
MPRINT(RUN_DAD):   end;
MPRINT(RUN_DAD):   if DIAG=1 and INTERV=1 THEN FLAG=1;
MPRINT(RUN_DAD):   if Discharge_disposition in ('07' '72' '73' '74') then flag_InHospDeath = 1;
MPRINT(RUN_DAD):  if DISCHARGE_DISPOSITION in ("72" "73" "74") then flag_InHospDeath = 1 ;
MPRINT(RUN_DAD):   keep fiscal_year dad_transaction_id SUBMITTING_PROV_CODE province ENTRY_CODE
ADMISSION_CATEGORY ANALYTICAL_INST_TYPE_CODE gender_code age_code age_units age_include
gender_include AGE_GRP MAIN_PATIENT_SERVICE MAIN_PATIENT_SUBSERVICE inpatient
DISCHARGE_DISPOSITION DIAG_CODE_: interv_cci_code_: ooh_ind_: status_attribute_code_: FLAG DIAG
INTERV flag_InHospDeath MCC ;
MPRINT(RUN_DAD):   run;

NOTE: Character values have been converted to numeric values at the places given by:
      (Line):(Column).
      622:177
NOTE: Invalid numeric data, 'N' , at line 622 column 177.

Hello Would appreciate your advice in correcting the error above

Thankyou

Re: Invalid Numeric data Q

PaigeMiller — Wed, 28 Jul 2021 18:13:58 GMT

@Baba9 wrote:
Hello Would appreciate your advice in correcting the error above

There are no errors in the log you posted. Please explain further.

Re: Invalid Numeric data Q

Reeza — Wed, 28 Jul 2021 18:16:18 GMT

1. You need to provide the code for the macro, since it's clearly conditionally running different things.

And if you identify this line it can help you debug your code.
NOTE: Invalid numeric data, 'N' , at line 622 column 177.

Re: Invalid Numeric data Q

Baba9 — Wed, 28 Jul 2021 18:18:13 GMT

The code for the macro is in the above code

I need help in resolving this NOTE

NOTE: Invalid numeric data, 'N' , at line 622 column 177.

Re: Invalid Numeric data Q

PaigeMiller — Wed, 28 Jul 2021 18:22:42 GMT

@Baba9 wrote:

The code for the macro is in the above code

I need help in resolving this NOTE

NOTE: Invalid numeric data, 'N' , at line 622 column 177.

Okay, that's a NOTE, not an error. It may be that the exact correct thing was done (which of course is not an error).

You need to provide the macro used, and a portion of your data (please provide a portion of your data via these instructions and not via any other method)

Re: Invalid Numeric data Q

Baba9 — Wed, 28 Jul 2021 18:26:04 GMT

%macro run_dad(yr=);

data dad&yr.;
set dss.Dad_dss_core_20&yr.;

format AGE_GRP $20.;
format PROVINCE $20.;

if ANALYTICAL_INST_TYPE_CODE in ('1', 'A') ;


	/* general inclusion/exclusion */
	if UPCASE(ENTRY_CODE) = "S" or "N" then delete; 
	if UPCASE(ADMISSION_CATEGORY) = "R" then delete; 

/* Province Groupings */
	if strip(submitting_prov_code) ne ('4') ;
	if strip(SUBMITTING_PROV_CODE) in ( "0" "1" "2" "3" )then PROVINCE = "Atlantic";
	else if strip(SUBMITTING_PROV_CODE) in ("Y" "V" "N" "A" )then PROVINCE = "Territories";
	else if strip(SUBMITTING_PROV_CODE) in  ("4" "5" "6" "7" "8" "9" ) then PROVINCE = "Seperate";

/*GENDER*/
if gender_code  in ("F" , "M") then gender_include=1;
if gender_include=1;

/**AGE**/
if UPCASE(age_code) in ("D" ) and AGE_UNITS >29 then age_include = 1;
if age_include = 1;
 
	
/* AGE GROUPS */
	if UPCASE(age_code) in ("Y" "E")and AGE_UNITS <30 then AGE_GRP = "Group 1: <30 yrs.";
	else if UPCASE(age_code) in ("Y" "E")and 30 <= AGE_UNITS <= 49 then AGE_GRP = "Group 2: 30-49 yrs.";
	else if UPCASE(age_code) in ("Y" "E")and 50 <= AGE_UNITS <= 64 then AGE_GRP = "Group 3: 50-64 yrs.";
	else if UPCASE(age_code) in ("Y" "E")and AGE_UNITS >= 65 then AGE_GRP = "Group 4: >= 65 yrs.";
/*Acute IP*/
if ANALYTICAL_INST_TYPE_CODE in ("1") then inpatient = 1;
	if inpatient = 1;

	/**Diagnosis of interest HCV, CLD, or HCC **/

 array _diag_code (25)$ DIAG_CODE_: ;
 
 do i=1 to 25;
 IF SUBSTR(_diag_code{i},1,4) in : ('B182') or SUBSTR(_diag_code{i}, 1,3) in ('C22','K70', 'K71', 'K72', 'K73', 'K74', 'K75', 'K76', 'K77', 'R18')  then DIAG=1;
  
 end;

 if DIAG=1 ;

 array _cci_code (20)$ interv_cci_code_:;
 array _ooh (20)$ ooh_ind_:;
 array _status(20)$  status_attribute_code_:;

 do j=1 to 20;

 IF (_cci_code{j}) in :('1OA85LAXXK','1OA85VCXXK','1OA85WLXXJ', '1OA85WLXXK')
 and  _ooh[j] = ' ' and 
     _status[j] ne 'A'  then INTERV=1;
  
 end;

 if DIAG=1 and INTERV=1 THEN FLAG=1;

	/* DEATHS */
	if Discharge_disposition in ('07'  '72' '73' '74') then flag_InHospDeath = 1;

	
	%if &yr. <= 18 %then %do;if DISCHARGE_DISPOSITION  in ("07") then flag_InHospDeath = 1;%end;
	%if &yr. >= 18 %then %do;if DISCHARGE_DISPOSITION in ("72" "73" "74") then flag_InHospDeath = 1 ;%end;

	
	/**/


keep
fiscal_year
dad_transaction_id
SUBMITTING_PROV_CODE
province
ENTRY_CODE
ADMISSION_CATEGORY
ANALYTICAL_INST_TYPE_CODE
gender_code
age_code
age_units
age_include
gender_include
AGE_GRP
inpatient
DISCHARGE_DISPOSITION
DIAG_CODE_:
interv_cci_code_:
ooh_ind_:
status_attribute_code_:
FLAG
DIAG
INTERV
flag_InHospDeath

;
run;

%mend;

Re: Invalid Numeric data Q

PaigeMiller — Wed, 28 Jul 2021 18:37:26 GMT

Another thing you can do (and should do) by yourself is to copy the log, as text; and paste it into your SAS program editor. Then batch modify the text by replacing MPRINT(RUN_DAD): with blanks. This gives you code that you should run, and then you can determine exactly which line is finding this unexpected 'N', and once you know which line is the problem you can then find the problem in your dataset.

Re: Invalid Numeric data Q

Reeza — Wed, 28 Jul 2021 18:44:40 GMT

%let yr=19;
data dad&yr.;
set dss.Dad_dss_core_20&yr.;

format AGE_GRP $20.;
format PROVINCE $20.;

if ANALYTICAL_INST_TYPE_CODE in ('1', 'A') ;


	/* general inclusion/exclusion */
	if UPCASE(ENTRY_CODE) = "S" or "N" then delete; 
	if UPCASE(ADMISSION_CATEGORY) = "R" then delete; 

/* Province Groupings */
	if strip(submitting_prov_code) ne ('4') ;
	if strip(SUBMITTING_PROV_CODE) in ( "0" "1" "2" "3" )then PROVINCE = "Atlantic";
	else if strip(SUBMITTING_PROV_CODE) in ("Y" "V" "N" "A" )then PROVINCE = "Territories";
	else if strip(SUBMITTING_PROV_CODE) in  ("4" "5" "6" "7" "8" "9" ) then PROVINCE = "Seperate";

/*GENDER*/
if gender_code  in ("F" , "M") then gender_include=1;
if gender_include=1;

/**AGE**/
if UPCASE(age_code) in ("D" ) and AGE_UNITS >29 then age_include = 1;
if age_include = 1;
 
	
/* AGE GROUPS */
	if UPCASE(age_code) in ("Y" "E")and AGE_UNITS <30 then AGE_GRP = "Group 1: <30 yrs.";
	else if UPCASE(age_code) in ("Y" "E")and 30 <= AGE_UNITS <= 49 then AGE_GRP = "Group 2: 30-49 yrs.";
	else if UPCASE(age_code) in ("Y" "E")and 50 <= AGE_UNITS <= 64 then AGE_GRP = "Group 3: 50-64 yrs.";
	else if UPCASE(age_code) in ("Y" "E")and AGE_UNITS >= 65 then AGE_GRP = "Group 4: >= 65 yrs.";
/*Acute IP*/
if ANALYTICAL_INST_TYPE_CODE in ("1") then inpatient = 1;
	if inpatient = 1;

	/**Diagnosis of interest HCV, CLD, or HCC **/

 array _diag_code (25)$ DIAG_CODE_: ;
 
 do i=1 to 25;
 IF SUBSTR(_diag_code{i},1,4) in : ('B182') or SUBSTR(_diag_code{i}, 1,3) in ('C22','K70', 'K71', 'K72', 'K73', 'K74', 'K75', 'K76', 'K77', 'R18')  then DIAG=1;
  
 end;

 if DIAG=1 ;

 array _cci_code (20)$ interv_cci_code_:;
 array _ooh (20)$ ooh_ind_:;
 array _status(20)$  status_attribute_code_:;

 do j=1 to 20;

 IF (_cci_code{j}) in :('1OA85LAXXK','1OA85VCXXK','1OA85WLXXJ', '1OA85WLXXK')
 and  _ooh[j] = ' ' and 
     _status[j] ne 'A'  then INTERV=1;
  
 end;

 if DIAG=1 and INTERV=1 THEN FLAG=1;

	/* DEATHS */
	if Discharge_disposition in ('07'  '72' '73' '74') then flag_InHospDeath = 1;

	
	%if &yr. <= 18 %then %do;if DISCHARGE_DISPOSITION  in ("07") then flag_InHospDeath = 1;%end;
	%if &yr. >= 18 %then %do;if DISCHARGE_DISPOSITION in ("72" "73" "74") then flag_InHospDeath = 1 ;%end;

	
	/**/


keep
fiscal_year
dad_transaction_id
SUBMITTING_PROV_CODE
province
ENTRY_CODE
ADMISSION_CATEGORY
ANALYTICAL_INST_TYPE_CODE
gender_code
age_code
age_units
age_include
gender_include
AGE_GRP
inpatient
DISCHARGE_DISPOSITION
DIAG_CODE_:
interv_cci_code_:
ooh_ind_:
status_attribute_code_:
FLAG
DIAG
INTERV
flag_InHospDeath

;
run;

This is likely wrong:

if UPCASE(ENTRY_CODE) = "S" or "N" then delete;

Separate is spelled incorrectly here:

else if strip(SUBMITTING_PROV_CODE) in ("4" "5" "6" "7" "8" "9" ) then PROVINCE = "Seperate";

Run the code from above and post that log. It will indicate exactly where the error is once the macro wrapping isn't involved.

@Baba9 wrote:

%macro run_dad(yr=);

data dad&yr.;
set dss.Dad_dss_core_20&yr.;

format AGE_GRP $20.;
format PROVINCE $20.;

if ANALYTICAL_INST_TYPE_CODE in ('1', 'A') ;


	/* general inclusion/exclusion */
	if UPCASE(ENTRY_CODE) = "S" or "N" then delete; 
	if UPCASE(ADMISSION_CATEGORY) = "R" then delete; 

/* Province Groupings */
	if strip(submitting_prov_code) ne ('4') ;
	if strip(SUBMITTING_PROV_CODE) in ( "0" "1" "2" "3" )then PROVINCE = "Atlantic";
	else if strip(SUBMITTING_PROV_CODE) in ("Y" "V" "N" "A" )then PROVINCE = "Territories";
	else if strip(SUBMITTING_PROV_CODE) in  ("4" "5" "6" "7" "8" "9" ) then PROVINCE = "Seperate";

/*GENDER*/
if gender_code  in ("F" , "M") then gender_include=1;
if gender_include=1;

/**AGE**/
if UPCASE(age_code) in ("D" ) and AGE_UNITS >29 then age_include = 1;
if age_include = 1;
 
	
/* AGE GROUPS */
	if UPCASE(age_code) in ("Y" "E")and AGE_UNITS <30 then AGE_GRP = "Group 1: <30 yrs.";
	else if UPCASE(age_code) in ("Y" "E")and 30 <= AGE_UNITS <= 49 then AGE_GRP = "Group 2: 30-49 yrs.";
	else if UPCASE(age_code) in ("Y" "E")and 50 <= AGE_UNITS <= 64 then AGE_GRP = "Group 3: 50-64 yrs.";
	else if UPCASE(age_code) in ("Y" "E")and AGE_UNITS >= 65 then AGE_GRP = "Group 4: >= 65 yrs.";
/*Acute IP*/
if ANALYTICAL_INST_TYPE_CODE in ("1") then inpatient = 1;
	if inpatient = 1;

	/**Diagnosis of interest HCV, CLD, or HCC **/

 array _diag_code (25)$ DIAG_CODE_: ;
 
 do i=1 to 25;
 IF SUBSTR(_diag_code{i},1,4) in : ('B182') or SUBSTR(_diag_code{i}, 1,3) in ('C22','K70', 'K71', 'K72', 'K73', 'K74', 'K75', 'K76', 'K77', 'R18')  then DIAG=1;
  
 end;

 if DIAG=1 ;

 array _cci_code (20)$ interv_cci_code_:;
 array _ooh (20)$ ooh_ind_:;
 array _status(20)$  status_attribute_code_:;

 do j=1 to 20;

 IF (_cci_code{j}) in :('1OA85LAXXK','1OA85VCXXK','1OA85WLXXJ', '1OA85WLXXK')
 and  _ooh[j] = ' ' and 
     _status[j] ne 'A'  then INTERV=1;
  
 end;

 if DIAG=1 and INTERV=1 THEN FLAG=1;

	/* DEATHS */
	if Discharge_disposition in ('07'  '72' '73' '74') then flag_InHospDeath = 1;

	
	%if &yr. <= 18 %then %do;if DISCHARGE_DISPOSITION  in ("07") then flag_InHospDeath = 1;%end;
	%if &yr. >= 18 %then %do;if DISCHARGE_DISPOSITION in ("72" "73" "74") then flag_InHospDeath = 1 ;%end;

	
	/**/


keep
fiscal_year
dad_transaction_id
SUBMITTING_PROV_CODE
province
ENTRY_CODE
ADMISSION_CATEGORY
ANALYTICAL_INST_TYPE_CODE
gender_code
age_code
age_units
age_include
gender_include
AGE_GRP
inpatient
DISCHARGE_DISPOSITION
DIAG_CODE_:
interv_cci_code_:
ooh_ind_:
status_attribute_code_:
FLAG
DIAG
INTERV
flag_InHospDeath

;
run;

%mend;

Re: Invalid Numeric data Q

jimbarbour — Wed, 28 Jul 2021 18:46:15 GMT

Can you tell us the column definitions dsd.Dad_dss_core_20&yr? Something like the below.

Jim

Re: Invalid Numeric data Q

Tom — Wed, 28 Jul 2021 18:46:53 GMT

I would fix this code error first.

if UPCASE(ENTRY_CODE) = "S" or "N" then delete;

What is the intent of that statement? Did you mean to compare the value of ENTRY_CODE to the number 1 ?

Or did you intend to test if it is either S or N?

If so then use the IN operator.

if UPCASE(ENTRY_CODE) in ("S" "N") then delete;

Re: Invalid Numeric data Q

jimbarbour — Wed, 28 Jul 2021 18:55:39 GMT

@Tom wrote:

I would fix this code error first.
if UPCASE(ENTRY_CODE) = "S" or "N" then delete;

@Tom, actually, I believe that is the "error" itself. Since "N" follows an "or", SAS is going to try to evaluate "N" to find out if it is "true" or "false". Presumably, SAS tries to cast the "N" as a number, finds the data invalid, and puts the two Notes in the log, i.e. that it had to convert alpha to numeric and that invalid numeric data was encountered which had a value of "N".

Jim

Re: Invalid Numeric data Q

Baba9 — Wed, 28 Jul 2021 18:56:20 GMT

Thankyou everyone