label variables on Proc report

[NazaninSAS]

Hi, here is my output. I would like to re-label EEOG and NOC

for example, when the EEOG is 2, I would like to change it to (2) middle managers. Please not that the EEOG and NOC are  numbers in my input DB, but I want to re-label it to a text in my output.

EEOG	NOC	country	province	CMA	# of Employees
2					2,706
2	.	Canada			2,706
3					12,689
3	1111	Canada			6,123
	1112	Canada			184
	1114	Canada			7
	1121	Canada			518
	1122	Canada			13
	1123	Canada			150
	2161	Canada			42
	2171	Canada			1,529
	2174	Canada			1,460
	4151	Canada			6
	4162	Canada			270
	4163	Canada			268
	4164	Canada			2,083
	4169	Canada			5
	5111	Canada			30
	5122	Canada			1

this is how I define variables:

proc report data=work.WFA2 spanrows;

columns EEOG NOC Country Province CMA PRI ;

define EEOG / group 'EEOG' left;

define NOC/ group missing 'NOC' left;

define Country/ group missing ' country' left;

define province / group missing 'province' left;

define CMA / group missing 'CMA' left;

define PRI/ analysis n format=comma8. '# of Employees ' center;

Thanks for help.

Nazanin

[SuryaKiran]

'Label' is not the right word here. It's better say format values. Create a user defined formats.

proc format ;
value eeog 2='Middle Manager'
		   3="Manager 1"
		   4="Manager 2"
		   other="Engineer";
run;

proc report data=work.WFA2 spanrows;
 
 
columns EEOG NOC Country Province CMA PRI ;
define EEOG / group 'EEOG' left format=eeog.; /* Format applied here */
define NOC/ group missing 'NOC' left;
define Country/ group missing ' country' left;
define province / group missing 'province' left;
define CMA / group missing 'CMA' left;
define PRI/ analysis n format=comma8. '# of Employees ' center;
run;

[NazaninSAS]

Thanks,

I did it, but it does not show it. I think in my input DB, EEOG is a number so because of that it does not accept the formatting?

What do you think?

Thanks,

Nazanin

[Cynthia_sas]

Hi:

  I think there's something amiss with your data that you haven't shown us. The format worked for me using fake data with EEOG variable as a number:

Cynthia

[NazaninSAS]

Thanks Cynthia,

these are my actual formatting needs:

Proc format;

value EEOG

2= '(2)Middle and Other Managers'

3= '(3)Professionals'

4= '(4)Semi-Professionals & Technicians'

5= '(5)Supervisors'

7= '(7)Administrative & Senior Clerical'

10= '(10)clerical Personnel'

12= '(12)Semi-skilled Manual Workers'

13= '(13)Other Sales & Service Personnel'

run;

but when I run it, the order is like 10, 12, 13 , 2

but I want the order as 2, 3,5,7,10,12,13

Thanks,

Nazanin

[NazaninSAS]

Thanks,

but how can I keep the same numerical order. now it mixes up the order based on the new values.

Thanks again,

Nazanin

[ballardw]

Use order=internal on the DEFINE statement for the variable to use the numeric value of the variable to control order.

[Cynthia_sas]

Hi:

  This is one of the ways I use "helper" variables ... to control ordering when the formatted order isn't what I want. You have to be careful because (1) and (10) would appear next to each other when the format was applied if you don't control it any other way. Since you didn't post any data, I went back to my fake example using SASHELP.CLASS -- not the best example since there are only 6 ages and therefore 6 values for my fake EEOG, and I didn't have enough values to get into (10) and (11), but you get the idea.

here's the code I used:

data WFA2;
  ** make eeog variable with fake data;
  set sashelp.class;
  eeog = age - 10;
  if eeog = 6 then eeog_ord = 1;
  else eeog_ord = eeog +1;
run;

proc format ;
value eeog 1="(1) Executive Manager"
           2='(2) Middle Manager'
		   3="(3) Manager 1"
		   4="(4) Manager 2"
		   5="(10) Engineers"
		   6="CEO";
run;

title '1) without using eeog_ord';
proc report data=work.WFA2 spanrows;
columns EEOG name age sex height weight;
define EEOG / order 'EEOG'  format=eeog. order=internal;  
run;

title '2) with using eeog_ord';
proc report data=work.WFA2 spanrows;
columns eeog_ord EEOG name age sex height weight;
define eeog_ord / order /* noprint */;
define EEOG / order 'EEOG'  format=eeog. order=internal;  
run;
title;

Cynthia

[NazaninSAS]

Thanks,

I used this:

Proc format;

value EEOG

2= '(2)Middle and Other Managers'

3= '(3)Professionals'

4= '(4)Semi-Professionals & Technicians'

5= '(5)Supervisors'

7= '(7)Administrative & Senior Clerical'

10= '(10)clerical Personnel'

12= '(12)Semi-skilled Manual Workers'

13= '(13)Other Sales & Service Personnel'

run;

proc report data=work.WFA2 spanrows;

columns EEOG NOC Country Province CMA PRI Abor Aborprc LMAAbor Expected Gap;

define EEOG / group 'EEOG' format=EEOG. order=internal;

define NOC/ group missing 'NOC' left;

define Country/ group missing ' country' left;

define province / group missing 'province' left;

define CMA / group missing 'CMA' left;

define PRI/ analysis n format=comma8. '# of Employees ' center;

it worked very well, but it takes more time to run. where is the best place to place "proc format"? I'm using it right before "proc report". but it takes more time to run the program compared to before having the "proc format".

Regards,

Nazanin

[Cynthia_sas]

Hi:
If those categories are not going to change, you could make a permanent format library. When you place the PROC FORMAT in the code as you show, you are essentially writing the format to the WORK location every time the job runs. If you wrote the PROC FORMAT to a permanent location, then you'd only have to have a libname for that location in order to have the format available -- you would not have to re-create it every time.

Cynthia