topic Re: Writing SAS data to a text file in SAS Enterprise Guide

Writing SAS data to a text file

npa — Mon, 26 Nov 2007 15:14:38 GMT

This is a basic question but I can't seem to find the answer to it.

I need to write out a date (that is, unfortunately, numeric rather than a SAS date) to an ASCII file. I would like to preserve any leading 0s. Currently I have a data step that converts the numeric date to character to accomplish this.

Is there a more efficient way to do this where I can skip the data step?

Re: Writing SAS data to a text file

Cynthia_sas — Mon, 26 Nov 2007 20:03:33 GMT

Hi:

When you use any of the SAS formats to write your date variables, SAS does put leading 0s on dates like 01/01/1960. However, even if you get the leading 0s in your ASCII text file, the application (like Excel) that is going to open the ASCII file may turn around and strip out the leading 0s -- so you have to check whether your leading 0s will be respected by the application that's going to open your file.

So, what if this is what your data (WORK.DATETEST) looks like this:
[pre]
Obs name date1 date2 date3 date4

1 alan -3334 -3334 -3334 -3334
2 bob 16927 16927 16927 16927
3 carl 0 0 0 0

[/pre]
(Alan's date is 11/15/1950; Bob's date is 05/06/2006; and Carl's date is 01/01/1960) What you see in the above file is the internally stored SAS date value as the number of days since Jan 1, 1960. If I run this code:
[pre]

ods csv(1) file='c:\temp\OpenWithExcel.csv';
ods csv(2) file='c:\temp\OpenWithNotepad.txt';
proc print data=work.datetest split='*';
var date1 date2 date3 date4;
format date2 mmddyy10.
date3 date9.
date4 date7.;
label date1='SAS Internal* Date Value'
date2='mmddyy10.* Standard Format'
date3='date9.* Standard Format'
date4='date7.* Standard Format';
run;
ods _all_ close;

** Even if you create an ASCII file with PUT statement;
** some software programs may still suppress the leading zeros;
data _null_;
set work.datetest;
file 'c:\temp\date_ascii.txt';
put @1 name @7 date1 date9. @19 date2 mmddyy10. ;
run;
[/pre]

When I open either of the .TXT files with Notepad, I see that SAS did put leading 0s in the ASCII CSV file.

[pre]
"Obs","SAS Internal Date Value","mmddyy10. Standard Format","date9. Standard Format","date7. Standard Format"
"1",-3334,"11/15/1950","15NOV1950","15NOV50"
"2",16927,"05/06/2006","06MAY2006","06MAY06"
"3",0,"01/01/1960","01JAN1960","01JAN60"
[/pre]
AND in the DATE_ASCII.TXT file
[pre]
alan 15NOV1950 11/15/1950
bob 06MAY2006 05/06/2006
carl 01JAN1960 01/01/1960
[/pre]

But, if you open either of the files with Excel, you will see that Excel does not respect the SAS formats and strips out the leading 0s.

So, you're right -- it is a basic question -- and the answer is to use either a SAS format or a user-defined format to preserve the leading 0s in your ASCII text file. Are you using SAS formats to write your data out to the ASCII text file? If what is happening AFTER the ASCII text file is opened (possibly with Excel) is what you're trying to work-around, then, that's a different question.

Can you elaborate on how you're using the date and why you think that leading 0s are NOT being preserved?

cynthia

Re: Writing SAS data to a text file

Olivier — Tue, 27 Nov 2007 13:23:50 GMT

Hi Npa.
I understand your date problem slightly differently from Cynthia. To me it seems that you have a variable which values are something like 990512 or 71111 to store dates like may 12th, 1999 or november 11th, 2007. And you'd like to store them as 990512 and 071111 in your text file.
The trick is exactly what Cynthia offered, using ODS CSV and specifying formats in a Print Procedure. In this case, the format would be something like Z6. to display leading zeroes if any.
If you want to use Proc Export, you cannot escape a preliminary Proc Sort ou Data step to associate Z6. format to your variables permanently ; if you specify a format in a proc Export, it is simply ignored.

Regards,
Olivier.

Re: Writing SAS data to a text file

npa — Tue, 27 Nov 2007 15:48:41 GMT

What Cynthia has said is correct. The leading zeroes are preserved. I do have a followup question. Does SAS have a date format that would write out the observations as follows (numbers only):

Obs Name Date
1 John 07102007 (July 10, 2007)

Message was edited by: npa Message was edited by: npa

Re: Writing SAS data to a text file

Cynthia_sas — Tue, 27 Nov 2007 15:57:07 GMT

Hi:
Yes, you can specify a separator character for a variant of the MMDDYY format. If you check out the documentation, these are the separators:

B separates with a blank
C separates with a colon
D separates with a dash
N indicates no separator
P separates with a period
S separates with a slash.

and here's a program that shows using the N separator (in the PROC PRINT):
[pre]
data bday;
infile datalines;
input name $ bday : mmddyy10.;
return;
datalines;
alan 11/15/1950
bob 06/30/1949
;
run;

proc print data=bday;
format bday mmddyyn8.;
run;
[/pre]

cynthia

Re: Writing SAS data to a text file

npa — Tue, 27 Nov 2007 16:16:38 GMT

Thanks, Cynthia!!