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

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

TurnTheBacon — Wed, 12 Sep 2012 20:05:25 GMT

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.

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

shivas — Thu, 13 Sep 2012 04:32:53 GMT

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

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

TurnTheBacon — Thu, 13 Sep 2012 04:56:03 GMT

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

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

shivas — Thu, 13 Sep 2012 06:13:01 GMT

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