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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.