topic Re: Monname Results Do not match month names in proc SQL in SAS Enterprise Guide

Monname Results Do not match month names in proc SQL

sun538 — Fri, 06 Nov 2020 22:28:29 GMT

Hi, I try to get month name from a date column (yyy-mm-dd) with the following code:

DATA WANT;
SET X;
names=put(Date, monname.);
RUN;

and the new column (names) results are:

proc sql ;
select distinct names from WANT; quit;

However, when I run a query to get all records for each month name, it only works for only one month "September":

proc sql;
 select *
 from WANT 
 where names="January";
 quit;

Surprisingly, the query returns 0 records for all months except "September" (where names="September").

Could you please help find out why that happens and how to resolve it? Is it the issue with using "monname" format?

Thanks.

Re: Monname Results Do not match month names in proc SQL

Reeza — Fri, 06 Nov 2020 22:31:56 GMT

Show the log from a query that does work and one that doesn't.

Re: Monname Results Do not match month names in proc SQL

ballardw — Mon, 09 Nov 2020 15:27:18 GMT

Since you are starting with a numeric valued date when you use the PUT function the result is right justified and there are leading spaces in most of the month names. So when you test for equality you have to remove the leading blanks.

data want;
   do month=1 to 12;
      date=mdy(month,1,2020);
      names= put(date,monname.);
      output;
   end;
   format date date9.;
run;

proc sql;
 select *
 from WANT 
 where strip(names)="May";
 quit;

OR use

put(date, monname. -L);

The -L will left justify the result of the Put.

Re: Monname Results Do not match month names in proc SQL

Tom — Sat, 07 Nov 2020 05:03:08 GMT

You are being confused by looking at ODS output instead of looking at the actual data.

data months;
 do month=1 to 12 ;
   monname=put(mdy(month,1,1),monname.);
   output;
 end;
run;

proc print;
run;

If you look a the regular text output you can see the leading spaces.

Obs    month     monname

  1       1       January
  2       2      February
  3       3         March
  4       4         April
  5       5           May
  6       6          June
  7       7          July
  8       8        August
  9       9     September
 10      10       October
 11      11      November
 12      12      December

Either use the LEFT() function to remove the leading spaces. Or add the -L modifier on the end of the format specification so that the leading spaces are not saved to begin with.