topic Re: delimeted file in SAS Programming

delimeted file

Hhh111 — Thu, 23 Mar 2017 07:52:32 GMT

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

9/12/2008

12/1/2009

Re: delimeted file

Patrick — Thu, 23 Mar 2017 08:27:35 GMT

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.

Re: delimeted file

Hhh111 — Thu, 23 Mar 2017 08:38:25 GMT

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).

Re: delimeted file

Patrick — Thu, 23 Mar 2017 08:47:29 GMT

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.

Re: delimeted file

Kurt_Bremser — Thu, 23 Mar 2017 09:13:30 GMT

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-data-AKA-generate/ta-p/258712 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.

Re: delimeted file

Astounding — Thu, 23 Mar 2017 10:29:26 GMT

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;

Re: delimeted file

Hhh111 — Fri, 24 Mar 2017 02:30:57 GMT

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.

Re: delimeted file

Tom — Fri, 24 Mar 2017 15:00:11 GMT

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.

Re: delimeted file

Hhh111 — Mon, 27 Mar 2017 01:33:00 GMT

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?

Re: delimeted file

Kurt_Bremser — Mon, 27 Mar 2017 07:45:53 GMT

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

Re: delimeted file

Patrick — Mon, 27 Mar 2017 07:47:22 GMT

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

Re: delimeted file

Hhh111 — Mon, 27 Mar 2017 09:16:06 GMT

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.

Re: delimeted file

Hhh111 — Mon, 27 Mar 2017 09:23:55 GMT

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.