Desktop productivity for business analysts and programmers

How to Proc Tabulate By group?

Reply
Contributor
Posts: 23

How to Proc Tabulate By group?

[ Edited ]

This is working code

 

/* Crosstab: main_job and work_inj_descript_code_sec2_ */
PROC TABULATE
DATA=WORK.DATA_JOINED6;

CLASS main_job / ORDER=UNFORMATTED;
CLASS work_inj_descript_code_sec2_ / ORDER=UNFORMATTED;
/*BY work_related_fulln;*/
TABLE /* Row Dimension */
work_inj_descript_code_sec2_,
/* Column Dimension */
main_job*
ColPctN;
;
FORMAT
main_job main_job.
work_inj_descript_code_sec2_ work_inj_descript_code_sec2_.;
RUN;

 

 By I have a categorical variable (some_var) which has value of =1 and =2, I would like the above code to execute one for some_var=1 and another for some_var=2

 

/* Crosstab: main_job and work_inj_descript_code_sec2_ */
PROC TABULATE
DATA=WORK.DATA_JOINED6;

CLASS main_job / ORDER=UNFORMATTED;
CLASS work_inj_descript_code_sec2_ / ORDER=UNFORMATTED;
/*BY work_related_fulln;*/
TABLE /* Row Dimension */
work_inj_descript_code_sec2_,
/* Column Dimension */
main_job*
ColPctN;
;
FORMAT
main_job main_job.
work_inj_descript_code_sec2_ work_inj_descript_code_sec2_.;
BY some_var;
RUN;

 

However, I get the following error - 

 

ERROR: Data set WORK.DATA_JOINED6 is not sorted in ascending sequence. The current BY group has Are you here today for an illness
or injury related to your work? = 2 and the next BY group has Are you here today for an illness or injury related to your w
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 17 observations read from the data set WORK.DATA_JOINED6.
NOTE: PROCEDURE TABULATE used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

-------------------------------------------------------------------------------------------

Update

 

I have tried this, and it seems to be working

 

PROC SORT DATA=WORK.DATA_JOINED6 OUT=WORK.DATA_JOINED7;
	by work_related_fulln; 

PROC TABULATE
DATA=WORK.DATA_JOINED7;
	
	CLASS main_job /	ORDER=UNFORMATTED;
	CLASS work_inj_descript_code_sec2_ /	ORDER=UNFORMATTED;
	/*BY work_related_fulln;*/
	TABLE /* Row Dimension */
		work_inj_descript_code_sec2_,
		/* Column Dimension */
		main_job*
  		ColPctN;
	;
  	FORMAT
    	main_job main_job.
		work_inj_descript_code_sec2_ work_inj_descript_code_sec2_.;

	By work_related_fulln;

RUN;

But in the log statement, it gave me this warning:

 

WARNING: A class, frequency, or weight variable is missing on every observation.
NOTE: The above message was for the following BY group:
      Are you here today for an illness or injury related to your work?=R
WARNING: A class, frequency, or weight variable is missing on every observation.
NOTE: The above message was for the following BY group:
      Are you here today for an illness or injury related to your work?=-99

Is this a warning I can overlook, do should I fix anything?

Super User
Posts: 23,980

Re: How to Proc Tabulate By group?

For BY to work you need to sort your data according to the same order you list your variables in the BY statement. 

So use PROC SORT before the PROC TABULATE to order your data and then use your code. I would also move the BY statement to right after the PROC statement. It's common convention and then a person can tell quickly if the procedure uses a BY statement or not.

Contributor
Posts: 23

Re: How to Proc Tabulate By group?

I have tried your suggestion and it seems to work, but I get this warning "WARNING: A class, frequency, or weight variable is missing on every observation." Any thoughts?

Super User
Posts: 13,913

Re: How to Proc Tabulate By group?

Try this modification:

 

PROC TABULATE
   DATA=WORK.DATA_JOINED6;

   CLASS main_job / ORDER=UNFORMATTED;
   CLASS work_inj_descript_code_sec2_ / ORDER=UNFORMATTED;
   Class some_var;
   TABLE /* PAGE DIMENSION*/
         Some_var,
         /* Row Dimension */
         work_inj_descript_code_sec2_,
         /* Column Dimension */
         main_job*ColPctN;
   ;
   FORMAT
      main_job main_job.
      work_inj_descript_code_sec2_ work_inj_descript_code_sec2_.
      /* add any format for some_var if available*/
   ;

RUN;

 

On this forum in helps to post code or log results into a code box opened with the menu icon {i}

Ask a Question
Discussion stats
  • 3 replies
  • 441 views
  • 0 likes
  • 3 in conversation