delimeted file

[Hhh111]

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

[Patrick]

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]

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]

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]

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]

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]

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]

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]

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]

---

@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]

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]

@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]

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.