I am using DI Studio to create a job to export a data set as a csv file. I am using the File Writer transformation. The job exports the data set and creates the csv file, but I am not getting the column headings in the data set as the first row in the csv file. What do I have to do in my DI job to make the column heading show up. Here is the code generated by the job
if &error gt input(symget('trans_rc'),12.) then
if &error gt input(symget('job_rc'),12.) then
Using the File Writer I couldn't find a way to write the variable names as first line.
What you could do:
Use a DATA _NULL_ step as pre-code and write the first line with variable names there.
Under Options\File Writer add "mod" so that the second data step appends the data to the external file.
I believe this would work - also that everything becomes quite a bit static (i.e. changing the order of variables in the source would cause issues).
Another way could be to use a "user written code" transformation with code generation mode "user written body". Doing some macro looping over the generated macro vars (containing variable names etc) you could generate the data _null_ step writing the external file. This needs some coding but would be more dynamic.
I am sorry, I have not tried this yet, I got pulled into something else, but I'll definitely let you know how it goes. It is surprising that I cannot export the field names as the first row in the CSV file using the DI Studio and and keeping it dynamic. In any case I'll post the results.
If the SAS Utility macros are available to your DI Studio servers, then this macro %DS2CSV allows you to place labels in row 1 of the output file. For example:
%ds2csv (data=sashelp.retail, runmode=b, csvfile=c:\temp\retail.csv,
Of course one can always write SAS code in DIS but I believe that with DIS one should try as far as possible to use standard transformations.
As soon as one starts to code some of the advantages using DIS are lost (i.e. that the external file metadata also defines in what order and format the data is written and that changing metadata also changes the SAS code generated).
I love coding and it cost me quite a bit of effort to accept the DIS way of doing things. But I can also see the advantages.
I was searching for a solution to this same problem and found this thread. I was able to find a partial solution. I'm using DI Studio 4.21 on a windows server. To get the header line to appear in the csv file follow these steps:
open the properties of the csv table
select the 'File Parameters' tab
Find the 'External file records' section and change the 'Start record:' to 2
This should get you the header row.
My problem is that my file is very wide and I've changed the lrecl option to match but I'm getting a warning when the header row is written. It is too wide.
This is the file statement:
file 'C:\files\updates\file_output.csv' lrecl=2000 dlm=',';
And this is the warning:
WARNING: The quoted string currently being processed has become more than 262 characters long. You may have unbalanced quotation marks.
So, if your file is not too wide, then this will work. Otherwise, you are stuck with me (unless somebody can solve this part).
Use option NOQUOTELENMAX (http://support.sas.com/kb/00/888.html) and the warning goes away.
"Find the 'External file records' section and change the 'Start record:' to 2"
I would have thought this results only in a "firstobs=2" in the infile statement - seems that something else is happening as well.
The code Cynthia has posted works for me and I have not had problems with using it. I also tried the advanced options you mentioned but it did not work, because I needed all the observations and not observations starting at record 2.
Agreed - it does not appear that would be the solution but it works. I'm still trying to determine the 'why' and figure out the pieces. When I was testing and defined the csv from an existing file, it defaulted to this. I'm concluding it was because ine one was selected for the header definitions on the infile. This then becomes the default for writing the file. That's all I've got; still pretty new to DI Studio.
Thanks for the options statement; I knew it was an options but could not remember /find the correct one. It worked nicely.
For those who are browsing this thread and not the OP who has found a solution. Setting the infile to 2 will not exclude any observations. Since this discussion is dealing with a csv file, the first line is the header row and the actual data lines start on line 2. You can test this for yourself to verify. The OP may have a different csv definition, not really sure why it did not work. I used it to define the csv table and saw that this became the default on the properties.
Message was edited by: kas