topic Re: Check variable type then convert it to character in SAS Studio

Check variable type then convert it to character

leela214 — Mon, 15 Jan 2018 05:21:03 GMT

Is there any way I could find out if a variable is character or numeric? If it's numeric then I need to convert it to character. If it's character then converting will not be needed.

I tried the if vartype(varname,18) = N then varname_1 = compress(put(varname,best12.)); else varname_1 = varname;

But it didn't work.

I will appreciate any suggestion! Thank you!

Re: Check variable type then convert it to character

novinosrin — Mon, 15 Jan 2018 05:32:30 GMT

it is not vartype, it is vtype.

Try in a datastep with if _n_=1 and check and then do

if vtype(variable)='N' then do; /*your conversion*/

or vartype, you can use it in macro language as explained in documentation

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000148443.htm

Re: Check variable type then convert it to character

Kurt_Bremser — Mon, 15 Jan 2018 07:41:24 GMT

You should not need to find that out. You should know your data (see Maxim 3) from the beginning (when you retrieve data from external sources or create it). From there, it's just following the documentation.

One way to really convert (quasi in place) is this:

data test;
x1 = '2';
run;

%macro convert_to_num(varname,library,dataset);

proc sql noprint;
select type into :vartype
from dictionary.columns
where libname = upcase("&library.") and memname = upcase("&dataset.") and upcase(name) = upcase("&varname.")
;
quit;

%if &vartype. = char
%then %do;

data &library..&dataset. (drop=__&varname.);
set &library..&dataset. (rename=(&varname.=__&varname.));
&varname. = input(__&varname.,best.);
run;

%end;

%mend;

%convert_to_num(x1,work,test)

Re: Check variable type then convert it to character

RW9 — Mon, 15 Jan 2018 09:11:24 GMT

Is this because the data is coming in from Excel by any chance? That is the number one cause of garbage data. Fix your input source (maybe use CSV), and write a datastep to read the data in per your import specification. Removes this issue totally.

Re: Check variable type then convert it to character

leela214 — Tue, 23 Jan 2018 02:56:11 GMT

I have tried using vtype but it didn't work as well.

I tried using vtype in the if then statement like you've kindly suggested but it didn't work. So, I created an variable to use in the if then statement, didn't work too... It would be nice if it did! Would be happy to know how I can fix this in order for it to work.

random_no in here is character so it should go int to the else statement but it did't.

Re: Check variable type then convert it to character

leela214 — Tue, 23 Jan 2018 02:59:57 GMT

The source is csv. But, the same variable in different dataset could be in different type. sometimes numeric, sometimes character. So, I want to add a step in my macro to check for variable type and convert it if necessary.

Re: Check variable type then convert it to character

leela214 — Tue, 23 Jan 2018 03:04:20 GMT

the same variable in different datasets could be in different type. sometimes numeric, sometimes character. So, I want to add a step in my macro to check for variable type and convert it if necessary.

your solution did help converting the variable but is there a simpler way?

Thank you for your code! It did work!

Re: Check variable type then convert it to character

Tom — Thu, 11 Apr 2019 20:07:51 GMT

You probably need to fix this issue earlier in your process.

If your input files are CSV files then write a DATA step to read them. Do not use PROC IMPORT because that will have to guess at how to define the variables, and the results could be inconsistent among different source files that contain differently formatted data.

If you can't be certain whether the data set you're working with will have a numeric or character version of a variable, then you should be able to use the VVALUE() function to get character version of your variable.

This data step will create a character version of OLDVAR and will work whether OLDVAR is currently character or numeric.

data want ;
  set have ;
  length newvar $20 ;
  newvar=vvalue(oldvar);
  drop oldvar ;
  rename newvar=oldvar;
run;

From the VVALUE documentation: The PUT function enables you to reformat a specified variable or constant. VVALUE uses the current format that is associated with the variable.

Re: Check variable type then convert it to character

Kurt_Bremser — Tue, 23 Jan 2018 06:10:47 GMT

@leela214 wrote:
the same variable in different datasets could be in different type. sometimes numeric, sometimes character. So, I want to add a step in my macro to check for variable type and convert it if necessary.

your solution did help converting the variable but is there a simpler way?

Of course. Set the correct type and other attributes in the data step that reads the csv into SAS in the first place.

Re: Check variable type then convert it to character

RW9 — Tue, 23 Jan 2018 08:51:49 GMT

This makes no sense. You are in charge of your data. You import it it and set the formats and such like. The reason you are having problems is because you are declining to do this important step. Write a datastep, specify informats, formats, lengths and labels, make your data look the way you know it should, don't rely on guessing procedures like proc import. This is the way you handle data correctly, documented and repeatable.

Re: Check variable type then convert it to character

leela214 — Wed, 24 Jan 2018 03:21:37 GMT

Thank you for letting me know that I need to set the formats. I am very new to sas so I wouldn't say I'm declining to do it. It's more like I didn't know/how so I just did what I do know instead. I was taught that proc import is easier and faster when I want to import data into sas. Thank you for taking the time to read my post!

Re: Check variable type then convert it to character

leela214 — Wed, 24 Jan 2018 03:24:42 GMT

It worked! Thank you for this. Is there anything I need to watch out for or be careful of when I use the vvalue function? It's way too good to be truth. If this can convert numeric to char, why would we need put function for converting numeric to char?