Enhancing CSV-file output to Excel (w/user written code in DI Studio)

[TurnTheBacon]

Hello,

I've almost finished creating a new job in DI Studio, and would greatly appreciate some last minute advice about enhancing the output (which will be displayed in Excel).

The job creates a CSV-file using proc export data=&tablename. outfile="SASEnvironment/Temp/&reportname..csv" dbms=dlm replace;. Although the CSV-file doesn't need to look very good, I'd definitely like to increase the column width so that all the values are entirely visible, to make the first row bold, and to exclude two of the variables. What would be an efficient way to do this in the User Written code?

Alas I'm not quite up-to-speed on the DATA and PROC statements yet, nor on ODS. :smileyplain:

Thanks for your time.

[shivas]

Hi,

No need of writing user written code for exporting the dataset to csv file..

SAS DI provides transformation like File Writer and this transformation writes data to external file and in this tranformation you add more length as per your requirement.

Thanks,

Shiva

[TurnTheBacon]

Thanks, but I need to do in in the User Written code for unrelated reasons.

[shivas · Accepted solution]

Hi,

Try using data-step code rather than proc export...to drop some variables.

    data _null_;

     attrib Name length = $30;

      attrib Sex length = $2;/* remove this for dropping the variable*/

      attrib Age length = 8;

      attrib Height length = 8;

      attrib Weight length = 8;

    file 'F:\aa1.csv' delimiter=',' DSD DROPOVER lrecl=32767;

    if _n_ = 1 then        /* write column names or labels */

     do;

       put

          "Name"

       ','

          "Sex" /* remove this for dropping the variable*/

       ','      /* remove this for dropping the variable*/

          "Age"

       ','

          "Height"

       ','

          "Weight"

       ;

     end;

   set  SASHELP.Class   end=EFIEOD;

        do;

       EFIOUT + 1;

       put Name $ @;

       put Sex $ @;/* remove this for dropping the variable*/

       put Age @;

       put Height @;

       put Weight ;

       ;

     end;

    if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */

    if EFIEOD then call symputx('_EFIREC_',EFIOUT);

    run;

Thanks,

Shiva