SQL query - delete fields according to condition

[sasuser_8]

Hi,

If I have a program like this that runs every month.

DATA HAVE;
INPUT CATEGORY$ TYPE MONTH ;
CARDS;

A 100  1
B 200  2 
B 100  3
B 200  1
A 100  2
A 200  1
B 200  1
A 100  1
A 200  2
A 200  3
A 100  5
A 200  9
B 200  10
A 100  1
A 200  11
A 200  11
;
RUN;


PROC SQL;
CREATE TABLE INVENTORY AS
SELECT CATEGORY,
       TYPE,
			 COUNT (CASE WHEN MONTH = 1 THEN TYPE END)   AS JANUARY,
			 COUNT (CASE WHEN MONTH = 2 THEN TYPE END)   AS FEBRUARY,
			 COUNT (CASE WHEN MONTH = 3 THEN TYPE END)   AS MARCH,
			 COUNT (CASE WHEN MONTH = 4 THEN TYPE END)   AS APRIL,
			 COUNT (CASE WHEN MONTH = 5 THEN TYPE END)   AS MAY,
			 COUNT (CASE WHEN MONTH = 6 THEN TYPE END)   AS JUNE,
			 COUNT (CASE WHEN MONTH = 7 THEN TYPE END)   AS JULY,
			 COUNT (CASE WHEN MONTH = 8 THEN TYPE END)   AS AUGUST,
			 COUNT (CASE WHEN MONTH = 9 THEN TYPE END)   AS SEPTEMBER,
			 COUNT (CASE WHEN MONTH = 10 THEN TYPE END)  AS OCTOBER,
			 COUNT (CASE WHEN MONTH = 11 THEN TYPE END)  AS NOVEMBER,
			 COUNT (CASE WHEN MONTH = 12 THEN TYPE END)  AS DECEMBER

			 FROM HAVE
			 GROUP BY CATEGORY, TYPE;
RUN; 

But I don't want months without data to appear in the report. I want to get this:

How should I proceed ?

[Kurt_Bremser]

Looks like a report, so

proc format;
value myfmt
  1 = "January"
  ...
  12 = "December"
;
run;

proc report data=have;
column category type n,month;
define category / group;
define type / group;
define n / "";
define month / "" across format=myfmt.;
run;

Untested, posted from my tablet.

[ballardw · Accepted solution]

My take:

proc format library=work;
value mymonth
1 = 'JANUARY'
2 = 'FEBRUARY'
3 = 'MARCH'
4 = 'APRIL'
5 = 'MAY'
6 = 'JUNE'
7 = 'JULY'
8 = 'AUGUST'
9 = 'SEPTEMBER'
10 = 'OCTOBER'
11 = 'NOVEMBER'
12 = 'DECEMBER'
;
run;



proc report data=have;
   columns category type n,month;
   define category/group;
   define type /group;
   define month/across '' order=internal format=mymonth.;
   define n /'';
run;

if you really want 0's in the body then set OPTIONS MISSING='0'; before proc report.

If you had an actual DATE value instead of some random numbers like 1, 2, 3 then the SAS supplied MONNAME would be the format to use for this report.

Or Proc tabulate which will have a slightly difference appearance:

proc tabulate data=have;
   class category type month;
   format month mymonth.;
   table category*type,
         month='' *n=''
         /misstext='0'
   ;
run;

And a very good reason to use formats instead of variables: Change the format, use it in the report with the SAME DATA SET.

proc format library=work;
value halfyear_qtr
1,2,3,4,5,6 = 'Jan-Jun'
7,8,9 = 'Jul-Sep'
10 = 'OCTOBER'
11 = 'NOVEMBER'
12 = 'DECEMBER'
;
run;
proc report data=have;
   columns category type n,month;
   define category/group;
   define type /group;
   define month/across '' order=internal format=halfyear_qtr.;
   define n/'' ;
run;

If a value to display in a report is based on a single variable then a custom format is quite often much easier to write, is more flexible, easier to change ( I have some formats with over 100 location codes for example that change adding one or two every few months). Instead of having to update hundreds of lines of IF/THEN/ELSE or worse yet CASE/WHEN code all I have to do is add 1234='New name' to the format, run the format definition program code and then the report updates based on the values.

[sasuser_8]

Thanks !

[Tom]

Don't use PROC SQL.

You could just make the report directly from that data you have:

proc report data=have;
  columns category type n,month;
  define category / group;
  define type / group;
  define month / across ;
  define n / ' ';
run;

If you really want the empty cells to be zeros you might try counting first.

proc freq data=have;
 tables type*category*month / noprint out=counts sparse;
run;

proc report data=counts;
  columns category type count,month;
  define category / group;
  define type / group;
  define month / across;
  define count / ' ';
run;

If you want the month numbers to appear as names just make a format and use it.