topic Re: Converting to date format in SAS Programming

Converting to date format

abd_khan11 — Tue, 25 Oct 2022 17:58:42 GMT

Hello! I'm new to SAS and was performing some data cleansing. I'm aware of the existence of a variety of date formats and was curious as to whether there was a relevant one for the one I'm trying to convert. The dates look like this: 01M_2017Q1 (01=first month of quarter, Q1=first quarter). I'd like to convert this to a date that provides the month and year of the given date. Any and all help is appreciated.

Re: Converting to date format

PeterClemmensen — Tue, 25 Oct 2022 18:02:06 GMT

I doubt that an out-of-the-box informat exists.

Question: Would 01M_2017Q2 represent the first month of the second quarter of 2017? Meaning 01Apr2017

Re: Converting to date format

PaigeMiller — Tue, 25 Oct 2022 18:41:03 GMT

I'm not sure I'd bother with a informat. Parse the string, turn the results into a SAS date value.

data want;
    date_string='01M_2017Q1';
    month=substr(date_string,1,2);
    year=substr(date_string,5,4);
    quarter=substr(date_string,10,1);
    sas_date=mdy((input(quarter,1.)-1)*3+input(month,2.),1,input(year,4.));
    format sas_date date9.; /* Comment out this line to leave the date value unformatted */
run;

To perhaps clear up the terminology, you need an informat (not a format) to read a text string into a date. But such an informat does not exist. You (and the code above) are not converting the character string to a "date format", it is converting a character string to a valid numeric SAS date value. Once you have a valid numeric SAS date value, you can then apply any valid SAS date format to it, or leave it unformatted.

Re: Converting to date format

Tom — Tue, 25 Oct 2022 18:36:56 GMT

I think you are looking for an INFORMAT, not a FORMAT. Formats convert values to text. Informats convert text to values.

If you want to convert those strings into dates you can probably just parse the values and convert them into a date value. This code will assume you want the first day of the month.

You can then attach any of the many date formats to the variable to display it in a way that humans will recognize. YYMM7 is one that displays only the year and month part.

data have ;
  input string $10. ;
cards;
01M_2017Q1
02M_2017Q1
03M_2017Q1
01M_2017Q2
;

data want;
  set have;
  date = intnx('month',input(scan(string,2,'_'),yyq6.),input(string,2.)-1);
  format date yymm7.;
run;

Result:

Obs      string         date

 1     01M_2017Q1    2017M01
 2     02M_2017Q1    2017M02
 3     03M_2017Q1    2017M03
 4     01M_2017Q2    2017M04

Re: Converting to date format

abd_khan11 — Tue, 25 Oct 2022 18:58:21 GMT

Hi! I didn't know those discrepancies existed, thanks so much for the info and your help. Will be really useful moving forward with my work.

Re: Converting to date format

abd_khan11 — Tue, 25 Oct 2022 19:02:26 GMT

Got it. Thanks for your help! Helped clear up some concepts I was struggling with.