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

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;
 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

5 REPLIES 5
fengyuwuzu
Pyrite | Level 9
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;
PGStats
Opal | Level 21

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
fengyuwuzu
Pyrite | Level 9

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 ?

Reeza
Super User

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;
fengyuwuzu
Pyrite | Level 9

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

 

Good night!

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 910 views
  • 2 likes
  • 3 in conversation