topic Re: Proc export CSV with 'xxx'n field names 30 to 32 characters long in SAS Procedures

Proc export CSV with 'xxx'n field names 30 to 32 characters long

tammy_dezilva — Wed, 28 Jan 2015 04:52:34 GMT

I have discovered a problem where if I have a field name that is 30 to 32 characters long and in the format 'xx'n rather than a standard SAS field name then it gives me an error when trying to export to CSV. It seems that Proc export code that is generated is taking the quotes and n as part of the field length and therefore the full field is not being output in the generated code.

I am using SAS 9.2 and below is the code I have used

data work.test;

This_Field_HasThirtyCharacters=30;

ThisFieldHasThirtyOneCharacters=31;

'Another has twenty nine chars'n=29;

'Another has thirty characterss'n=30;

'Another has thirty1 characterss'n=31;

run;

proc export data=work.test

dbms=csv

replace

outfile="\\sas\test.csv";

run;

with a snippet of the log showing the error below:

53 set WORK.TEST end=EFIEOD;

54 format This_Field_HasThirtyCharacters best12. ;

55 format ThisFieldHasThirtyOneCharacters best12. ;

56 format "Another has twenty nine chars"N best12. ;

NOTE: SCL source line.

57 format "Another has thirty characterss" best12. ;

________________________________

200

ERROR 22-322: Syntax error, expecting one of the following: a name, ;, DEFAULT, _ALL_, _CHARACTER_, _CHAR_, _NUMERIC_.

ERROR 200-322: The symbol is not recognized and will be ignored.

58 format "Another has thirty1 characterss best12. ;

NOTE: SCL source line.

58 format "Another has thirty1 characterss best12. ;

__________________________________________

NOTE 49-169: The meaning of an identifier after a quoted string may change in a future SAS release. Inserting white space between

a quoted string and the succeeding identifier is recommended.

NOTE 141-205: SCL source line.

58 format "Another has thirty1 characterss best12. ;

__________________________________________

ERROR 22-322: Syntax error, expecting one of the following: a name, ;, DEFAULT, _ALL_, _CHARACTER_, _CHAR_, _NUMERIC_.

NOTE: SCL source line.

58 format "Another has thirty1 characterss best12. ;

__________________________________________

200

ERROR 200-322: The symbol is not recognized and will be ignored.

Has anyone else encountered this problem?

thanks

Tammy

Re: Proc export CSV with 'xxx'n field names 30 to 32 characters long

Reeza — Wed, 28 Jan 2015 05:03:51 GMT

Line 57 is missing the n.

The error leads me to think syntax error somewhere in the code instead, either a missing n or mismatched quotation marks somewhere.

Perhaps post the code actually run?

Re: Proc export CSV with 'xxx'n field names 30 to 32 characters long

tammy_dezilva — Wed, 28 Jan 2015 06:01:15 GMT

That was the code actually run. The error has occurred with the generated code behind the scenes from proc export as I don't have those format statements - I simply have the data step and proc export code and nothing else. It looks like proc export has a bug in that the generated code is chopping off fields that are in the 'xx'n format by not taking into consideration that the quotes and n will be an extra 3 characters to the maximum 32 characters allowed on a field name. Well..that is how it is appearing to me? The field name 'Another has twenty nine chars'n goes ok as 29 plus the two quotes and n add up to the 32 characters.

If you take my exact code that I have above and run it (well in a v9.2 environment at anyway), you should get the same result.

Re: Proc export CSV with 'xxx'n field names 30 to 32 characters long

MichelleHomes — Wed, 28 Jan 2015 06:13:46 GMT

Hi Tammy,

It seems to be truncating as you discovered... I found a usage note stating this is also a problem with exporting labels - 40573 - EFI, Export Wizard, and Proc Export truncate labels at 32 characters The workaround is to use ODS CSV with proc print. This might help overcome the issue you're experiencing.

Cheers,

Michelle

Re: Proc export CSV with 'xxx'n field names 30 to 32 characters long

MichelleHomes — Wed, 28 Jan 2015 06:20:50 GMT

It seems the ability to use variable named literals with PROC EXPORT is fixed in SAS 9.4 despite the VALIDMEMNAME option...

41788 - VALIDMEMNAME=EXTEND option is not recognized when you use EFI, the IMPORT and EXPORT procedures, and the Import/Export Wizard

SAS(R) 9.3 System Options: Reference, Second Edition - VALIDMEMNAME documentation

Re: Proc export CSV with 'xxx'n field names 30 to 32 characters long

Reeza — Wed, 28 Jan 2015 07:44:09 GMT

Well that sucks

You can write or create a data step that will generate a CSV file for you painlessly if ODS CSV isn't an option for some reason.

Re: Proc export CSV with 'xxx'n field names 30 to 32 characters long

tammy_dezilva — Wed, 28 Jan 2015 21:27:13 GMT

Thanks Michelle. Let's hope our install of 9.4 happens soon then I will give ODS CSV a go, but from past experience on large files this is extremely slow so I might have to revert to doing a data step as Reeza mentions.

Re: Proc export CSV with 'xxx'n field names 30 to 32 characters long

Tom — Wed, 28 Jan 2015 23:06:07 GMT

There is no need to use PROC EXPORT or ODS CSV to write CSV files. You can do it with a simple generic Data Step.

Here is one method that uses PROC SQL metadata query to generate the header line.

You can even have the data step generate the header line without any extra steps.

Re: Proc export CSV with 'xxx'n field names 30 to 32 characters long

tammy_dezilva — Wed, 28 Jan 2015 23:35:19 GMT

thanks Tom. I'll give that a try. Our server also has bad performance issues (our new environment couldn't come sooner). I killed the ods csv step after 1.5 hours as that was just too long.