BookmarkSubscribeRSS Feed
Hhh111
Calcite | Level 5

Why when I output my data into delimeted textfile, the date are not right/left justified. Some in left position, some in right position. How to make it in right justified for all.

 

 

12/4/2016
     31/03/2016
31/03/2016
       9/12/2008
12/1/2009
12 REPLIES 12
Patrick
Opal | Level 21

Provide sample code which illustrates the issue.

 

The table you've posted indicates that you're looking at your textfile via Excel. Don't do that! Excel might do "stuff" to your data. Use a text editor like Notepad++ to examine how your data got written out. 

Hhh111
Calcite | Level 5

Tq for the reply Mr Patrick.

 

The sample code as per below

DATA _NULL_;
SET A.TRY;
FILE "C:\Testing Data\ACCOUNT&REPTDT..txt" DLM='|';
IF _N_ EQ 1 THEN PUT @1 'DATE|ACCTNO|NAME|';
put DATE ACCTNO NAME;
run;

 

currently the date is in DDMMYY10. format.

 

And yes, to view the file, I open up with Excel. I never try use Notepad as I want it to be viewed in Excel (as per request from boss).

Patrick
Opal | Level 21

For us to replicate the issue you will need to provide some sample data for SET A.TRY;

 

Best provide a SAS data step which creates this data.

 

Even if your end goal is to open the data in Excel you still need to use a text editor and not Excel to look into the created text file. Excel won't tell you what's not working and doesn't show you the text file as it really looks like.

 

From what I can tell: Some of the date strings in your text file get interpreted as Dates by Excel and others get interpreted as text strings (that's most likely the explanation for the left/right alignments). WHY that's happening is something you need to investigate by looking at the actual text file without changing it - that's what a text editor does for you.

Hhh111
Calcite | Level 5

This is the sample code to generate the files

 

libname a 'C:\Testing Data\ROP';

data try;

FORMAT DATE DDMMYY10.;

input date DDMMYY10.;

cards;

13032011

22112016

01042015

04072012

run;

DATA _NULL_;

SET TRY;

FILE "C:\Testing Data\REPORT DATE..txt" DLM='|';

IF _N_ EQ 1 THEN PUT @1 'DATE|';

PUT DATE;

RUN;

 

when i open up with Excel, those data that contains 0 in the front, will be right justified and the other data that have two digit will be left justified. This might be the problem when open up the report using Excel, however, is there any solution to synchronize all data to be right justified even if I used Excel to read it? Thanks.

Kurt_Bremser
Super User

First of all, provide sample data. Use the macro from https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... to convert (part of) your dataset to a datastep that can be posted here ({i} or "little running man" icon).

I'm particularly intrigued by the fact that 31/03/2016 is treated by Excel once as a date and once as text, so I suspect there's something special in the data.

Astounding
PROC Star

One possibility that would produce such results:  DATE is actually a character variable that already contains leading blanks.  You can rule that out by adding this line before the PUT statement:

 

date = left(date);

 

However, if DATE is actually numeric, don't do this  It won't work, and SAS will complain about numeric to character conversion.

 

Also note, you may get a trailing blank after data values.  That's probably not a problem but if it happens you can address it with:

 

put date +(-1) acctno +(-1) name;

Tom
Super User Tom
Super User

There is nothing wrong with your SAS code or your text file. Excel is assuming that you specify dates in M/D/Y order instead of D/M/Y order. So when it sees a value larger than 12 in the first position it doesn't think it is a date so it stores the value as a character string instead of as a date.

 

You could avoid this by using the YYMMDD10. format in SAS when you generate the text file.  Excel cannot then confuse those dates. It will store them as dates and display them using your local convention for displaying dates.

Hhh111
Calcite | Level 5

Thanks for your reply Tom. You are correct because when the value is larger than 12, the position will be left justified. Any values lower or equal to 12 will be right justified. I already assign format DDMMYY10. to the date before output to textfile, but the problem still occurs. However, in dataset, the date field is already stored as date format, so Excel shouldnt confuse with the format.

 

Any other solution?

Kurt_Bremser
Super User

@Hhh111 wrote:

Thanks for your reply Tom. You are correct because when the value is larger than 12, the position will be left justified. Any values lower or equal to 12 will be right justified. I already assign format DDMMYY10. to the date before output to textfile, but the problem still occurs. However, in dataset, the date field is already stored as date format, so Excel shouldnt confuse with the format.

 

Any other solution?


Read @Tom's post again.

DDMMYY10. != YYMMDD10.

Hhh111
Calcite | Level 5

Oh yes....i put it wrongly. Instead of YYMMDD10. I put DDMMYY10. And yes, it looks fine. I just need to explain to user to avoid confusion. Thanks for the reminder. Thanks again @Tom.

 

Patrick
Opal | Level 21

@Hhh111

You could use a date format which Excel understand but which doesn't have the mmdd vs. ddmm issue. Using your code I've used format DATE9. and things worked just fine for me.

data try;
  FORMAT DATE date9.;
  input date DDMMYY10.;
  cards;
13032011
22112016
01042015
04072012
run;

DATA _NULL_;
  SET TRY;
  FILE "C:\temp\REPORT DATE.txt" DLM='|';
  IF _N_ EQ 1 THEN
    PUT @1 'DATE|';
  PUT DATE;
RUN;
Hhh111
Calcite | Level 5

TQ for your reply Patrick. Appreciate it. Yes, by using DATE9. format, it is readable using date format and not converted into funny thing. Maybe I need to advise my user that DDMMYY10. will not be a suitable solution if they converted the dlm file into excel manually. As for now, I will assume that is the best solution. Thanks again.

 

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
  • 12 replies
  • 1125 views
  • 3 likes
  • 5 in conversation