02-26-2015 11:57 AM
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"
02-26-2015 12:12 PM
02-27-2015 10:00 AM
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;
into :names separated by ","
order by varnum;
02-27-2015 10:19 AM
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.
file "&output.\source.csv" dsd lrecl=300000 ;
set name end=eof;
by varnum ;
put name @ ;
if eof then put ;
file "&output.\source.csv" dsd lrecl=300000 MOD ;
put (_all_) (;
02-26-2015 01:48 PM
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.
02-26-2015 07:44 PM
It would be much easier to fix if SAS would supply the source code for the SCL that PROC EXPORT is calling.
02-27-2015 08:09 AM
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:
02-27-2015 10:17 AM
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.
02-27-2015 08:36 AM
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.
02-27-2015 10:29 AM
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.
02-27-2015 10:57 AM
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.