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
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.
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
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;
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.
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
@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
It's all in there.
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.
So you already have code that throws an ERROR? Why didn't you post the log?
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.
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.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.