Using the same value Twice in a Format or Sort

[DavidPhillips2]

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:

  description   tenure2 a b group

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;

[Ksharp]

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;

[ballardw]

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.

[DavidPhillips2 · Accepted solution]

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;