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

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

rayzak — Fri, 26 May 2017 23:38:59 GMT

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

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

Reeza — Sat, 27 May 2017 00:14:22 GMT

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.

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

Shmuel — Sat, 27 May 2017 06:28:07 GMT

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

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

rayzak — Tue, 30 May 2017 15:55:57 GMT

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