Monname Results Do not match month names in proc SQL

[sun538]

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.

[Reeza]

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

[ballardw · Accepted solution]

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.

[Tom]

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.