DATA Step, Macro, Functions and more

Using the same value Twice in a Format or Sort

Accepted Solution Solved
Reply
Super Contributor
Posts: 480
Accepted Solution

Using the same value Twice in a Format or Sort

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
Tenure1.1
 1.2
Non-Tenure.12



Desired output:

 

  description   tenure2 a b group
Tenure1.1
 Non-Tenure.12
Tenure1.
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;


Accepted Solutions
Solution
a month ago
Super Contributor
Posts: 480

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

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;

View solution in original post


All Replies
Super User
Posts: 9,671

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

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;

x.png

Super User
Posts: 10,476

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

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.

 

Solution
a month ago
Super Contributor
Posts: 480

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

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 87 views
  • 0 likes
  • 3 in conversation