Preserving leading blanks in variable when exporting to CSV

[ginak]

Hi all,

I did a thorough search of the message boards before deciding to finally post because I am still confused. Help! I created a dataset of results in SAS to export to Excel via csv file. However, it removes my trailing blanks. I posted what I want it to look like in SAS, and what it looks like in the excel screenshot. Notice that it also removes the trailing 0 in "0.40"

How can I keep these? I'll paste part of my code. Here, race5 is our "&xvar" and you can see below where I added the leading blanks.

%macro simple(xvar, output);
%do i = 1 %to 2;
/*%do i = 1 %to %sysfunc(countw(&outcomes));*/
%let tabit = %scan(&outcomes, %eval(&i));

title "I. y= &tabit , x =&xvar";

data comb4 (keep = NOBsUsed NOBsRead Run value effect2 pval count variable classval0 count2 orcl);
retain value NOBsUsed NOBsRead effect2 variable classval0 orcl pval count count2 Run;
set comb3;
length pval 8.;
format pval PVALUE6.4;
if probtb ne . then pval = probtb;
else pval = probfb;

LABEL orcl = "OR (95% CI)";
LABEL pval = "p";

else if classval0 ne "Female" and classval0 ne "At least 1 child <18" and classval0 ne "Never deployed"
then classval0 = cat('     ',classval0); /********HERE I ADDED IN SOME LEADING BLANKS*******/
if classval0 = "" then pval = .;
if classval0 ne "" then Run = .;

put classval0 $char80.; /****I FOUND THIS ON A MESSAGE BOARD AND TRIED IT BUT IT DIDN'T WORK*****/
RUN;

%mend;

[Reeza]

Please check your CSV file in a text editor, not Excel. Are the spaces in the CSV, ie is Excel stripping the blanks or is SAS?

[ginak]

Hi, 

It is excel. But like one of the other commentors mentioned.. for a .csv it'll happen. I just have to use tagsets

[Reeza]

This works for me, not ideal obviously. 

data class;
length name $30.;
set sashelp.class;

if name='Alfred' then name= '     Alfred';
if name='Judy' then name='Fred    ';
run;

data _null_;
set class;
file '/folders/myfolders/output.csv' dlm=',' lrecl=1000;
put name $char50. "," sex $char10. "," age "," weight "," height;

run;

[ginak]

This worked for me, too! the only problem is that I have many, many variables T_T but thank you for showing me that it can be done! I think my problem then, is that I used the cat statement to add in the blanks instead of manually typing them? Not sure why mine didn't work since it seems like we did the same thing. Oh well. There are better ways to do this anyway, like you said. I have tried ods tagsets and that seemed to help

[RW9]

That is not a CSV file you are creating.  You are creating a report.  Do you want the output to be Excel?  If so then use ods tagsets.excelxp (for older versions of SAS) or ods excel (for 9.4).  With these you can create a proc report statement, put all the column widths and such like, and key being there is an option to keep padding:

ods excel file="abc.xlsx";
 
proc report data=your_data nowd;
  columns your_var;
  define  your_var / "Label" style={asis=on};
run;

ods excel close;

[ginak]

Hi, yes you are correct. Tagsets seem to do the trick for me. I just need to research on how to use proc report 🙂 Thank you!

[Ksharp]

classval0 = cat('     ',classval0);

-->

classval0 = cat('09'x ,classval0);

[RW9]

Isn't that a tab though @Ksharp?  Spaces are not same as tab as tabs rendered differently in different apps.

[Ksharp]

Yeah. That is a TAB character.

I think OP want a 'dummy' blank, do not care whether it is a Tab character  or real blank .

or could try PRETEXT='     '  style .

[ginak]

Thank you for your help! Yes, I think the style option + tagsets did the trick for me. And I just don't export as a .csv (type of excel file is not as important since I just need it to be in Excel).

[Tom · Accepted solution]

There is no where in a CSV file that you can store that level of detailed formatting.

Why not use ODS EXCEL to write your report directly to an EXCEL file. Then you can use the STYLE options to attach to appropriate Excel formatting to the cells.

If you do want to preserve leading spaces in a delimited file written by the SAS data step then you cannot use the DSD option on the FILE statement.  You could use the DLM= option, but to preserve the leading spaces you will need to use either $CHAR or $VARYING format on the PUT statement. And in either case you would then need to manually add the delimiter after the formatted variable.

data _null_;
  a=1;x='  leading'; b=2;
  file log dlm=',';
  len=length(x);
  put a x $varying20. len ',' b;
run;

1,  leading,2