topic Re: Using the same value Twice in a Format or Sort in SAS Programming

Using the same value Twice in a Format or Sort

DavidPhillips2 — Mon, 17 Jul 2017 13:32:21 GMT

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;

Re: Using the same value Twice in a Format or Sort

Ksharp — Mon, 17 Jul 2017 14:13:08 GMT

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;

Re: Using the same value Twice in a Format or Sort

ballardw — Mon, 17 Jul 2017 15:08:16 GMT

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.

Re: Using the same value Twice in a Format or Sort

DavidPhillips2 — Wed, 19 Jul 2017 13:59:17 GMT

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;