BookmarkSubscribeRSS Feed
rayzak
Calcite | Level 5

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
3 REPLIES 3
Reeza
Super User

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
Garnet | Level 18

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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1243 views
  • 1 like
  • 3 in conversation