topic Re: How to extract months from dates in SAS Programming

How to extract months from dates

GN0001 — Mon, 24 Jan 2022 05:25:27 GMT

data test;
input  memberID $ DateOfService :ddmmyy10.;
format DateOfService ddmmyy10.;
datalines;
123 01/02/2021
124 02/02/2022
124 01/02/2022
156 09/08/2018
144 10/12/2019
;
run;

data want;
format month $3.;
set test;
run;

data want2;
set want;
select (DateOfService);
when (Month(DateofService) = 01) Then Month "Jan";
when (Month(DateofService) = 02) Then Month "Feb";
Otherwise ' ';
end;
run;

Hello SAS Programmers;

I need to pull the month name from a column named DateOfService which has number data type with when. Any tips?

Second question is when I wanted to enter date in input statement, I needed to add this part: DateOfService :ddmmyy10. Without it, the result was not correct. Can anyone please tell me what :ddmmyy10. serve for DateOfService? Does it instruct SAS how to read the date? If yes, do we need to instruct SAS how to read dates when we have a date variable?

I appreciate your help.

Blue&Blue

Re: How to extract months from dates

japelin — Mon, 24 Jan 2022 06:25:45 GMT

First: How about this.

data want;
  set test;
  length Month $3;
  if DateOfService ne . then do;
    Month=substr(put(DateOfService,date9.),3,3);          /* result is like FEB */
    Month=propcase(substr(put(DateOfService,date9.),3,3));/* result is like Feb select as you like */
  end;
run;

If you really want to use SELECT WHEN, write the following

data want2;
  set want;
  select (DateOfService);
    when (Month(DateofService) = 01) Month="Jan";
    when (Month(DateofService) = 02) Month="Feb";
    Otherwise Month=' ';
  end;
run;

Second:

Specify this if the value specified in datalines is written in the format ddmmyy10..

It instructs SAS how to read the date

Yes.

do we need to instruct SAS how to read dates when we have a date variable?

Yes.

Otherwise, the SAS process may not complete successfully.

Re: How to extract months from dates

Astounding — Mon, 24 Jan 2022 06:27:56 GMT

Because your WHEN statements specify a complete comparison, you need to simplify the SELECT statement. Use:
SELECT();

For the second question, yes yes yes. However, it is not clear from your data whether ddmmyy10. is correct. Is the incoming data in that form, or is it in MMDDYY10. form? You need to know and must use the instruction that matches what actually appears in the data.

Re: How to extract months from dates

GN0001 — Mon, 24 Jan 2022 07:12:35 GMT

data want;
  set test;
  length Month $3;
  if DateOfService ne . then do;
    Month=substr(put(DateOfService,date9.),3,3);          /* result is like FEB */
    Month=propcase(substr(put(DateOfService,date9.),3,3));/* result is like Feb select as you like */
  end;
run;

Hello team member,

First solution: dates are like 03/12/2021, it is not text.
All months as character have 3 characters. How does SAS distinguish this?

I think your second solution is the answer. Let me test it and get back to you.

What would be the syntax if I want to write in process sql?

Refering to dates in datelines, thanks for clarification,

I am so thankful,

Blue&white

Re: How to extract months from dates

GN0001 — Mon, 24 Jan 2022 07:17:28 GMT

Hello team member,

Because your WHEN statements specify a complete comparison, you need to simplify the SELECT statement. Use:
SELECT();

Can you please kindly explain it more? If I select one variable, are the other variable included in the result?

Regards,

blue & white

Re: How to extract months from dates

japelin — Mon, 24 Jan 2022 07:25:25 GMT

Please try it first.

And check put function and DATE9. format.

Same in SQL.

proc sql;
  select memberID,substr(put(DateOfService,date9.),3,3) as month 
  from test;
quit;

Re: How to extract months from dates

Kurt_Bremser — Mon, 24 Jan 2022 07:44:01 GMT

@GN0001 wrote:

Hello team member,

Because your WHEN statements specify a complete comparison, you need to simplify the SELECT statement. Use:
SELECT();

Can you please kindly explain it more? If I select one variable, are the other variable included in the result?

Regards,

blue & white

SELECT Statement

It's all in there.

Re: How to extract months from dates

s_lassen — Mon, 24 Jan 2022 11:39:22 GMT

There has been a lot of other suggestions, but if you for some reason must use a SELECT statement, I think it should be done like this:

select (Month(DateOfService));
when (01) Month="Jan";
when (02) Month="Feb";
Otherwise;
end;

This calculates the MONTH function only once, not once for every line in the SELECT statement.

I made the OTHERWISE statement empty, as the MONTH variable will already be empty, if nothing has been assigned to it first. The OTHERWISE is still necessary, else you will get an error when there is a value not in the first two months.

Re: How to extract months from dates

Astounding — Mon, 24 Jan 2022 14:02:09 GMT

Sorry, my mistake. I thought you already knew at least the basics of the SELECT statement. You did use it in the program you posted. I won't make that mistake again.

I'm afraid that @Kurt_Bremser is correct. I can't exercise to make you stronger. I can't eat right to make you healthier. And I can't read the documentation to make you smarter. There are some things you just have to do for yourself. If you have specific questions, that's another story. But it's not my job to read the documentation, summarize it for you, and compose examples to ensure that you understand it.

Just a word to the wise I posted a nearly working solution. Evidently, you didn't test it because you didn't post any results. So I will give you the last piece of the solution. In addition to what I posted, the OTHERWISE statement needs to be simplified as well:

otherwise;

So that means your original program would have been correct, had you just removed some of the characters from it.

Re: How to extract months from dates

Tom — Mon, 24 Jan 2022 14:22:28 GMT

First solution: dates are like 03/12/2021, it is not text.

The date variable is numeric and display like the string you showed then it contains a date value (number of days since 1960) and you displayed it with the MMDDYY or DDMMYY format so that it generated that 10 character string.

The format used to display a number does not change the number. So you can still use the DATE format to convert the value in your date variable into a string that uses 3 characters for the month.

Re: How to extract months from dates

ballardw — Mon, 24 Jan 2022 16:24:26 GMT

MONNAME3. Format, and possibly not even need another variable.

21   data example;
22      x=today();
23      put x= monname3.;
24   run;

x=Jan

You will find a LARGE number of ways to manipulate date appearances with just the default formats supplied by SAS. Plus you can role your own with Proc Format that would be honored by report and analysis procedures.

For almost any date, time or datetime value you must specify to SAS with an informat the structure of the values. Consider the sequence:

01/02/03

Depending on locality and usage that could represent: 1 February 2003 (or 1903), 2 January 2003 (or 1903) , 3 Februray 2001 (or 1901) . So you have to tell SAS which order the day, month and year appear in your values.

SAS treats any variable that has not been provided information somewhere (Informat statement, Attrib statement, in line informat) as numeric by default. Which is going to have issues with the / characters.

Re: How to extract months from dates

GN0001 — Mon, 24 Jan 2022 17:09:27 GMT

Hello Astounding,
Sorry, my mistake. I thought you already knew at least the basics of the SELECT statement.
I wrote the code by myself and posted here. It was giving 8 errors and I fixed them all, except the last one that I couldn't, that is why, I posted here in this forum.
I know sql.
Respectfully,
BlueBlue

Re: How to extract months from dates

GN0001 — Mon, 24 Jan 2022 17:19:12 GMT

Hello,
I know sql and relational database management system. I did the code myself
and it gave me 8 errors and I fixed them one by one, still the last one
persisted.

Last night, it was too late and I couldn't apply your solution. Please bear
with me.

Blue&Blue

Re: How to extract months from dates

Kurt_Bremser — Tue, 25 Jan 2022 07:24:17 GMT

So you already have code that throws an ERROR? Why didn't you post the log?