topic Re: how to tell column numeric or not?! in SAS Programming

how to tell column numeric or not?!

hellohere — Wed, 17 Jul 2024 12:12:21 GMT

Any function to tell a column at a dataset is numeric or not?!

Reading in files, but some columns are numbers but are readin as char.

So need a macro take dataset name and column name 1) tells numeric or not 2) if not, simple to

change the data format.

Thanks,

Re: how to tell column numeric or not?!

PaigeMiller — Wed, 17 Jul 2024 12:17:19 GMT

PROC CONTENTS will tell you (and there are other ways as well)

proc contents data=sashelp.class;
run;

So need a macro take dataset name and column name 1) tells numeric or not 2) if not, simple to

change the data format.

This is not a "format" issue, it is a variable type issue. In general, if the column is not numeric, you can't convert it to numeric unless it contains only digits (and perhaps a single decimal point and perhaps a single minus sign at the start of the text string). You can't convert these two character variables to numeric. So what do you really want here?

Re: how to tell column numeric or not?!

ballardw — Wed, 17 Jul 2024 14:21:50 GMT

One way to query specific data set properties:

proc sql;
   select name, type 
   from dictionary.columns
   where libname='SASHELP' and memname='CLASS' 
      and upcase(name)='SEX'
   ;
quit;

Libname and memname (dataset/view /etc ) are stored in uppercase. If you have more than datasets in your libarary you may need to add " and memtype='DATA' ".

The names of the variables may not be of consistent case so address that with one of the case functions.

If you have a list of variables to check then use something like: " upcase(name) in ('VAR1' 'VARNAME2' 'OTHERVARNAME')".

Or skip the name entirely essentially duplicating Proc Contents output...

Or look at the documentation of the VARTYPE function as an alternate.

Re: how to tell column numeric or not?!

Reeza — Wed, 17 Jul 2024 15:15:11 GMT

@hellohere wrote:

Reading in files, but some columns are numbers but are readin as char.

If you can fix it while you read in the data that's preferable than fixing after the fact. In the odd case you read in a variable as numeric instead of character you would lose information. If all files are the same layout using a data step to read rather than proc import will help to standardize the read in and fix it.

Re: how to tell column numeric or not?!

Tom — Wed, 17 Jul 2024 15:23:33 GMT

@hellohere wrote:

Any function to tell a column at a dataset is numeric or not?!

Reading in files, but some columns are numbers but are readin as char.

So need a macro take dataset name and column name 1) tells numeric or not 2) if not, simple to

change the data format.

Thanks,

Such programs have been written. Here is one https://github.com/sasutils/macros/blob/master/varexist.sas

So you could use that macro to find the TYPE of the vairable.

%put AGE in SASHELP.CLASS is %varexist(sashelp.class,age,type);

But you might want to take a step back and find out why the variable was not created properly. Where did the dataset in question come from? How did it get into SAS?

Re: how to tell column numeric or not?!

hellohere — Wed, 17 Jul 2024 16:43:49 GMT

Thanks all, The macro should work out well.