Creating a case and assigning values to appropriate columns based on the data type in SAS

[rayzak]

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

1. Check if the New_Value is a Character or a Numeric type
2. If the New_Value is a number, store it as the value_numerical field
3. If the New_Value is a character but contains a numeric value, convert it to numeric and store it as the value_numerical field
4. If the New_Value is a char, store it as the value_text field

[Reeza]

What's %datatyp? 

Why not use VTYPE in a data step, it won't work in SQL? 

Also, rather than this, use SASHELP.VCOLUMN which holds the metadata for your tables so you can see which tables you need to change. 

[Shmuel]

You should distinguish between 3 options:

1) the column is numeric only, then VTYPE is numeric

2) the column is CHAR type, then you need test the variable

    is it numeric - digits only - or alphanumeric.

   You can do the test, either by:

   -  if not missing(input(var,best8.,??) ) then NUMERIC; else ALPHANUMERIC

   -  if  var ne ' ' and translate(var,' ','0123456789') = ' ' then NUMERIC else ALPHANUMERIC

[rayzak]

Unforunately, there is no VTYPE in PROC SQL.

I came up with the following method:

	CASE 
		WHEN NOT missing(input(cats(New_Value), best8.))
			THEN input(cats(New_Val), best8.)
		END AS value_numerical,
		
	CASE 
		WHEN missing(input(cats(New_Value), best8.))
			THEN cats(New_Val)
		END AS value_character

I am hoping it will satisfy my needs