BEST19. format conversion to Char32 how to do this.

[sharonlee]

Hi,

I have data set A that has a variable formatted in CHAR32.

I have dataset B that has the same variable formatted in BEST19.

I'm trying to merge both datasets into one, but SAS will not let me because the formats are different.

I've tried converting dataset B variable from numeric to character using:

try1:

format var2 32..;

var2=var1;

doesn't work.  It truncates the variable, despite the variable only having 19 digits.

try2:

var2=put(var1, 32.);

doesn't work.  It truncates the variable, despite the variable only having 19 digits.

Then I tried to to convert dataset A from character to numeric:

try3:

var2=var1;

format var1 best19.;

doesn't work because SAS doesn't recognize the format best19. NOTE:484-185.

try4:

var=input(var, 19.);

Again, it truncates the variable.

Can anyone help me?

Thanks.

[SASKiwi · Accepted solution]

A SAS numeric variable can only hold around 15 digits accurately due to the precision limitations of using 8-byte floating point storage. If you have imported data into SAS from a 19-digit field it is likely that the digits beyond 15 are inaccurate. You will need to go back to source and re-import this a character variable.

[Patrick]

As long as the number of digits in the string can get converted with full precision into a SAS numerical variable below should work.

data char_tbl;
  length char_var $32;
  format char_var $char32.;
  char_var='12345';
  output;
  char_var='4567';
  output;
  stop;
run;

data num_tbl;
  length num_var 8;
  format num_var 19.;
  num_var='12345';
  output;
  num_var='8910';
  output;
  stop;
run;

proc sql;
  select *
  from 
    char_tbl c full outer join num_tbl n
    on input(c.char_var,best32.)=n.num_var
  ;
quit;

[Patrick]

Actually: You probably should also test for wrong matches due to not having full precision.

data char_tbl;
  length char_var $32;
  format char_var $char32.;
  char_var='12345';
  output;
  char_var='4567';
  output;
  char_var=repeat('9',16);
  output;
  stop;
run;

data num_tbl;
  length num_var 8;
  format num_var 19.;
  num_var='12345';
  output;
  num_var='8910';
  output;
  num_var=input(repeat('9',16),best32.);
  output;
  stop;
run;

proc sql;
  select 
    *, 
    case 
      when (not missing(n.num_var) and lengthn(left(c.char_var))>15)  
         then 'potential missmatch' 
      else ' ' 
      end as match_ind
  from 
    char_tbl c full outer join num_tbl n
    on input(c.char_var,best32.)=n.num_var
  ;
quit;

[Tom]

If the maximum actual value is larger than 9,007,199,254,740,992 then you will have trouble because that is the largest integer before SAS will start mapping some different integers to same floating point representation.

Anyway let's look in detail at what you tried.

format var2 32..;

var2=var1;

So assuming VAR2 did not already exist by attaching a numeric format to it you told the compiler to make it a number. So then when you assigned a character variable to it it should convert the value to a number.  That should work but you will get notes in the log that SAS had to convert the character variable to numeric.  

var2=put(var1, 32.);

So this time you appear to be assuming that VAR1 is numeric and you want to create VAR2 as character.  That is the reverse of what your first text tried.  The most likely problem with this is that the value of VAR2 generated will have leading spaces.  You can add the LEFT() function or add the -L modifier on the end of the format to have it generate the value left aligned instead of right aligned.  The only reason it would "truncate" VAR2 would be if VAR2 was defined with a length less than 32 before this statement.

var2=var1;

format var1 best19.;

doesn't work because SAS doesn't recognize the format best19. NOTE:484-185.

Most likely here you started with VAR1 as character.  You probably have copied the error message wrong here.  The BEST format definitely exists.  Most likely it told you the $BEST format does not exist.  You cannot attach a numeric format to a variable you have already defined as character so SAS will assume you just forgot to type the $ at the front of the format name.  If you just reverse the order you are back to your first try.

var=input(var, 19.);

Again, it truncates the variable.

This has a major logic flaw.  You are using VAR as the first argument to the INPUT() function. So VAR should be a character string. But you are also using VAR as the output of result of the INPUT() function call with a numeric informat. So VAR should be numeric.  So if VAR was CHAR the INPUT() function will work (but note it VAR was length 32 you told it to only read the first 19 of those characters) and then it will have to convert the result back into a charater string to store it back into the character variable. Since you didn't use the PUT() statement to do that conversion SAS will just use the BEST12. format to do the conversion.

So what do you need to do?

If you have VAR1 as character with a maximum length of 32 and you want to create number from it then use the INPUT() function with 32. as the informat.

numvar = input(charvar,32.);

If you have VAR1 as numeric (note that is does not matter what display format you have attached to it, the underlaying stored value is a floating point number) and you want to create an integer with at most 32 bytes (but see caveat at the top of this post) you can use the 32. format. You probably will want to left align the result instead of having a value like 10 stored with 30 leading spaces.

charvar=left(put(numvar,32.));

In general it is better to convert to numbers (when the values can be stored as numbers) and compare because with character values you need to worry about whether there are leading zeros or leading spaces that could confuse the comparison.

[sharonlee]

I imported the dataset again and during import I set the numeric variable into character length 32.
Thanks!!!