topic How to convert text to numeric date? in New SAS User

How to convert text to numeric date?

LydiaSmith — Mon, 22 Apr 2019 11:51:20 GMT

Dear all,

I used "proc sort data= alldata0001;
by Company_Name Annual_Report_Year ; run;"

to combine several sets together, however Annual_Report_Year in the original file is text not numeric date...

and all my sorting are messed up, as you can see from the attached photo, Apr 2016, Apr 2017 come before Mar 2000 and Mar 2002...

Anyone know how to use sas codes to convert text to numeric date in this case?

Thank you in advance!

Re: How to convert text to numeric date?

Kurt_Bremser — Mon, 22 Apr 2019 12:04:30 GMT

numdate = input('01' !! compress(chardate),date9.);
format numdate date9.;

compress() removes the blank, and '01' completes the string to a date.

Re: How to convert text to numeric date?

Shmuel — Mon, 22 Apr 2019 12:08:02 GMT

Check next code:

data test;
    text = "APR 2016";
    date = input(cat('01',strip(text)),daye9.);
run;

but for merging by date, are all your dates at 01 day of a month ?

May be you need create a separate variable key for merging, excluding the day:

sortby = put(date, yymmddn6.);

Re: How to convert text to numeric date?

LydiaSmith — Mon, 22 Apr 2019 15:40:46 GMT

Mr. Bremser,

Thank you so much for your reply! I really appreciate your help!!

However, it did not work out...the result is blank...

I don't know where I did wrong...

I attached my test file for your reference!

The date under Annual_Report_Year is literal text, not shortened variable from actual date...

Thank you again!

Re: How to convert text to numeric date?

LydiaSmith — Mon, 22 Apr 2019 15:48:53 GMT

Mr. Shmuel,

Thank you so much for your reply! Your help is appreciated!

I've tried with your code then changed "text" to "Annual_Report_Year,"

But the result displayed only APR 2016, still in text form...

The date (APR 2016) under Annual_Report_Year is literal text, not shortened variable from actual date, so no day of the month either...

My goal is to convert all the literal date to numerical date, APR 2016 to 201604, so I could sort my combined data sets in the right annual order.

So maybe I need to try another way to convert it?

I attached my test file for your reference, thank you again!

Re: How to convert text to numeric date?

ballardw — Mon, 22 Apr 2019 16:15:23 GMT

@LydiaSmith wrote:

Mr. Shmuel,

My goal is to convert all the literal date to numerical date, APR 2016 to 201604, so I could sort my combined data sets in the right annual order.

Poor choice. SAS has it's own numeric date values which are MUCH more flexible. You apply any one of a number of Formats (or create your own) to display or group dates as desired. There are a number of functions to manipulate the SAS date values. And the SAS date values will sort consistently .

I might try using the ANYDTDTE. format to read that date.

data test;
  x='Apr 2016';
  y=input(x,anydtdte.);
  put y= date9. y=mmddyy10. y=yymmn6.;
run;

The default will be for SAS to make the day of the month the first. Note that I demonstrate 3 of several possible formats to display the date value. With a SAS date value you change the appearance as needed just by using a different format, either in a report or analysis procedures. Most of the analysis and graphing procedures will honor groups created by use of a format. With formats that can group calendar quarters, such as YYQ, or simple years using the YEAR format you can gain a lot of flexibility that would otherwise require adding additional variables.

One example: how many days are between the 201604 (first of april) and 202008 (first of august)? How many weeks? You can't do simple arithmetic to get either answer but SAS functions working with date values can.

Re: How to convert text to numeric date?

Kurt_Bremser — Mon, 22 Apr 2019 17:27:44 GMT

Your dataset does not contain a variable chardate. I used this name only for a simple example, as you did not provide example data in usable form (data step with datalines).