topic SAS Dropping Trailing Spaces in SAS Programming

SAS Dropping Trailing Spaces

ACoyle — Thu, 30 Apr 2020 18:05:05 GMT

Hello,

I am using SAS to output a file where the first line is a header that requires a set of 32 trailing spaces (weird client requirement).

Unfortunately everything I try has SAS dropping the extra spaces when I am writing to the Excel file. I know the method I'm doing to create the header line is not the most elegant, but if I could get the trailing spaces to remain I'd be grateful.

For the purposes of the below example, the v_RCount is 144.

/*Turning previous dataset into single column concat*/

Proc SQL noprint;
Create Table Output as
Select
Record_ID || Medicaid_ID || Fill_date || NDC || Rx_No || Billing_Provider_ID || Phys_Provider_ID || CLAIM_INDICATOR as Results
from SQL_Table;
Quit;

/*Getting count of obs for header and creating header*/

Data Header;
call symputx('v_RCount',v_RCount);
format head $char75.;
set output end=eof nobs=v_RCount;
output;
if eof then
do;
head=cat("HNCPDP340BMB000734ORDHS",put(date(),yymmddn8.),put(v_RCount,z12.)," ");
output;
end;
drop results;
rename head=Results;
run;

/*Exporting file as excel file*/

data _null_;
set Header
Output;
if Results = "" then delete;
file "&v_Path&v_Fname";
put Results;
Run;

Re: SAS Dropping Trailing Spaces

Reeza — Thu, 30 Apr 2020 19:35:30 GMT

Have you checked the file in the editor, not in Excel?

How does excel enter this process when you're creating a file with spaces that's usually a text file not an excel file.

Sounds like they want a fixed width file exported.

Re: SAS Dropping Trailing Spaces

ACoyle — Thu, 30 Apr 2020 19:45:58 GMT

Sorry, yes this is a fixed width text file being exported (not sure what I was thinking when I wrote excel). And to your question, yes I have checked it in a different editor (slickedit) and the spaces are not present.

Re: SAS Dropping Trailing Spaces

Reeza — Thu, 30 Apr 2020 19:48:02 GMT

Make sure to specify LRECL on the FILE statement to specify the length of the full record.

Re: SAS Dropping Trailing Spaces

ACoyle — Thu, 30 Apr 2020 20:25:10 GMT

I've added the lrecl section as suggested, but am still missing the trailing spaces in the header.

data _null_;
set Header
Output;
if Results = "" then delete;
file "&v_Path&v_Fname" lrecl=75;
put Results;
Run;

Re: SAS Dropping Trailing Spaces

FreelanceReinh — Thu, 30 Apr 2020 20:38:45 GMT

Hi @ACoyle: Add the PAD option to the FILE statement.

file "&v_Path&v_Fname" lrecl=75 pad;

Re: SAS Dropping Trailing Spaces

ACoyle — Thu, 30 Apr 2020 20:41:21 GMT

Thank you so much for the fast reply and help with this! As usual, there are so many options available in SAS that I missed the very one I needed.

Re: SAS Dropping Trailing Spaces

Kurt_Bremser — Fri, 01 May 2020 07:37:55 GMT

You're overcomplicating things by a mile here. This can be solved very simple:

data _null_;
set sashelp.class nobs=nobs;
file '/folders/myfolders/test.txt' dlm='|' dsd;
if _n_ = 1
then do;
  length header $75;
  header = cat("HNCPDP340BMB000734ORDHS",put(date(),yymmddn8.),put(nobs,z12.));
  put header $char75.;
end;
put name sex age weight height;
run;

The log already tells us that the line was correctly padded:

73         data _null_;
 74         set sashelp.class nobs=nobs;
 75         file '/folders/myfolders/test.txt' dlm='|' dsd;
 76         if _n_ = 1
 77         then do;
 78           length header $75;
 79           header = cat("HNCPDP340BMB000734ORDHS",put(date(),yymmddn8.),put(nobs,z12.));
 80           put header $char75.;
 81         end;
 82         put name sex age weight height;
 83         run;
 
 NOTE: The file '/folders/myfolders/test.txt' is:
       Dateiname=/folders/myfolders/test.txt,
       Besitzername=root,Gruppenname=vboxsf,
       Zugriffsberechtigung=-rwxrwx---,
       Zuletzt geändert=01. Mai 2020 09.34 Uhr
 
 NOTE: 20 records were written to the file '/folders/myfolders/test.txt'.
       The minimum record length was 17.
       The maximum record length was 75.
 NOTE: There were 19 observations read from the data set SASHELP.CLASS.
 NOTE:  Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
       real time           0.02 seconds
       cpu time            0.01 seconds

and this is the resulting file:

HNCPDP340BMB000734ORDHS20200501000000000019                                
Alfred|M|14|112.5|69
Alice|F|13|84|56.5
Barbara|F|13|98|65.3
Carol|F|14|102.5|62.8
Henry|M|14|102.5|63.5
James|M|12|83|57.3
Jane|F|12|84.5|59.8
Janet|F|15|112.5|62.5
Jeffrey|M|13|84|62.5
John|M|12|99.5|59
Joyce|F|11|50.5|51.3
Judy|F|14|90|64.3
Louise|F|12|77|56.3
Mary|F|15|112|66.5
Philip|M|16|150|72
Robert|M|12|128|64.8
Ronald|M|15|133|67
Thomas|M|11|85|57.5
William|M|15|112|66.5

when you move the cursor over the text, you'll see the blanks.

Re: SAS Dropping Trailing Spaces

FreelanceReinh — Fri, 01 May 2020 09:10:24 GMT

This is interesting! Thanks, @Kurt_Bremser, for pointing this out.

Literally for decades, we've been taught: "The $CHARw. format is identical to the $w. format." (SAS® 9.3 Formats and Informats: Reference).

Now, all of a sudden, it says: "The $CHARw. format is not identical to the $w. format. The $CHARw. format does not trim trailing blanks. The $w. format trims trailing blanks." (SAS® 9.4 Formats and Informats: Reference).

And what does "What's New in SAS 9.4 Formats and Informats" say about this unexpected change: nothing!

Moreover, both the DS2 and FedSQL flavors of the $CHARw. format are still documented as being "identical" to the corresponding $w. formats (in SAS® 9.4 DS2 Language Reference and SAS® 9.4 FedSQL Language Reference).