BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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

1 ACCEPTED SOLUTION

Accepted Solutions
MichelleHomes
Meteorite | Level 14

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

//Contact me to learn how Metacoda software can help keep your SAS platform secure - https://www.metacoda.com

View solution in original post

8 REPLIES 8
Reeza
Super User

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?

tammy_dezilva
Quartz | Level 8

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.

MichelleHomes
Meteorite | Level 14

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

//Contact me to learn how Metacoda software can help keep your SAS platform secure - https://www.metacoda.com
MichelleHomes
Meteorite | Level 14

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

//Contact me to learn how Metacoda software can help keep your SAS platform secure - https://www.metacoda.com
tammy_dezilva
Quartz | Level 8

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.

Reeza
Super User

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.

Tom
Super User Tom
Super User

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.

tammy_dezilva
Quartz | Level 8

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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