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

Hello Friends,

I have character date in YYMM format.(Column Name EXPDATE)

e.g. '2004' which means April 2020

      '2104' which means April 2021

      '2204' which means April 2022

      '2304' which means April 2023

How to convert it into date format ?

Being a beginner in SAS I googled the same and use below mentioned logic

format exp date9.;

Exp   = intnx('month', input(put(EXPDATE,4.), yymmn4.), 1)-1;

;

Which gives me output 30Apr1920 instead of 2020  and similarly for all remaining values where YY value is above 20.

Please help me to resolve the same.

Thanks,

Prasad Devasthali

Business Analyst,HDFC Bank

1 ACCEPTED SOLUTION

Accepted Solutions
8 REPLIES 8
andreas_lds
Jade | Level 19

Can't test ist right now, but maybe setting YEARCUTOFF solves the issue.

See YEARCUTOFF= System Option :: SAS(R) 9.4 System Options: Reference, Fourth Edition

ImPrasad
Calcite | Level 5

Thanks Novice Master

data_null__
Jade | Level 19

Your problem is YEARCUTOFF I expect it is the same as mine which defaults to 1920 set it to something greater than your largest 2 digit year;  Also you don't need PUT(EXPDATE,4.) as EXPDATE is already character, or so you say.  If you want the last day of the month EXP you can advance by 0 and use E for the alignment option.  This it looks like you know how INTNX works.

options yearcutoff=1930;
data exp;
   expdate = '2004';
  
x = put(EXPDATE,4.);
   dt = input(put(EXPDATE,4.), yymmn4.);
   put x=  dt=date9.;
   Exp   = intnx(
'month',input(EXPDATE,yymmn4.),1)-1;
   ExpLastday = intnx(
'month',input(EXPDATE,yymmn4.),0,'e');
   put (exp expl:) (=date9.);
   run;

28            expdate = '2004';
29            x = put(EXPDATE,4.);
30            dt = input(put(EXPDATE,4.), yymmn4.);
31            put x=  dt=date9.;
32            Exp   = intnx('month',input(EXPDATE,yymmn4.),1)-1;
33            ExpLastday = intnx('month',input(EXPDATE,yymmn4.),0,'e');
34            put (exp expl:) (=date9.);
35            run;

x=2004 dt=01APR2020
Exp=30APR2020 ExpLastday=30APR2020
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

data tmp;

  infile datalines;

  input text_date $;

  num_date=input("20"||text_date,yymmn6.);

  format num_date date9.;

datalines;

2004

2104

2204

2304

;

run;

Tom
Super User Tom
Super User

Note sure why you are getting the wrong century, but it is not hard to add the century back in.  Is the existing variable character or numeric?  You text says it is character but your example code is treating it as numeric.  If it is numeric then convert it to text using the Z4. format to preserve any leading zeros.

data tmp;

  infile datalines;

  input text_date $4.;

  num_date1=input(text_date,yymmn4.);

  num_date2=input(cats('20',text_date),yymmn6.);

  num_date3=input(cats(text_date,'01'),yymmdd6.);

  num_date4=input(cats('20',text_date,'01'),yymmdd8.);

  format num_date: yymmdd10.;

  put (_all_) (=);

datalines;

2004

2104

2204

2304

;

run;


text_date=2004 num_date1=2020-04-01 num_date2=2020-04-01 num_date3=2020-04-01 num_date4=2020-04-01

text_date=2104 num_date1=2021-04-01 num_date2=2021-04-01 num_date3=2021-04-01 num_date4=2021-04-01

text_date=2204 num_date1=2022-04-01 num_date2=2022-04-01 num_date3=2022-04-01 num_date4=2022-04-01

text_date=2304 num_date1=2023-04-01 num_date2=2023-04-01 num_date3=2023-04-01 num_date4=2023-04-01


ballardw
Super User

Personal rant: Anyone still writing 2 digit years deserves many lashes with a wet spaghetti noodle or similar chastisement.

Of course I may be too sensitive after Y2K reprogramming and storing data from the early 60's.

data_null__
Jade | Level 19

I reckon you're preachin' to the choir.  I doubt that @ImPrasad is responsible for the date entry. 

ImPrasad
Calcite | Level 5

Very true  Master

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 8 replies
  • 2633 views
  • 2 likes
  • 6 in conversation