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;
Hi @ACoyle: Add the PAD option to the FILE statement.
file "&v_Path&v_Fname" lrecl=75 pad;
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.
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.
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;
Hi @ACoyle: Add the PAD option to the FILE statement.
file "&v_Path&v_Fname" lrecl=75 pad;
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.
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.
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).
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: