I am trying to migrate my SAS export script to Bteq export script.
Thorugh SAS it generates file in below fixed width format
When you multiply the inc-amt by 100 to get the pence, you are multiplying a decimal field with two decimal places by an integer, so the answer is still a decimal with two decimal places.
You need to give this a numeric format before casting it, so
CAST ((inc_amt*100 (format 9(15))) AS CHAR(15)) (title ''),
Thank you so much Jimm.
Only one issue :(
After using below query i am getting almost the desired output except very last column still coming as Char(13) , ideally there should be one more space . Like in below example after atm Incentive there should be an space but just after 'e' its going to next line even after i am putting it as char(14)
Its a problem with BTEQ - when you export report, any trailing space on a line is truncated - it does not show up on the paper!
You need to change to export data. The separator, titles/ titledashes are then superfluous, but I would leave them on with a comment!
Your script is ok, but make sure all future fields are CHAR fields, not VARCHAR. A character constant is VARCHAR unless you tell SQL explicitly!
I changed to below but it does not work
Oh ok if i am getting it correctly . Are you asking me to change the delimiter? If yes then this not the option because i will have to send this file to other server which expects only fixed width file :(
When you export data, you dont have a delimiter unless you export one as part of the query.
When you say it does not work, what is happening; what error is reported from bteq.
A couple of likely causes:
.export data = /prod/user/sam/us/bank/bankma/non_npi/ghp501/iFRE_Testing/Bteq/Payment_file.txt
.export data file = /prod/user/sam/us/bank/bankma/non_npi/ghp501/iFRE_Testing/Bteq/Payment_file.txt
Export data will always output in recordmode, so remove the ".set recordmode off" (I thought it would just ignore it.)
Its working but giving some weird result. Sorry to bother you but do you know how to correct the format