Convert Date Format

[Toni2]

hi, my i have the variable date which has the below format :

date

2000 Q1
2000 Q2
2000 Q3
2000 Q4
2001 Q1
2001 Q2
2001 Q3
2001 Q4

and i need to convert it to 01/01/2000 format. I use the below 

Data b;
Set a;
date = compress(date);
date1= input(date,anydtdte32.);
format date1 ddmmyy10.;
drop date;
rename date1=date;
run;

However, i get the below results which is not correct as the months must be 3,6,9,12 for the different quarters and not 1,4,7,10. 

Any help it would be appreciated 

01/01/2000
01/04/2000
01/07/2000
01/10/2000
01/01/2001
01/04/2001
01/07/2001
01/10/2001

[Kurt_Bremser]

Use the correct informat:

data have;
input date $7.;
datalines;
2000 Q1
2000 Q2
2000 Q3
2000 Q4
2001 Q1
2001 Q2
2001 Q3
2001 Q4
;

data want;
set have;
quarter = input(compress(date),yyq6.);
format quarter ddmmyy10.;
run;

The ANY.... informats are unreliable by their very nature.

[Toni2]

hi, thank you again for your support. I think i am doing something wrong (apologies i am new here) but when i run the code i get the below which is still not what i was expected. The quarter variable has months 1,4,7,10 months and not 3,6,9,12

Obs	date	quarter
1	2000 Q1	01/01/2000
2	2000 Q2	01/04/2000
3	2000 Q3	01/07/2000
4	2000 Q4	01/10/2000
5	2001 Q1	01/01/2001
6	2001 Q2	01/04/2001
7	2001 Q3	01/07/2001
8	2001 Q4	01/10/2001

[PaigeMiller]

Which code are you running? @Kurt_Bremser has posted two different sets of code, his second code ought to work. Show us the exact code your are running.

[Toni2]

thanks for your response. It has been clarified 🙂

[Tom]

I don't understand what you want.  You said you wanted January 1st.  Now you are saying you want March instead of January.  Do you want the last day in March? That is the last of the quarter?

date1= intnx('qtr',input(compress(date),yyq6.),0,'e');
format date1 ddmmyy10.;

PS Why use DMY order to display dates?  It will confuse the half of your audience that expects dates in MDY order.  Use either DATE9 or YYMMDD10 and avoid the risk of confusion.

[Toni2]

thank you for your response and i am sorry for the confusion i am new here. They have now given the answer 🙂

[Kurt_Bremser · Accepted solution]

And since you seem to want the last day of a quarter, add the INTNX function:

data want;
set have;
quarter = intnx('quarter',input(compress(date),yyq6.),0,'e');
format quarter ddmmyy10.;
run;

Mind that this may not be favorable for later work, as SAS always uses the first day as the date for a given period.

[Toni2]

yes, it works! thank you very much. 

P.s the date format is nightmare....