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
'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;
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
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
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
Thanks,
but how can I keep the same numerical order. now it mixes up the order based on the new values.
Thanks again,
Nazanin
Use order=internal on the DEFINE statement for the variable to use the numeric value of the variable to control order.
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: