I am trying to display the same text value in row one of a report but control the order of the text values that have repeating values by using a format. However using the same text value twice causes the output below. How can I display the text “Tenure” twice?
Results in:
description tenure2 a b group
Tenure | 1 | . | 1 |
1 | . | 2 | |
Non-Tenure | . | 1 | 2 |
Desired output:
Tenure | 1 | . | 1 |
Non-Tenure | . | 1 | 2 |
Tenure | 1 | . | 2 |
proc format;
invalue tenureInValue
'Tenure' = 1
'Non-Tenure' = 2
'Tenure-2nd-Display' = 3;
value tenureFormatValue
1 = 'Tenure'
2 = 'Non-Tenure'
3 = 'Tenure';
run;
proc sql;
create table miniquery (
tenure varchar2(25),
description varchar2(25),
group int
);
insert into miniquery (tenure, description, group)
values("Tenure", "a", 1);
insert into miniquery (tenure, description, group)
values("Non-Tenure", "b", 2);
insert into miniquery (tenure, description, group)
values("Tenure-2nd-Display", "a", 2);
quit;
run;
data myemployee; set miniquery;
tenure2 = input(tenure, tenureInValue.);
run;
proc report nowd data= myemployee ;
column tenure2 description group;
define tenure2 / group format=tenureFormatValue. order = internal;
define group / group;
define description / across ;
run;
The trick is to create a temporary variable in the compute block. SAS Technical Support provided the below:
proc format;
invalue tenureInValue
'Tenure' = 1
'Non-Tenure' = 2
'Tenure-2nd-Display' = 3;
value tenureFormatValue
1 = 'Tenure'
2 = 'Non-Tenure'
3 = 'Tenure';
run;
proc sql;
create table miniquery (
tenure varchar2(25),
description varchar2(25),
group2 int
);
insert into miniquery (tenure, description, group2)
values("Tenure", "a", 1);
insert into miniquery (tenure, description, group2)
values("Non-Tenure", "b", 2);
insert into miniquery (tenure, description, group2)
values("Tenure-2nd-Display", "a", 2);
quit;
run;
data myemployee; set miniquery;
tenure2 = input(tenure, tenureInValue.);
run;
proc report nowd data= myemployee ;
column tenure2 tenurecomp description group2;
define group2 / group;
define tenurecomp / computed 'tenure' format=tenureFormatValue.;
define tenure2 / group order = internal;
define description / across ;
compute tenurecomp;
if tenure2 ne . then hold = tenure2;
tenurecomp = hold;
endcomp;
run;
It is really awkward code.
I don't know why SAS behavior this way. Maybe ask sas technique support.
proc format;
invalue tenureInValue
'Tenure' = 1
'Non-Tenure' = 2
'Tenure-2nd-Display' = 3;
value tenureFormatValue
. = 'Tenure'
1 = 'Tenure'
2 = 'Non-Tenure'
3 = 'Tenure';
run;
proc sql;
create table miniquery (
tenure varchar2(25),
description varchar2(25),
group int
);
insert into miniquery (tenure, description, group)
values("Tenure", "a", 1);
insert into miniquery (tenure, description, group)
values("Non-Tenure", "b", 2);
insert into miniquery (tenure, description, group)
values("Tenure-2nd-Display", "a", 2);
quit;
run;
data myemployee; set miniquery;
tenure2 = input(tenure, tenureInValue.);
run;
proc report nowd data= myemployee ;
column group tenure2 description _group ;
define group / group noprint;
define tenure2 / group format=tenureFormatValue. order = internal descending ;
define description / across ;
define _group/computed 'group';
compute _group;
_group=group;
lag=lag(group);
if missing(_group) then _group=lag;
endcomp;
run;
Any format that has multiple values assigned to the same display value in effect are the same group:
Proc format ;
value grp;
1-4 = 'First'
5-10='Second'
;
run;
data junk;
do i = 1 to 10;
output;
end;
run;
proc freq data=junk;
tables i;
format i grp.;
run;
the format creates bins. If you do not what that behavior either the display text should be different OR, for a very limited number of procedures (tabulate, means and summary) a multilabel format may be of help.
But I have to say that with such a limited example I am not sure what you are attempting to show.
The trick is to create a temporary variable in the compute block. SAS Technical Support provided the below:
proc format;
invalue tenureInValue
'Tenure' = 1
'Non-Tenure' = 2
'Tenure-2nd-Display' = 3;
value tenureFormatValue
1 = 'Tenure'
2 = 'Non-Tenure'
3 = 'Tenure';
run;
proc sql;
create table miniquery (
tenure varchar2(25),
description varchar2(25),
group2 int
);
insert into miniquery (tenure, description, group2)
values("Tenure", "a", 1);
insert into miniquery (tenure, description, group2)
values("Non-Tenure", "b", 2);
insert into miniquery (tenure, description, group2)
values("Tenure-2nd-Display", "a", 2);
quit;
run;
data myemployee; set miniquery;
tenure2 = input(tenure, tenureInValue.);
run;
proc report nowd data= myemployee ;
column tenure2 tenurecomp description group2;
define group2 / group;
define tenurecomp / computed 'tenure' format=tenureFormatValue.;
define tenure2 / group order = internal;
define description / across ;
compute tenurecomp;
if tenure2 ne . then hold = tenure2;
tenurecomp = hold;
endcomp;
run;
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 25. 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.