Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Contributor
Posts: 47
Accepted Solution

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

Hi

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. ;

                         ________________________________

                         22

                         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. ;

                         __________________________________________

                         49

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. ;

                         __________________________________________

                         22

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


Accepted Solutions
Solution
‎01-28-2015 01:13 AM
Trusted Advisor
Posts: 1,248

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

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

View solution in original post


All Replies
Super User
Posts: 17,831

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

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?

Contributor
Posts: 47

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

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.

Solution
‎01-28-2015 01:13 AM
Trusted Advisor
Posts: 1,248

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

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

Trusted Advisor
Posts: 1,248

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

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 procedu...

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

Contributor
Posts: 47

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

Thanks Michelle.  Let's hope our install of 9.4 happens soon then Smiley Happy  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.

Super User
Posts: 17,831

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

Well that sucks Smiley Sad

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.

Super User
Super User
Posts: 6,500

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

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.

Contributor
Posts: 47

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

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 1409 views
  • 6 likes
  • 4 in conversation