DI Studio and external file

[deleted_user]

Hello All,
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

/******************************************************************************
* Job: Test_CSV A5HUSR61.BR0001LL *
* Description: *
* *
* Metadata Server: 340srv10 *
* Port: 8562 *
* Location: /shri_test/Exceed Data *
* *
* Server: SASApp A5HUSR61.AR000002 *
* *
* Source Table: ABSENT_LATE_REASON_DIM - A5HUSR61.B6000002 *
* Current.ABSENT_LATE_REASON_DIM *
* Target Table: Test_File2 - A5HUSR61.CB0000S3 *
* D:\DATA\CPO_DROP\ED_Test\exceed_Test2 *
* .csv *
* *
* Generated on: Friday, May 7, 2010 8:52:28 AM EDT *
* Generated by: sas@340SRV10 *
* Version: SAS Data Integration Studio 4.2 *
******************************************************************************/

/* General macro variables */
%let jobID = %quote(A5HUSR61.BR0001LL);
%let etls_jobName = %nrquote(Test_CSV);
%let etls_userID = %nrquote(sas@340SRV10);

/* Setup to capture return codes */
%global job_rc trans_rc sqlrc;
%let sysrc = 0;
%let job_rc = 0;
%let trans_rc = 0;
%let sqlrc = 0;
%global syserr;

%macro rcSet(error);
%if (&error gt &trans_rc) %then
%let trans_rc = &error;
%if (&error gt &job_rc) %then
%let job_rc = &error;
%mend rcSet;

%macro rcSetDS(error);
if &error gt input(symget('trans_rc'),12.) then
call symput('trans_rc',trim(left(put(&error,12.))));
if &error gt input(symget('job_rc'),12.) then
call symput('job_rc',trim(left(put(&error,12.))));
%mend rcSetDS;

/* Create metadata macro variables */
%let IOMServer = %nrquote(SASApp);
%let metaPort = %nrquote(8562);
%let metaServer = %nrquote(340srv10);

/* Set metadata options */
options metaport = &metaPort
metaserver = "&metaServer";

/* Setup for capturing job status */
%let etls_startTime = %sysfunc(datetime(),datetime.);
%let etls_recordsBefore = 0;
%let etls_recordsAfter = 0;
%let etls_lib = 0;
%let etls_table = 0;

%global etls_debug;
%macro etls_setDebug;
%if %str(&etls_debug) ne 0 %then
OPTIONS MPRINT%str(;);
%mend;
%etls_setDebug;

/*============================================================================*
* Step: File Writer A5HUSR61.$0000021 *
* Transform: File Writer *
* Description: *
* *
* Source Table: ABSENT_LATE_REASON_DIM - A5HUSR61.B6000002 *
* Current.ABSENT_LATE_REASON_DIM *
* Target Table: Test_File2 - A5HUSR61.CB0000S3 *
* D:\DATA\CPO_DROP\ED_Test\exceed_Test2 *
* .csv *
*============================================================================*/

%let transformID = %quote(A5HUSR61.$0000021);
%let trans_rc = 0;
%let etls_stepStartTime = %sysfunc(datetime(), datetime20.);

%let SYSLAST = %nrquote(Current.ABSENT_LATE_REASON_DIM);

data _null_;
set &SYSLAST;
attrib ABSENT_LATE_REASON_SK length = 8
format = 7.
informat = 7.;
attrib SCHOOL_ID length = $20
format = $20.
informat = $20.;
attrib ABSENT_LATE_REASON_CODE length = $6
format = $6.
informat = $6.;
attrib ABSENT_LATE_REASON_DESC length = $20
format = $20.
informat = $20.;
attrib ABSENT_LATE_REASON_SHORT_DESC length = $7
format = $7.
informat = $7.;
attrib AUTHORIZED_FLG length = $1
format = $1.
informat = $1.;
attrib EXCUSED_FLG length = $1
format = $1.
informat = $1.;
quote='"';
file 'D:\DATA\CPO_DROP\ED_Test\exceed_Test2.csv' dlm=',';

put
quote +(-1) ABSENT_LATE_REASON_SK +(-1) quote
quote +(-1) SCHOOL_ID +(-1) quote
quote +(-1) ABSENT_LATE_REASON_CODE +(-1) quote
quote +(-1) ABSENT_LATE_REASON_DESC +(-1) quote
quote +(-1) ABSENT_LATE_REASON_SHORT_DESC +(-1) quote
quote +(-1) AUTHORIZED_FLG +(-1) quote
quote +(-1) EXCUSED_FLG +(-1) quote
;

run;

%rcSet(&syserr);


Thanks for your help

Shri

[Patrick]

Hi Shri

The following for DI 4.2:

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.

HTH
Patrick

[Patrick]

And sometimes it would be nice to get some feedback in order to know if answers are also read by the poster or if answering this specific person is only a waste of time.

[deleted_user]

Patrick,
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.

Thanks
Shri

[Cynthia_sas]

Hi:
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:
[pre]
%ds2csv (data=sashelp.retail, runmode=b, csvfile=c:\temp\retail.csv,
labels=y, formats=y);
[/pre]

Documentation here:
http://support.sas.com/documentation/cdl/en/lrdict/62618/HTML/default/a002683390.htm

cynthia

[deleted_user]

Thank you so much Cynthia, You saved my day, this is clean and easy to get the column names to the csv file.

[Patrick]

Hi Cynthia

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.

Patrick

[kas]

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
select 'Advanced'
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).

Karen

[Patrick]

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.

[deleted_user]

Karen,
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.

Shri Message was edited by: sksastry

[kas]

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.

[kas]

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