BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ginak
Quartz | Level 8

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;

 


have.JPGwant.JPG
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

View solution in original post

11 REPLIES 11
Reeza
Super User

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
Quartz | Level 8

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
Super User

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
Quartz | Level 8

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

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
Super User

classval0 = cat('     ',classval0);

-->

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Ksharp
Super User

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
Quartz | Level 8

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
Super User Tom
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 8660 views
  • 8 likes
  • 5 in conversation