Export to Txt, column names with spaces

Reply
Super Contributor
Posts: 418

Export to Txt, column names with spaces

Hello everyone. I have a sas dataset that I created using the Validvarname=ANY option within the system. As such, quite a few of my column names have spaces or punctuation in them.

Ex.) FI Section 7    Issue Revision No., etc...

However when trying to proc export this to tab delimited text file (or csv file for that manner), SAS is throwing an error because it is not enclosing each variable within the  " "n framewwork.

Aka it is putting the following error.

3470            put Brandon Issue No. @;

                                                  ^

ERROR: Format $No was not found or could not be loaded

Does anyone know a setting in SAS that will allow you to proc export to output files given that the file names have spaces in them?

Thank you!

Brandon

Frequent Contributor
Posts: 129

Re: Export to Txt, column names with spaces

Posted in reply to Anotherdream

Brandon,

What OS and version of SAS are you running?  I am on windows with SAS9.2 and Access to PCFiles and the following code works as expected:

options validvarname=any ;
  data test ;
     data test ;
     'Brandon Issue No.'n = 5 ;
  run ;

  proc export data=test
              file = 'my_test'
              dbms = csv ;

        run ;

The log shows that the generated code does include the appropriate named string syntax.

1387      data _null_;

1388      %let _EFIERR_ = 0; /* set the ERROR detection macro variable */

1389      %let _EFIREC_ = 0;     /* clear export record count macro variable */

1390      file 'my_test' delimiter=',' DSD DROPOVER lrecl=32767;

1391      if _n_ = 1 then        /* write column names or labels */

1392       do;

1393         put

1394            "Brandon Issue No."

1395         ;

1396       end;

1397     set  TEST   end=EFIEOD;

1398         format "Brandon Issue No."N best12. ;

1399       do;

1400         EFIOUT + 1;

1401         put "Brandon Issue No."N ;

1402         ;

1403       end;

1404      if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */

1405      if EFIEOD then call symputx('_EFIREC_',EFIOUT);

1406      run;

Super Contributor
Posts: 418

Re: Export to Txt, column names with spaces

Posted in reply to LarryWorley

That's strange. I am on windows 7 (now, just upgraded), with 9.2.  However I don't believe I have the Access to PCFiles you mention (unless this comes base with 9.2 and in that case i've just never used it).

Perhaps I have a corrupted install, I have seen other problems that people say they do not also have...

I'm going to have my IT team un-install the program and see if the error still persists, I will keep you posted.

Frequent Contributor
Posts: 129

Re: Export to Txt, column names with spaces

Posted in reply to Anotherdream

If the re-install does not fix, then I would suggest checking with SAS tech support.

Proc Export is available with base SAS, but it might work differently than the proc export on steroids that comes with Access to PCFiles.

Good Luck!

Super User
Super User
Posts: 7,035

Re: Export to Txt, column names with spaces

Posted in reply to Anotherdream

I find that PROC EXPORT is not worth the effort for delimited files.  You can export the file yourself using a data step.  If you want to include variable names then it is simple to do with a quick PROC TRANSPOSE on zero records to get the names and an extra data step.

* Setup sample dataset with spaces in variable names ;

options validvarname=any;

data class;

  'Name with Space'n = _n_;

  set sashelp.class;

run;

* Set options for export ;

filename tmpfile1 temp ;

%let dsn=class;

%let fname=tmpfile1;

%let varlist=_all_;

* Export file ;

proc transpose data=&dsn (obs=0) out=names;

  var &varlist;

run;

data _null_;

  file &fname dsd dlm=',' lrecl=10000 ;

  set names;

  put _name_ @;

run;

data _null_;

  file &fname mod dsd dlm=',' lrecl=10000 ;

  set &dsn ;

  put (&varlist) (:) ;

run;

* Show content of exported file ;

data _null_;

  infile &fname ;

  input ;

  list;

run;


NOTE: The infile TMPFILE1 is:

      (system-specific pathname),

      (system-specific file attributes)

RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0

1         Name with Space,Name,Sex,Age,Height,Weight 42

2         1,Alfred,M,14,69,112.5 22

3         2,Alice,F,13,56.5,84 20

4         3,Barbara,F,13,65.3,98 22

5         4,Carol,F,14,62.8,102.5 23

6         5,Henry,M,14,63.5,102.5 23

7         6,James,M,12,57.3,83 20

8         7,Jane,F,12,59.8,84.5 21

9         8,Janet,F,15,62.5,112.5 23

10        9,Jeffrey,M,13,62.5,84 22

11        10,John,M,12,59,99.5 20

12        11,Joyce,F,11,51.3,50.5 23

13        12,Judy,F,14,64.3,90 20

14        13,Louise,F,12,56.3,77 22

15        14,Mary,F,15,66.5,112 21

16        15,Philip,M,16,72,150 21

17        16,Robert,M,12,64.8,128 23

18        17,Ronald,M,15,67,133 21

19        18,Thomas,M,11,57.5,85 22

20        19,William,M,15,66.5,112 24

NOTE: 20 records were read from the infile (system-specific pathname).

      The minimum record length was 20.

      The maximum record length was 42.

Ask a Question
Discussion stats
  • 4 replies
  • 1419 views
  • 1 like
  • 3 in conversation