I am importing excel spreadsheets and each sheet is in the same layout. I have a column called New_Value that has a value of unknown data type since it is being imported from one column in excel sheet (therefore, if all values in that column are numeric, then SAS will create this column as numeric type, but if that column has mixed data, SAS will assign the character data type). This is how I import and create multiple sets (depending on how many files are in the folder): proc sql;
CREATE TABLE tmp&cnt. AS SELECT DISTINCT * FROM xl.Sheet2;
run; Then, I need to figure out if the value is character type or numeric type, because I need to separate them into different columns in the database. My problem is that I am unable to figure out the way to distinguish value types. This is what I have tried, but it always gives me the type of CHAR, although in some test scenarios the type is actually a NUMBER SELECT
CASE
WHEN ("%DataTyp(New_Value)" = 'NUMERIC') THEN 0
WHEN ("%DataTyp(New_Value)" = 'CHAR') THEN 1
ELSE 2
END as is_char,
"%DataTyp(New_Value)" as value_type
FROM tmp&cnt. Basically, the final goal is to Check if the New_Value is a Character or a Numeric type If the New_Value is a number, store it as the value_numerical field If the New_Value is a character but contains a numeric value, convert it to numeric and store it as the value_numerical field If the New_Value is a char, store it as the value_text field
... View more