Help using Base SAS procedures

Sorting nominal variables in PROC FREQ (Based on order in PROC FORMAT)

Accepted Solution Solved
Reply
Super Contributor
Posts: 312
Accepted Solution

Sorting nominal variables in PROC FREQ (Based on order in PROC FORMAT)

[ Edited ]

I am running proc freq with format to control the order of the variables. I want to have the variables in the oder of VV, NV, LV, DD, SUB, but I got the following table:

Capture.PNG

 

 

 

It seems the format did not work. Here is the code I used and next message is my data.

 


/* data see below */
PROC FORMAT; VALUE $ statusfmt 1 = 'VV' 2 = 'NV' 3 = 'LV' 4 = 'DD' 5 = 'SUB'; proc freq data=companyA ; tables status / nopercent; format status $statusfmt.; run;
 

 


Accepted Solutions
Solution
‎05-12-2016 12:15 AM
Respected Advisor
Posts: 4,644

Re: Sorting nominal variables in PROC FREQ (Based on order in PROC FORMAT)

Make your values numeric to get the order you want:

 

PROC FORMAT;
   INVALUE statusfmt
    'VV'  = 1
    'NV'  = 2
    'LV'  = 3
    'DD'  = 4
    'SUB' = 5;
   VALUE statusfmt
    1 = 'VV'
    2 = 'NV'
    3 = 'LV' 
    4 = 'DD'
    5 = 'SUB';
run;
data companyA;
input ID status :statusfmt. @@;
format status statusfmt.;
datalines;
1 LV 2 LV 3 LV 4 LV 5 LV 6 LV 7 LV 8 LV 9 NV 
10 NV 11 NV 12 NV 13 NV 14 NV 15 NV 16 NV 17 NV 18 NV 19 NV 
20 NV 21 NV 22 NV 23 NV 24 NV 25 NV 26 NV 27 NV 28 NV 29 NV 
30 NV 31 NV 32 NV 33 NV 34 NV 35 NV 36 NV 37 NV 38 NV 39 NV 
40 NV 41 NV 42 NV 43 NV 44 NV 45 NV 46 NV 47 NV 48 NV 49 NV 
50 NV 51 NV 52 NV 53 NV 54 NV 55 NV 56 NV 57 NV 58 NV 59 NV 
60 NV 63 SUB 64 SUB 65 SUB 66 SUB 67 SUB 68 SUB 69 SUB 
71 VV 72 VV 73 VV 74 VV 75 VV 76 VV 77 VV 78 VV 79 VV 
80 VV 81 VV 82 VV 83 VV 84 VV 85 VV 86 VV 87 VV 88 VV 89 VV 90 VV
;

proc freq data=companyA ;
tables status / nopercent;
run;
PG

View solution in original post


All Replies
Super Contributor
Posts: 312

Re: Sorting nominal variables in PROC FREQ (Based on order in PROC FORMAT)

data companyA;
input ID status $;
datalines; 
1 LV 
2 LV 
3 LV 
4 LV 
5 LV 
6 LV 
7 LV 
8 LV 
9 NV 
10 NV 
11 NV 
12 NV 
13 NV 
14 NV 
15 NV 
16 NV 
17 NV 
18 NV 
19 NV 
20 NV 
21 NV 
22 NV 
23 NV 
24 NV 
25 NV 
26 NV 
27 NV 
28 NV 
29 NV 
30 NV 
31 NV 
32 NV 
33 NV 
34 NV 
35 NV 
36 NV 
37 NV 
38 NV 
39 NV 
40 NV 
41 NV 
42 NV 
43 NV 
44 NV 
45 NV 
46 NV 
47 NV 
48 NV 
49 NV 
50 NV 
51 NV 
52 NV 
53 NV 
54 NV 
55 NV 
56 NV 
57 NV 
58 NV 
59 NV 
60 NV 
63 SUB 
64 SUB 
65 SUB 
66 SUB 
67 SUB 
68 SUB 
69 SUB 
71 VV 
72 VV 
73 VV 
74 VV 
75 VV 
76 VV 
77 VV 
78 VV 
79 VV 
80 VV 
81 VV 
82 VV 
83 VV 
84 VV 
85 VV 
86 VV 
87 VV 
88 VV 
89 VV 
90 VV 
;
run;
Solution
‎05-12-2016 12:15 AM
Respected Advisor
Posts: 4,644

Re: Sorting nominal variables in PROC FREQ (Based on order in PROC FORMAT)

Make your values numeric to get the order you want:

 

PROC FORMAT;
   INVALUE statusfmt
    'VV'  = 1
    'NV'  = 2
    'LV'  = 3
    'DD'  = 4
    'SUB' = 5;
   VALUE statusfmt
    1 = 'VV'
    2 = 'NV'
    3 = 'LV' 
    4 = 'DD'
    5 = 'SUB';
run;
data companyA;
input ID status :statusfmt. @@;
format status statusfmt.;
datalines;
1 LV 2 LV 3 LV 4 LV 5 LV 6 LV 7 LV 8 LV 9 NV 
10 NV 11 NV 12 NV 13 NV 14 NV 15 NV 16 NV 17 NV 18 NV 19 NV 
20 NV 21 NV 22 NV 23 NV 24 NV 25 NV 26 NV 27 NV 28 NV 29 NV 
30 NV 31 NV 32 NV 33 NV 34 NV 35 NV 36 NV 37 NV 38 NV 39 NV 
40 NV 41 NV 42 NV 43 NV 44 NV 45 NV 46 NV 47 NV 48 NV 49 NV 
50 NV 51 NV 52 NV 53 NV 54 NV 55 NV 56 NV 57 NV 58 NV 59 NV 
60 NV 63 SUB 64 SUB 65 SUB 66 SUB 67 SUB 68 SUB 69 SUB 
71 VV 72 VV 73 VV 74 VV 75 VV 76 VV 77 VV 78 VV 79 VV 
80 VV 81 VV 82 VV 83 VV 84 VV 85 VV 86 VV 87 VV 88 VV 89 VV 90 VV
;

proc freq data=companyA ;
tables status / nopercent;
run;
PG
Super Contributor
Posts: 312

Re: Sorting nominal variables in PROC FREQ (Based on order in PROC FORMAT)

yes, your code works, in the case the data is entered by datalines. Thank you.

 

But my data has many other variables, and I do not want to go back to import step.

Is there a way to handle the order for already imported data ?

Super User
Posts: 17,818

Re: Sorting nominal variables in PROC FREQ (Based on order in PROC FORMAT)

The datalines is used to create fake data. You can use a data step to create your own data. Here's the example separated out (PS if this is the correct solutin please mark PGStats solution as correct). 

 

*This makes fake data;
data yourdata;
input ID status $ @@;
datalines;
1 LV 2 LV 3 LV 4 LV 5 LV 6 LV 7 LV 8 LV 9 NV 
10 NV 11 NV 12 NV 13 NV 14 NV 15 NV 16 NV 17 NV 18 NV 19 NV 
20 NV 21 NV 22 NV 23 NV 24 NV 25 NV 26 NV 27 NV 28 NV 29 NV 
30 NV 31 NV 32 NV 33 NV 34 NV 35 NV 36 NV 37 NV 38 NV 39 NV 
40 NV 41 NV 42 NV 43 NV 44 NV 45 NV 46 NV 47 NV 48 NV 49 NV 
50 NV 51 NV 52 NV 53 NV 54 NV 55 NV 56 NV 57 NV 58 NV 59 NV 
60 NV 63 SUB 64 SUB 65 SUB 66 SUB 67 SUB 68 SUB 69 SUB 
71 VV 72 VV 73 VV 74 VV 75 VV 76 VV 77 VV 78 VV 79 VV 
80 VV 81 VV 82 VV 83 VV 84 VV 85 VV 86 VV 87 VV 88 VV 89 VV 90 VV
;

*You would start here for your data;
PROC FORMAT;
   INVALUE statusfmt
    'VV'  = 1
    'NV'  = 2
    'LV'  = 3
    'DD'  = 4
    'SUB' = 5;
   VALUE statusfmt
    1 = 'VV'
    2 = 'NV'
    3 = 'LV' 
    4 = 'DD'
    5 = 'SUB';
run;

data formatted_data;
set yourdata; *Refer to your dataset here;
Status_numeric=input(status, statusfmt.);
format status_numeric statusfmt.;
run;


proc freq data=formatted_data; ;
tables status_numeric / nopercent;
run;
Super Contributor
Posts: 312

Re: Sorting nominal variables in PROC FREQ (Based on order in PROC FORMAT)

Thank you. This does work. I will mark PGStat's answer as solution.

 

Good night!

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 305 views
  • 2 likes
  • 3 in conversation