Proc SQL to change to numeric when imported as numeric OR Charcter

[ccaudillo100]

Hello, 

Currently, I have a document that drops that imports into SAS and sometimes one of the columns is blank, and other times it is not. I am trying to use Proc Sql to edit the whole table in one swoop. The issue is, case - when does not work because the values are different when the import is blank. I was wanting to see if anyone had recommendations. currently, my import can sometimes look like:

Name	Date	Answered	Total
Someone	1/19/2022		30

or like:

Name	Date	Answered	Total
Someone	1/19/2022	10	30

The Proc SQL i was trying to use was

Proc Sql;
Create Table Want as 
select datepart('Date'n) as Date1 format  = mmddyy10.
		,Name as Source
		,case
		When Answered is Null then input("0",best.)
		When Answered is not Null then Answered
		end as Answered1
		,case 
		When Total is Null then input("0",best.)
		When Total is not Null then Total 
		end as Totals
From Have
;

This pull only works if the 2nd type of table is dropped for the import but if the 1st type is, the error I receive is:

ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

Please help. I have tried CAST but it is not working for me.

[Tom · Accepted solution]

What is the original file type?  If it is a text file, like a CSV file, then don't IMPORT the file.  Just READ it and you can have complete control over the type.

To answer your question for a variable that should be a general number using the CATS() function to convert the values to character strings. 

data want;
  set have(rename=(badvar=dropme));
  length badvar $32;
  badvar = cats(dropme);
  drop dropme;
run;  

If you want the target to be numeric then add an INPUT() function call.

data want;
  set have(rename=(badvar=dropme));
  length badvar $32;
  badvar = input(cats(dropme),32.);
  drop dropme;
run;  

[ccaudillo100]

The original file type is xlsx. 

I want to automate the SAS code so ideally it would not need to be touched moving forward unless there was an error from something unexpected that did not come up during testing. 

[Tom]

---

@ccaudillo100 wrote:

The original file type is xlsx. 

I want to automate the SAS code so ideally it would not need to be touched moving forward unless there was an error from something unexpected that did not come up during testing. 

---

If you want it automated then don't use an XLSX file as the source.

Generally this type of thing happens when the column is EMPTY in the XLSX file.  In that case PROC IMPORT or the XLSX libname engine will create a one byte character variable since it will use less space than an 8 byte floating point value.

So you could drop the variables that are character with length =1 that should have been numeric.   You can make a sample SAS dataset (or just the metadata for it) to drive the checking/fixing process.

%let droplist=;
proc sql noprint;
select nliteral(a.name) into :droplist separated by ' '
  from dictionary.columns a 
  inner join dictionary.columns b
  where a.libname='MYLIB' and a.memname='TEMPLATE'
    and b.libname='WORK' and b.memname='IMPORTED'
    and upcase(a.name) = upcase(b.name)
    and a.type='num' and b.type='char' and b.length=1
;
quit;

data want;
   set mylib.template(obs=0) work.imported(drop=&droplist);
run;  

[ccaudillo100]

doing

input(cats(dropme),32.)

in the Proc SQL code worked as well. Thank you!