BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
sasuser_8
Obsidian | Level 7

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:

MONTH.jpg

How should I proceed ?

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

4 REPLIES 4
Kurt_Bremser
Super User

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
Super User

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.

Tom
Super User Tom
Super User

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.

Tom_0-1716670634919.png

 

 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 735 views
  • 2 likes
  • 4 in conversation