05-26-2017 07:38 PM
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
05-26-2017 08:14 PM
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.
05-27-2017 02:28 AM
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
05-30-2017 11:54 AM - edited 05-30-2017 11:55 AM
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