BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jjames1
Fluorite | Level 6

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@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;

View solution in original post

15 REPLIES 15
jjames1
Fluorite | Level 6

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

But it might not be solving what I am looking for

 

ballardw
Super User

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
Fluorite | Level 6

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

Reeza
Super User

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
Super User

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
Fluorite | Level 6

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
Fluorite | Level 6

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
Opal | Level 21

@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
Super User

@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
Opal | Level 21

@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
Super User Tom
Super User

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
Fluorite | Level 6

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
Opal | Level 21

@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 

 

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
  • 15 replies
  • 32699 views
  • 5 likes
  • 7 in conversation