SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 89
Accepted Solution

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

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.


Accepted Solutions
Solution
‎09-13-2012 02:13 AM
Super Contributor
Posts: 349

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

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

View solution in original post


All Replies
Super Contributor
Posts: 349

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

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.

report.png

Thanks,

Shiva

Frequent Contributor
Posts: 89

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

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

Solution
‎09-13-2012 02:13 AM
Super Contributor
Posts: 349

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

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 900 views
  • 0 likes
  • 2 in conversation