topic Re: Convert Date Format in New SAS User

Convert Date Format

Toni2 — Fri, 25 Jun 2021 10:49:45 GMT

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

Re: Convert Date Format

Kurt_Bremser — Fri, 25 Jun 2021 11:13:14 GMT

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.

Re: Convert Date Format

Tom — Fri, 25 Jun 2021 11:14:25 GMT

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.

Re: Convert Date Format

Kurt_Bremser — Fri, 25 Jun 2021 11:16:49 GMT

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.

Re: Convert Date Format

Toni2 — Fri, 25 Jun 2021 11:36:29 GMT

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

Re: Convert Date Format

PaigeMiller — Fri, 25 Jun 2021 11:41:49 GMT

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.

Re: Convert Date Format

Toni2 — Fri, 25 Jun 2021 12:17:24 GMT

yes, it works! thank you very much.

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

Re: Convert Date Format

Toni2 — Fri, 25 Jun 2021 12:18:13 GMT

thanks for your response. It has been clarified 🙂

Re: Convert Date Format

Toni2 — Fri, 25 Jun 2021 12:19:54 GMT

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