topic Re: Difficulty saving user defined format in SAS Programming

Difficulty saving user defined format

frupaul — Tue, 11 Dec 2018 13:04:44 GMT

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,

Re: Difficulty saving user defined format

RW9 — Tue, 11 Dec 2018 13:27:59 GMT

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#p10m7bigg3hsgbn1f6v5tzjxcyl8.htm

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.

Re: Difficulty saving user defined format

Tom — Tue, 11 Dec 2018 14:01:45 GMT

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;