Why variable names are missing when I print SAS tables into CSV files?

Reply
Occasional Contributor
Posts: 7

Why variable names are missing when I print SAS tables into CSV files?

I have a SAS table containing about 2630+ variables and I would like to print it into a .csv file. Surprisingly, only 2522 variables are printed out normally. The rest of variables name are missing, although the values in each variable remain good there. Copied below is the SAS code I used to create csv files. Thanks in advance for any suggestions or comments.

proc export data=source  outfile ="&output.\source.csv"

dbms=csv replace;

run;

Super User
Posts: 19,789

Re: Why variable names are missing when I print SAS tables into CSV files?

Try using a data step to export and explicitly setting the LRECL in your file statement.

Super User
Super User
Posts: 7,042

Re: Why variable names are missing when I print SAS tables into CSV files?

Sounds like your variable names are averaging about 12 characters each.

Either use shorter variable names so that they all fit into the 32,767 character logical record length that PROC EXPORT supports.

Or use another method to write your CSV file. 

Occasional Contributor
Posts: 7

Re: Why variable names are missing when I print SAS tables into CSV files?

Thank you. But another question - Is there any way to get rid of 32,767 character logical record length when I create a macro variable? Copied below is the procedure I used to create a macro 'names' that contains the list of variables in the table. If I don't shortern my variable names, I would get an error message saying only the first 32,767 characters remain.

proc sql noprint;

select NAME

into :names separated by ","

from name

order by varnum;

quit;

Super User
Super User
Posts: 7,042

Re: Why variable names are missing when I print SAS tables into CSV files?

Nope.

But you do not need the variable names in a macro variable to be able to write them to a file. In fact it is easier if they are not.

data _null_;

   file "&output.\source.csv" dsd lrecl=300000 ;

   set name end=eof;

   by varnum ;

   put name @ ;

   if eof then put ;

run;

data _null_;

   file "&output.\source.csv" dsd lrecl=300000 MOD ;

   set source;

   put (_all_) (Smiley Happy;

run;

Respected Advisor
Posts: 3,799

Re: Why variable names are missing when I print SAS tables into CSV files?

You SHOULD be able to use a FILEREF with LRECL and have the ability to write the file without concern.  Or have PROC EXPORT look at the LRECL system option.  As far as I can tell from the data step written by PROC EXPORT there is not reason to limit lrecl to 32767.

The LRECL specified on the FILREF should be honored by PROC EXPORT omitting the option on the INFILE statement.  Using a FILEREF with LRECL worked in SAS 9.1.3, and was broken in 9.2  I think I have that right.

34         filename FT34F001 '~/testclass.csv' lrecl=50000;
35         proc export data=sashelp.class outfile=FT34F001 dbms=csv replace;
36            run;

NOTE:
Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to
WORK
.PARMS.PARMS.SLIST.
37          /**********************************************************************
38          *   PRODUCT:   SAS
39          *   VERSION:   9.3
40          *   CREATOR:   External File Interface
41          *   DATE:      26FEB15
42          *   DESC:      Generated SAS Datastep Code
43          *   TEMPLATE SOURCE:  (None Specified.)
44          ***********************************************************************/

45             data _null_;
46             %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
47             %let _EFIREC_ = 0;     /* clear export record count macro variable */
48             file FT34F001 delimiter=',' DSD DROPOVER lrecl=32767;
49             if _n_ = 1 then        /* write column names or labels */
50              do;
51                put
52                   "Name"
53                ','
54                   "Sex"
55                ','
56                   "Age"
57                ','
58                   "Height"
59                ','
60                   "Weight"
61                ;
62              end;
63            set  SASHELP.CLASS   end=EFIEOD;
64                format Name $8. ;
65                format Sex $1. ;
66                format Age best12. ;
67                format Height best12. ;
68                format Weight best12. ;
69              do;
70                EFIOUT + 1;
71                put Name $ @;
72                put Sex $ @;
73                put Age @;
74                put Height @;
75                put Weight ;
76                ;
77              end;
78             if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
79             if EFIEOD then call symputx('_EFIREC_',EFIOUT);
80             run;
Super User
Super User
Posts: 7,042

Re: Why variable names are missing when I print SAS tables into CSV files?

Posted in reply to data_null__

It would be much easier to fix if SAS would supply the source code for the SCL that PROC EXPORT is calling.

Respected Advisor
Posts: 3,799

Re: Why variable names are missing when I print SAS tables into CSV files?

They broke it why should we have to fix it.  But since I'm the only person in the world that uses FILEREFs it doesn't really matter.:smileymischief:

Occasional Contributor
Posts: 7

Re: Why variable names are missing when I print SAS tables into CSV files?

Posted in reply to data_null__

Thanks. I will give it a try although I am not familiar with FILREF.:smileysilly:

Respected Advisor
Posts: 3,799

Re: Why variable names are missing when I print SAS tables into CSV files?

My point is that even that (fileref) won't fix it.  You will have to modify the code generated by PROC EXPORT to fix the problem.  That is easy.

Super User
Super User
Posts: 7,950

Re: Why variable names are missing when I print SAS tables into CSV files?

Completely off topic, but what exactly are you intending to acheive with a dataset with 2k + variables in?  It sounds to me like the dataset could use some thought before any attempt to export data as its pretty unwieldy.  Maybe consider using some relational methodology. 

Occasional Contributor
Posts: 7

Re: Why variable names are missing when I print SAS tables into CSV files?

I will hand it over to another person for analysis and .csv file is the only format accepted.

Super User
Super User
Posts: 7,950

Re: Why variable names are missing when I print SAS tables into CSV files?

Yes, I wasn't talking about changing the file format, but asking why you are trying to work with dataset which has 2k + columns.  Take an example, in my area we use SDTM model data.  We could just create one extremely large dataset with all subjects/visits/domains stretching out across and down the page.  It may work in some instances, however its not really a good way of doing things.  So we split the data out into domains or groups of related information, and also normalise the data so we have many observations rather than variables.  If I was to receive a file with 2k + columns, or even in fact 200 columns, stage 1 for me would be to break that file down into workable chunks.

Respected Advisor
Posts: 3,799

Re: Why variable names are missing when I print SAS tables into CSV files?

Seems to me the relevant point is why does PROC EXPORT hard code an unreasonable LRECL and perhaps more alarming use the DROPOVER option silently discarding data.

The deficiencies in PROC EXPORT are demonstrated by the OPs data set with many variables but it would be just as easily demonstrated by a SAS data set with fewer long character variables.

data badexport;
   length x y $32767;
   substr(x,
32767)='x';
   substr(x,
1,1) = 'x';
   substr(y,
32767)='y';
   substr(Y,
1,20) = 'YYYYYYYYYYYYYYY';
  
run;

filename FT61F001 '~/badexport.csv' lrecl=1000000;

proc export data=badexport outfile=FT61F001 dbms=csv replace;
  
run;


2-27-2015 9-54-58 AM.png
Ask a Question
Discussion stats
  • 13 replies
  • 629 views
  • 0 likes
  • 5 in conversation