BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
metallon
Pyrite | Level 9

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

7 REPLIES 7
ballardw
Super User

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

Cynthia_sas
SAS Super FREQ

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

metallon
Pyrite | Level 9

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;

ballardw
Super User

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.

metallon
Pyrite | Level 9

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 😕 😕

metallon
Pyrite | Level 9

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

ballardw
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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