topic Re: Convert text of long date to short date in SAS Programming

Convert text of long date to short date

Jumboshrimps — Mon, 14 Nov 2022 23:24:41 GMT

Importing some Excel data with a column of dates in Saturday, October 31, 2018 format. Need to convert them to Oct312018.

data import;

format Appt_date $30.; /*CLEARLY DEFINING appt_date AS A CHARACTER.

set xport;

run;

data full_date;
FORMAT Appt_dt MMDDYYS10.; /* CREATING AN ADDITIONAL VARIABLE IN MMDDYYS10. FORMAT.
Appt_Dt=INPUT(Appt_date, ANYDTDTE10.);
set import;
run;

LOG:

data full_date;
26 FORMAT Appt_dt MMDDYYS10.;
27 Appt_Dt=INPUT(Appt_date, ANYDTDTE10.);
28 set import; ERROR: Variable Appt_date has been defined as both character and numeric.

Appt_Date has clearly NOT been defined as both character and numeric. I don't know what gives, but I have to convert long text dates to a more standard SAS format.

Re: Convert text of long date to short date

SASKiwi — Tue, 15 Nov 2022 03:20:26 GMT

What does the log of the IMPORT DATA step look like?

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
      30:13

If it contains anything like the above your excel data never started out as character anyway and applying a FORMAT statement won't convert it.

Run this to confirm what the variable type for Appt_date is to start with:

proc contents data = import;
run;

Re: Convert text of long date to short date

Tom — Tue, 15 Nov 2022 04:23:40 GMT

Your statements are out of order.

So you said. Convert APPT_DATE to APPT_DT. Now read the value of APPT_DATE from IMPORT.

Since you never gave SAS any hint (or at least any hint that it used) how to define APPT_DATE it defined it as NUMERIC. Then when it looked it the IMPORT dataset you referenced in the SET statement it found out you had a conflict of definition.

Let's look at the data step code line by line.

data full_date;

So first you tell SAS to start an new data step that will create the dataset FULL_DATE.

FORMAT Appt_dt MMDDYYS10.;

Then you tell SAS that the APPT_DT variable should be displayed using the MMDDYYS10. format. Since you have not previously referenced this variable SAS guesses that you wanted APPT_DT to be a NUMERIC variable since you are asking it to be displayed using a numeric format. Note that the unclosed block comments you added in the code you posted (but fortunately did not include in the code you ran) are going to confuse the people reading the code. A FORMAT statement does not DEFINE a variable any more than any other usage, such as an assignment statement or an array statement, defines a variable. If you want to really be sure how the variable is defined, what its TYPE is and what its storage length is, then use a LENGTH statement (or the ATTRIB statement with the LENGTH= option). The FORMAT statement is for defining how you want the variable DISPLAYED.

Appt_Dt=INPUT(Appt_date, ANYDTDTE10.);

Now calculate the value of APPT_DT from the value of APPT_DATE. SInce APPT_DATE has not been defined yet SAS will guess that it should be numeric. (SAS makes stupid guesses sometimes. That is why it is safer to actually define the variables yourself).

Also notice that (assuming you had defined APPT_DATE with the right type before this statement) that placing the statement here will mean that APPT_DT is calculated from the PREVIOUS value of APPT_DATE, since the current value has not been read in by the SET statement yet.

So if you wanted to generate APPT_DT(length=8) from APPT_DATE (length=$30) then use code like this:

data full_date;
  set import;
  length appt_dt 8;
  Appt_Dt=INPUT(Appt_date, ANYDTDTE60.);
* Display APPT_DT in mm/dd/yyyy style ;
  FORMAT Appt_dt MMDDYYS10.;
run;

The INPUT() function does not care if the width used in the informat is larger than the length of the string being read. SInce the maximum width that the ANYDTDTE informat can use is 60 just go ahead and use that. Just incase the next time you IMPORT that dataset SAS guesses that the length should be more than $30.

Comments should appear BEFORE the code they are explaining. It makes it much clearer for the reader.

Re: Convert text of long date to short date

Jumboshrimps — Wed, 16 Nov 2022 20:08:28 GMT

Thanks for your reply.

Working on this the last two days with no success.

Made some dummy long dates (well three).

Data set before code:

After code as below:

data full_date;
set import;
length appt_dt 8;
Appt_Dt=INPUT(Appt_date, ANYDTDTE60.);
* Display APPT_DT in mm/dd/yyyy style ;
FORMAT Appt_dt MMDDYYS10.;
run;

Resulting data set:

LOG:

24 data full_date;
25 set import;
26 length appt_dt 8;
27 Appt_Dt=INPUT(Appt_date, ANYDTDTE60.);
28 * Display APPT_DT in mm/dd/yyyy style ;
29 FORMAT Appt_dt MMDDYYS10.;
30 run;

NOTE: There were 3 observations read from the data set WORK.IMPORT.
NOTE: The data set WORK.FULL_DATE has 3 observations and 3 variables.
NOTE: Compressing data set WORK.FULL_DATE decreased size by 0.00 percent.
Compressed is 1 pages; un-compressed would require 1 pages.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 568.40k
OS Memory 22948.00k
Timestamp 11/16/2022 10:01:07 AM
Step Count 113 Switch Count 2
Page Faults 0
Page Reclaims 51
Page Swaps 0

I have seen literally page after page on how to convert strings of mmddyy or mmm-dd-yy or mm/dd/yy to a numeric date, but no one wants to tackle strings of dates in Week day format.

Re: Convert text of long date to short date

PaigeMiller — Wed, 16 Nov 2022 18:42:28 GMT

Strip out the day of the week (like Wednesday), and the rest can be read with the informat anydtdte50.

data test;                            
    dateinfo='Wednesday, October 13, 2017';   
    where_comma=find(dateinfo,','); 
    sasdate=input(substr(dateinfo,where_comma+2),anydtdte50.);      
    put sasdate ;                            
    put sasdate date9.;                      
run;

Re: Convert text of long date to short date

Reeza — Wed, 16 Nov 2022 18:54:59 GMT

Your fake dates are invalid.
October 13, 2017 is a Friday so the Wednesday isn't correct so SAS ~~doesn't~~ shouldn't convert it.

If you get rid of the day of the week component, then SAS will convert it. If you need to verify that portion as well, you can write some custom code to do that.

data have;
appt_date = 'Wednesday, November 16, 2022'; output;
appt_date = 'Tuesday, November 15, 2022'; output;
appt_date = 'Monday, November 6, 2022'; output;
run;

data want;
set have;
Appt_Dt=trim(catx(", ", scan(appt_date, 2, ","), scan(appt_date, 3, ",") ));
appt_dt2= input(Appt_Dt, anydtdte60.);
FORMAT Appt_dt2 date9.;
run;

Re: Convert text of long date to short date

Tom — Wed, 16 Nov 2022 19:04:26 GMT

Please post data as text so we don't have to make it up. Your picture has invalid dates (those dates are all on Friday). But that is not the problem. The ANYDTDTE informat does not know what to do with strings in that format.

You can just remove the day of the week.

data have;
  input date date9. ;
  string1=catx(', ',put(date,downame.),put(date,worddate.));
  string2=substr(string1,index(string1,',')+1);
  date1=input(string1,anydtdte60.);
  date2=input(string2,anydtdte60.);
  format date: date9.;
cards;
13OCT2017
20OCT2017
06OCT2017
;
proc print;
run;

Obs         date            string1                 string2             date1        date2

 1     13OCT2017    Friday, October 13, 2017    October 13, 2017            .    13OCT2017
 2     20OCT2017    Friday, October 20, 2017    October 20, 2017            .    20OCT2017
 3     06OCT2017    Friday, October 6, 2017     October 6, 2017             .    06OCT2017

Re: Convert text of long date to short date

Jumboshrimps — Wed, 16 Nov 2022 20:32:54 GMT

Much better!!!!

I had to use real dates for SAS to convert them?

Who knew?

Thanx.