BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ahzu
Calcite | Level 5

I am having trouble trying to format dates with single-digit days (no leading zero). The date in the dataset, in particular, corresponds to February 1st, 2000.

 

 **EDIT** rookie mistake. I forgot to check my log and thought anydtdtew gave an actual output.

data want;
	format date mmddyy10.;
	informat date anydtdte.;
	INPUT date;
datalines;
0212000
;

 

Running this code gets me an incorrect date:

06/08/2540

 

The following output I want is in the format mmddyy10. :

02/01/2000

 

So I believe the problem lies in the informat anydtdte., however, I am not sure which informat to use. I've tried other types of informats (mmddyy7.) but I can't seem to find the right one.

Thanks.

 

** Solved using CAT and SUBSTR functions to transform the date. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ShiroAmada
Lapis Lazuli | Level 10

I checked here http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a002605538.htm

 

Yes i think your informat is incorrect.

 

You might need to come up with data manipulation to address this one.  Say read the value as character, check the length, extract using substr day, month and year then use mdy function to put the details back together.  I think @Reeza mentioned something about which digit will be affected with no preceeding zero.

View solution in original post

9 REPLIES 9
Reeza
Super User

Can you always guarantee that the month will be 2 digit and the year will be a 4 digit?

 

EDIT: Please make sure your code is debugged for other errors when posting. Your INFORMAT is not correct and then SAS defaults to reading the date as a number and then using that number as a date. 

 

 

Ahzu
Calcite | Level 5

Yes, all the months are 2 digits, and the years are all 4 digits. 

Running with the informat anydtdte. gives me a missing value '.'

 

I forgot to check my log when running my program. Thanks for reminding me, I still have a lot to learn!

 

error_prone
Barite | Level 11
Removing the "w" from anydtdtew could help.
ShiroAmada
Lapis Lazuli | Level 10

I tried your code and it gave me an error.

 

ERROR: Informat ANYDTDTEW was not found.....

 

Removing the "w" in anydtdtew has no error but missing value.

 

Make it a habit to CHECK the log (LATEST).

Ahzu
Calcite | Level 5
Thanks for reminding me to check my log! I am still a beginner with SAS. But yes, I also get a missing value after removing the 'w'.
ShiroAmada
Lapis Lazuli | Level 10

I checked here http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a002605538.htm

 

Yes i think your informat is incorrect.

 

You might need to come up with data manipulation to address this one.  Say read the value as character, check the length, extract using substr day, month and year then use mdy function to put the details back together.  I think @Reeza mentioned something about which digit will be affected with no preceeding zero.

Ahzu
Calcite | Level 5

I ended up doing some data manipulation using CAT and SUBSTR functions and found a solution!

Basically I transformed 0212000 to 02/1/2000 and was able to call an informat which got it to my desired output! 

 

Thanks, I think a light bulb popped into my head while reading the comments.

ShiroAmada
Lapis Lazuli | Level 10

I am glad you were able to visualize what we all mentioned.

 

Not checking the log is not limited to "rookie mistake" (but yeah it's frequent among rookies).

 

Just remember when you check or review the log contents:

1. Clear previous log contents first.

2. Re-submit your code.

3. Check or review the log from top to bottom.

4. Always correct or resolve the first error (and not the latest or bottom error).  Remember sas reads sequentially unless you have goto in your script (which i seldom see now).

5. Do steps 1-3 again.

 

Lastly, an error-free log does not mean your output is correct.  A good example is that removing the "w" has no error but with missing value.

 

Tom
Super User Tom
Super User

The ANYDTDTE. informat cannot handle those 7 digit strings since there is no good way to tell where the missing digit should be added.

But you should be able to use the new REGEXE features of PROC FORMAT to define your own format.

Let's call this new informat MDYYYY to emphasize that it assumes there are always 4 digit years and that Month comes before Day.

I have added a rule to assume that if the string has only 6 digits then assume that it has both a one digit month and a one digit day.

invalue mdyyyy (default=8)
 's/([01][0-9])([0-9]{5}?)/${1}0$2/' (regexpe) = [mmddyy8.]
 's/(\d)([0-9]{5}?)/0${1}0$2/' (regexpe) = [mmddyy8.]
 '/[0-9]{8}?/' (regexp) = [mmddyy8.]
 other = _error_
;

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
  • 9 replies
  • 5620 views
  • 1 like
  • 5 in conversation