DATA Step, Macro, Functions and more

proc tabulate and order by hidden ID variable

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 133
Accepted Solution

proc tabulate and order by hidden ID variable

Hi SAS Experts,

I use EG 6.1 and create a table with proc tabulate.

proc tabulate data=data.data601;

class haupt_kat_text unter_kat_text zeitraum;

var normmw anz_nnnb anz_b;

table haupt_kat_text *unter_kat_text *zeitraum,

    N anz_nnnb*f=4.0 anz_b*f=4.0 ;   

run;

In the data set data601 I also got haupt_kat_ID and unter_kat_ID

but those are not in the proc tabulate because they should be hidden and only the text shown.

Nevertheless, I want to order by those IDs (haupt_kat_ID and unter_kat_ID) but they

should stay hidden.

Any idea how I can achive that?

I dont think manually reformating with PROC FORMAT is feasible because there are more than 100 rows.


Accepted Solutions
Solution
‎04-09-2015 10:44 AM
Super User
Posts: 10,466

Re: proc tabulate and order by hidden ID variable

Specify ORDER=DATA for presorted data otherwise it will default to order=formatted.

Adding a format alone isn't going to solve your problem. I think it is ADD a variable with the order needed, you can use the format you created. And the sort by that variable before proc tabulate.

View solution in original post


All Replies
Super User
Posts: 10,466

Re: proc tabulate and order by hidden ID variable

The base approach would be to sort the data into the desired order before Proc Tabulate and then use ORDER=DATA option.

SAS Super FREQ
Posts: 8,740

Re: proc tabulate and order by hidden ID variable

Or, the alternate approach would be to use the 2 "hidden" variables in PROC REPORT as ORDER or GROUP variables and hide them with the NOPRINT option. But if you are interested in using TABULATE for summary purposes, then adjusting the code to use ORDER=DATA might be quicker.

cynthia

Frequent Contributor
Posts: 133

Re: proc tabulate and order by hidden ID variable

Hi Cynthia,

Thanks for your reply. I dont know if the below case is one where I have to resort to a hidden variable.

My problem is that the sorting by BL_ does not work. BL_ is a variable shown on the horizontal axis above the table.

proc Format;

Value $BL_                      'DL' =0

                'jj' =1

                'xx' =2

                'NI' =3

                'NW' =5

                'kl' =6

                'RP' =7

                'be' =8

                'as' =9

                'SL' =10

                'BE' =11

                'gf' =12

                'baV' =13

                'SN' =14

                'bh' =15

                'mn' =16;

run;

proc tabulate data=xxx order=formatted;

var COUNT_ITEMS;

class TRIAL_REAS EXTRA_REAS sub_group sub_group_TEXT BL_;

table TRIAL_REAS='PG'*EXTRA_REAS='MG'*sub_group='Untergr.'*sub_group_TEXT='Untergr. Text'

COUNT_ITEMS=''*BL_='';

run;

Super User
Posts: 10,466

Re: proc tabulate and order by hidden ID variable

You need to associate the format with the variable to use formatted otherwise the default format will be used.

To use the format add:

format bl_ $bl_. ;

However the displayed values will be 1, 2, 3.

Frequent Contributor
Posts: 133

Re: proc tabulate and order by hidden ID variable

Hi ballardw!

thanks for you reply! I understand what you mean. Why are the displayed values changed? I only want to do sorting  not change the look of the data :/ :/

Frequent Contributor
Posts: 133

Re: proc tabulate and order by hidden ID variable

The data is ordered correcly. I just dont know why SAS messes it up in the proc tabulate.

Solution
‎04-09-2015 10:44 AM
Super User
Posts: 10,466

Re: proc tabulate and order by hidden ID variable

Specify ORDER=DATA for presorted data otherwise it will default to order=formatted.

Adding a format alone isn't going to solve your problem. I think it is ADD a variable with the order needed, you can use the format you created. And the sort by that variable before proc tabulate.

☑ This topic is SOLVED.

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

Discussion stats
  • 7 replies
  • 521 views
  • 6 likes
  • 3 in conversation