Difficulty saving user defined format

[frupaul]

Hi Everyone,

I have recently created a user-defined format which I have used in formatting a variable on my table. The format works well on temporary tables and reformats the values as needed, but when I try to save the table with formatted values to our Teradata database it strips out the formats and saves values unformatted.

I am not sure if anyone knows why this is happening or how to address it.

Thanks,

[RW9]

Have you tried any of the topis which come up from a Google search?

https://support.sas.com/resources/papers/sgf09/338-2009.pdf

http://support.sas.com/documentation/cdl/en/indbug/68170/HTML/default/viewer.htm#p10m7bigg3hsgbn1f6v...

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a003276900.htm

Personally I avoid having formats for any data which is not stored only as SAS data - i.e. CSV, databases, transfer files etc. I would always put the full values.  But then I am really not a fan of proprietary binary file formats at all.

[Tom · Accepted solution]

What did you expect SAS to do in that situation?  

When you attach a format to a variable is SAS it just stores the format specification (ie:  'COMMA10.2' ) into the metadata of the dataset. There is no place in Teradata to store that and Teradata wouldn't know what it meant anyway.

If you want to use the formats with the table in SAS then just make sure to re-attach the format with a FORMAT statement.

libname mydb teradata .... ;
proc print mydb.mytable;
  format myvar myfmt.;
run;

Otherwise use the format to generate a new character variable withe the decoded value of the variable and store that in Teradata.

data mydb.mytable;
  set mysas.mytable;
  var1_decode = put(var1,fmt.);
run;