How to check if a variable is numeric and convert to a character?

[jjames1]

Hello,

I am doing XML mapping inorder to create my SAS Dataset. Once I create the SAS dataset I put them into my table after performing the transformation I wants. 

Here is my problem. Since I am reading from XML files, I have a particular variable_ABC that can sometimes be numeric or character. What I need is to insert this value into my final_table after tranformation, where  I have created the specific column corresponding to variable_ABC as character datatype. Now I have two scenarios

1.When the variable_ABC in XML is of type character, It runs successfully as variable_ABC  in my final_table is also of type character

2.When the variable_ABC in XML is of type numeric, I get the following error message:

ERROR: Value on the SELECT clause does not match the data type of the corresponding column listed after the INSERT table name.

As a soln to this problem I thought of doing a condition to check if the variable_ABC is numeric, then convert it into character; else keep variable_ABC as it is, So I make sure I convert everything into character before inserting into the final_table.

But I am not quite sure on how to write this condition as I am new to SAS.

Please help

Thanks in advance!

[kiranv_]

this link might be useful

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

[jjames1]

Thank you for the reply Kiran. This is a useful link 

But it might not be solving what I am looking for

[ballardw]

My first thought would be to go to the source of the xml file and ask them why a variable changes data types.

Any data sharing should have an agreement on data types and layout information.

You can use CALL VNEXT in a data step to identify variable type or select that information from Dictionary.columns but without knowing how you are generating your Where clause I'm not sure what else to suggest.

proc sql;

    select type

    from dictionary.columns

    where libname='YOULIB' and memname='DATSETNAME' and upcase(name) = 'YOURVARIABLENAME';

quit;

whether you need to joint that something you are doing or create a macro variable or what have you I'm not sure. You may want to look the results to see the types of values to play with from the dictionary table. Note that library and dataset names are stored in uppercase but variable names may be mixed case so structure your comparison values accordingly.

[jjames1]

Does using dictionary.columns for a large set of transformation affect the performance?

[Reeza]

You're only reading from dictionary.columns. At worst it'll be a bit slow if you have a lot of libraries and data defined. If this is the case, you can use proc contents to extract the same information.

[Reeza]

You should fix the XML maps. If you have a specific known and fixed structure your parsing routine should be automatically converting this to a character variable in the first place. 

[jjames1]

Unfortunately it does not have a fixed structure.

This particular variable is of type numeric or character based on the type of the xml file.

So from my point of solving this particular problem the ideal way would be to check if the variable is numeric or character.

Something like

%if  (isNumeric(myVar)) %then %do;
put(myVar,$5.);
%end

proc sql;
create table abc as 
  select myvar
  from xyz;
  quit;

Since I am a beginner in SAS I dont know how can I put it in SAS 

[jjames1]

Unfortunately it does not have a fixed structure.

This particular variable is of type numeric or character based on the type of the xml file.

So from my point of solving this particular problem the ideal way would be to check if the variable is numeric or character.

Something like

%if  (isNumeric(myVar)) %then %do;
put(myVar,$5.);
%end

proc sql;
create table abc as 
  select myvar
  from xyz;
  quit;

Since I am a beginner in SAS I dont know how can I put it in SAS 

[Patrick · Accepted solution]

@jjames1

Below a brute force method which should work - but it's certainly not good coding practice and the right thing to do is to either request a stable and defined XML from your data provider as @ballardw suggests or to deal with this instability in the XML map as @Reeza proposes.

The following code works because the SAS cats() function accepts both character and numeric variables but always returns a string.

data master;
  length myVar $5;
  myVar='1';
run;

data trans;
  myVar=2;
run;

proc sql;
  insert into master
    select 
      put(cats(myVar),$5.)
      from trans
  ;
quit;

[Reeza]

---

@Patrick wrote:

@jjames1

Below a brute force method which should work - but it's certainly not good coding practice and the right thing to do is to either request a stable and defined XML from your data provider as @ballardw suggests or to deal with this instability in the XML map as @Reeza proposes.

The following code works because the SAS cats() function accepts both character and numeric variables but always returns a string.

data master;
  length myVar $5;
  myVar='1';
run;

data trans;
  myVar=2;
run;

proc sql;
  insert into master
    select 
      put(cats(myVar),$5.)
      from trans
  ;
quit;

---

As long as there's no missing values. Otherwise the conversion will create a period and load that. I don't believe you can have missing values in an XML since it just excludes that value but depending on your processing it may be possible. 

[Patrick]

@Reeza

Good point. Option missing=' ' should circumvent this issue.

%let save_opt_miss=%sysfunc(getoption(missing));
options missing=' ';
proc sql;
  insert into master
    select 
      put(cats(myVar),$5.)
      from trans
  ;
quit;
options missing="&save_opt_miss";

[Tom]

Can you explain how you are creating the XML map you are using to convert the XML file into a SAS dataset?

Do the XML files actually change FORMAT from run to run? Or is it just that your process for generating the XML map has to guess at what type/length a variable should be based on the data that is available in the particular file that you are trying to import?

If the format is fixed and it is the guessing process that is generating the wrong type then stop guessing. First create a permanent map file that you use for all of the XML files. Then when you get a new XML file to import the output dataset will always have the same format.

[jjames1]

Hello Tom,

I am not using XML mapper instead I use the following piece of code for my purpose

filename xx temp;
libname xx xmlv2 '/folders/myfolders/temp.xml' automap=replace xmlmap=xx ;
proc copy in=xx out=zz noclone;
run;

[Patrick]

@jjames1

Also that it is very convenient to use automap=replace xmlmap=xx and let SAS do the guessing what variable types to use, it appears this is not the right approach for your case.

You will need to define an explicit and permanent XMLMAP instead of having SAS create one on-the-fly. Such an XMLMAP instructs SAS how to read the XML file. 

The SAS XML MAPPER utility will help you to create such an XMLMAP.

If you haven't done so already then you can download this utillity from here (no license required):

https://support.sas.com/downloads/package.htm?pid=1278