I am trying to export a sas dataset to a CSV file. The dataset has 4,048 variables and 57,868 records. All the variables are exported correctly, but for some reason, the header gets truncated. Only the first 3,366 names are exported to the first records. Any idea why? This is how the header looks Var4570Post,,,
A few commas were added to the end, not sure why, but I should have another 682 variables in the header record.
Any clue?? Is there a limit of variables to export? Should I try another method instead of proc export? Suggestions? Thanks in advance!!
It's using the default LRECL which is cutting off. Try a manual export instead:
https://communities.sas.com/t5/SAS-Procedures/Proc-Export-for-large-files/td-p/188894
Not sure if ODS CSV would be helpful here.
It's using the default LRECL which is cutting off. Try a manual export instead:
https://communities.sas.com/t5/SAS-Procedures/Proc-Export-for-large-files/td-p/188894
Not sure if ODS CSV would be helpful here.
There's a problem with proc export and lots of variables, as the put statement for the header exceeds the maximum length of a SAS statement (32767).
Create a dataset of variable names from dictionary.columns (or sashelp.vcolumn), and use call execute in a data _null_ step off that dataset to create your export data step dynamically.
Another thought, could you not re-structure your data? It sounds like you have a normalised dataset - i.e. data goes across (as 57k records is not much but 4k variables is). If I took this data:
ID WEEK1 WEEK2 WEEK3...
1 34 12 56
And re-structured it to:
ID WEEK_NO RESULT
1 1 34
1 2 12
1 3 56
...
The data is effectively the same, however th import/export and programming work with this data is far simpler. Then if I need a transposed dataset for output a simple proc transpose can achieve this.
As others already pointed out there is a limitation how many characters of header Proc Export can write.
As @Reeza already hinted using ODS could be an alternative.
Below code worked for me:
data have;
firstVar='AAAA';
array allMyVars_0123456789012345_ {4048} 3. (4048*1);
/* do i=1 to 57868;*/
do i=1 to 100;
output;
end;
stop;
run;
ods _all_ close;
ods tagsets.csv file='c:\temp\test.csv';
proc print data=have noobs;
run;
ods csv close;
ods listing;
While the ODS method works, the following data step method worked as well AND ran about 500 times faster:
%let flname=have; %let outfname=/folders/myfolders/testit3.csv; proc transpose data=sashelp.vcolumn (where=(libname eq upcase("WORK") and memname eq upcase("&flname."))) out=vcolumnt (drop=_NAME_ _LABEL_); var name; run; data _null_; file "&outfname." dlm=',' dsd lrecl=2000000; set vcolumnt; put (_all_) (+0); run; data _null_; file "&outfname." dlm=',' dsd lrecl=2000000 mod; set &flname.; /* format _all_; */ put (_all_) (+0); run;
Art, CEO, AnalystFinder.com
The advantage of using ODS is that it also does the quoting of character variables inclusive treatment of embedded quotes in a string.
Another option would be utility macro %ds2csv()
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.