DATA Step, Macro, Functions and more

Order in Proc format function

Accepted Solution Solved
Reply
Contributor
Posts: 43
Accepted Solution

Order in Proc format function

Hi,

 

I am using the proc format option to change the group attributes to look like below. But after applying the format I am not the results in the correct order. Is there a way to fix it? Please refer to the attached file. Thanks

 

 

proc format;

value Cust_inc

                                low-10000 = "0-10,000"

                                10001-30000 = "10,001-30,000"

                                30001-50000 = "30,001-50,000"

                                50001-70000 = "50,001-70,000"

                                70001-100000 = "70,001-100,000"

                                100001-200000 = "100,001-200,000"

                                200000-1000000 = "200,000-1000,000"

                                1000001-high = "GT 1000,000"

                                other = "ND";

run;

 

Group                                   Feb16   Mar16  Apr16    May16  Jun16

0-10,000                             8487     8248     8014     7981     7705

10,001-30,000                 0             0             0             0             0

100,001-200,000            0             0             0             0             0

200,000-1000,000         0             0             0             0             0

30,001-50,000                 0             0             0             0             0

50,001-70,000                 0             0             0             0             0

70,001-100,000              0             0             0             0             0

GT 1000,000                      0             0             0             0             0

ND                                          0             0             0             0             0

Single Customer                10248   9844     9604     9500     9357


Accepted Solutions
Solution
‎02-16-2017 06:04 PM
Trusted Advisor
Posts: 1,405

Re: Order in Proc format function

Next code creates the report as desired, but you need decide about the first two rows,

as @art297 noticed at the begining of his post - either to sum or make other calculation

or leave in two rows of same group:

data test1;
infile datalines dlm="#";
input Group Jan17 Feb17 Mar17 Apr17 May17 Jun17 ;
datalines;
6000#55#6#290#338#131#99
220000#199#155#291#170#174#259
150000#228#164#252#221#294#262
86000#330#32#15#324#131#50
24000#67#216#77#216#43#229
1200075#285#146#157#161#58#66
#98#151#29#331#233#161
45000#191#82#53#136#73#312
;
run;
Proc format; value grpc low-10000="1" 10001-30000="2" 30001-50000="3" 50001-70000="4" 70001-100000="5" 100001-200000="6" 200001-1000000="7" 1000000-high="8" other="9"; run; Proc format; value gfmt 1="0-10,000" 2="10,001-30,000" 3="30,001-50,000" 4="50,001-70,000" 5="70,001-100,000" 6="100,001-200,000" 7="200,001-1000,000" 8="GT 1000,000" 9="ND"; run; data to_rep; format gcode; /* force gcode to be first on left */ set test1; gcode = input(put(group,grpc1.),1.); run; proc sort data=to_rep; by gcode;; run; proc print data=to_rep(drop=group); format gcode gfmt.; run;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,405

Re: Order in Proc format function

[ Edited ]

You probably use the format to create a report.

What is your code to create the report ?

You might find in the reporting procedure the answer to your query.

Look for an option like ORDER= ...;

Super User
Posts: 5,099

Re: Order in Proc format function

[ Edited ]

It looks like you are using ORDER=FORMATTED in the reporting procedure.  That would get the "ND" value to come out at the end (which I assume you want).  If you don't care about where "ND" shows up, just remove the ORDER=FORMATTED specification.  The default is probably ORDER=INTERNAL which would reshuffle the other rows into the correct order.

 

The problem with ORDER=FORMATTED as that as character strings, "200,000" is less than "30,000".  Since "2" is less than "3", the rows are ordered differently than what you are looking for.

 

One possible solution would be to redefine the format in a way that will put the rows in order.  For example:

proc format;

value Cust_inc

                                low-10000 = "(a) 0-10,000"

                                10001-30000 = "(b) 10,001-30,000"

                                30001-50000 = "(c) 30,001-50,000"

                                50001-70000 = "(d) 50,001-70,000"

                                70001-100000 = "(e) 70,001-100,000"

                                100001-200000 = "(f) 100,001-200,000"

                                200000-1000000 = "(g) 200,000-1000,000"

                                1000001-high = "(h) GT 1000,000"

                                other = "(i) ND";

run;

 

 

Then you can use ORDER=FORMATTED and control where the ND row shows up.

Contributor
Posts: 43

Re: Order in Proc format function

Hi, 

 

It's a massive code which also includes the macros. The objective is to print & export the results through ODS option to an excel. In the proc print option I am applying the formats I have defined as shown in the below example. When I apply the formats the results are not coming in the right order.

Current Results:

Group                             Feb16   Mar16  Apr16    May16   Jun16

0-10,000                         8487     8248     8014     7981        7705

10,001-30,000                 0             0            23            2             0

100,001-200,000             0             0             0             0             0

200,000-1000,000           0             0             0             0             0

30,001-50,000                 0            34            0             0             0

50,001-70,000                 0             0             43           0             0

70,001-100,000               0             0             0             0             0

GT 1000,000                   0             0             0             0             0

ND                                   0             0             0             0             0

Single Customer                10248   9844     9604     9500     9357

 

Expected Results:

Group                              Feb16   Mar16  Apr16    May16    Jun16

0-10,000                          8487     8248     8014     7981       7705

10,001-30,000                 0             0             0             0             0

30,001-50,000                 0             0             0             0             0

50,001-70,000                 0             0             0             0             0

70,001-100,000               0             0             0             0             0

100,001-200,000             0             0             0             0             0

200,000-1000,000           0             0             0             0             0

 GT 1000,000                   0             0             0             0             0

ND                                   0             0             0             0             0

Single Customer                10248   9844     9604     9500     9357

Contributor
Posts: 43

Re: Order in Proc format function

I can get this done in proc report & proc format options. But I am looking for a resolution using proc print option. Help is highly appreciated if there a way to solve this.

Trusted Advisor
Posts: 1,405

Re: Order in Proc format function

[ Edited ]

You have posted already in your first post - your foramt and the expected results. No need to post it twice.

In order to help you you should supply some test data and your code to create the report,

then we can try find a solution.

 

 

Super User
Posts: 5,099

Re: Order in Proc format function

Sorry, there's no way to do that within PROC PRINT.  It has no capabilities to change the order of the observations.  They have to be in the correct order coming in.  You said you have a PROC FORMAT solution, so I would guess that's the best way.

Super User
Posts: 9,691

Re: Order in Proc format function

An workaround way is padding blanks before it. the first one has the most blanks before value.

proc format;
value Cust_inc
                                low-10000 = "                      0-10,000"
                                10001-30000 = "            10,001-30,000"
                                30001-50000 = "         30,001-50,000"
                                50001-70000 = "       50,001-70,000"
                                70001-100000 = "   70,001-100,000"
                                100001-200000 = "  100,001-200,000"
                                200000-1000000 = "  200,000-1000,000"
                                1000001-high = " GT 1000,000"
                                other = "ND";
run;


Contributor
Posts: 43

Re: Order in Proc format function

Hi,

 

Below is the sample data and the code for the objective.

 

data test1;
infile datalines dlm="#";
input Group Jan17 Feb17 Mar17 Apr17 May17 Jun17 ;
datalines;
6000#55#6#290#338#131#99
220000#199#155#291#170#174#259
150000#228#164#252#221#294#262
86000#330#32#15#324#131#50
24000#67#216#77#216#43#229
1200075#285#146#157#161#58#66
#98#151#29#331#233#161
45000#191#82#53#136#73#312
;
run;

Proc format;
value gfmt
low-10000=" 0-10,000"
10001-30000=" 10,001-30,000"
30001-50000=" 30,001-50,000"
50001-70000=" 50,001-70,000"
70001-100000=" 70,001-100,000"
100001-200000=" 100,001-200,000"
200001-1000000=" 200,001-1000,000"
1000000-high=" GT 1000,000"
other="ND";
run;

 

proc print data=test1;
format group gfmt.;
run;

 

@Ksharp - I tried with the method you mentioned, but no luck Smiley Sad. Below are the results I get when I use the proc format as below.


Proc format;
value gfmt
low-10000=           "        0-10,000"
10001-30000=      "       10,001-30,000"
30001-50000=      "      30,001-50,000"
50001-70000=      "     50,001-70,000"
70001-100000=    "    70,001-100,000"
100001-200000=  "   100,001-200,000"
200001-1000000="  200,001-1000,000"
1000000-high=     " GT 1000,000"
other=                   "ND";
run;

 

Result:

 

ObsGroup427524278342811428424287242903
10-10,00055629033813199
2200,001-1000,000199155291170174259
3100,001-200,000228164252221294262
470,001-100,000330321532413150
510,001-30,000672167721643229
6GT 1000,0002851461571615866
7ND9815129331233161
830,001-50,000191825313673312
Contributor
Posts: 43

Re: Order in Proc format function

ObsGroup17-Jan17-Feb17-Mar17-Apr17-May17-Jun
10-10,00055629033813199
2200,001-1000,000199155291170174259
3100,001-200,000228164252221294262
470,001-100,000330321532413150
510,001-30,000672167721643229
6GT 1000,0002851461571615866
7ND9815129331233161
830,001-50,000191825313673312
Super User
Posts: 9,691

Re: Order in Proc format function


That is not the way to do it. You need define a new variable and sort it just like Arthur.T did.

 
data test1;
infile datalines dlm="#" dsd;
input Group Jan17 Feb17 Mar17 Apr17 May17 Jun17 ;
datalines;
6000#55#6#290#338#131#99
220000#199#155#291#170#174#259
150000#228#164#252#221#294#262
86000#330#32#15#324#131#50
24000#67#216#77#216#43#229
1200075#285#146#157#161#58#66
#98#151#29#331#233#161
45000#191#82#53#136#73#312
;
run;
Proc format;
  value gfmt
  low-10000     ="        0-10,000"
  10001-30000   ="   10,001-30,000"
  30001-50000   ="   30,001-50,000"
  50001-70000   ="   50,001-70,000"
  70001-100000  ="  70,001-100,000"
  100001-200000 =" 100,001-200,000"
  200001-1000000=" 200,001-1000,000"
  1000000-high  =" GT 1000,000"
  other         ="ND";
run;

proc sql;
create view test1_view as
select put(group,gfmt32.) as Group,Jan17 ,Feb17, Mar17, Apr17, May17, Jun17 
 from test1
  order by 1;
 quit;

 
 
proc print data=test1_view;
run;

PROC Star
Posts: 7,366

Re: Order in Proc format function

In your example data you have two records that fall within the 0 to 10000 category and you've never mentioned whether you want two lines printed in such cases, or want the sums, means, or something else.

 

That said, couldn't you do what you want just by sorting your data. e.g.:

 

data test1;
  infile datalines dlm="#";
  input Group Jan17 Feb17 Mar17 Apr17 May17 Jun17 ;
  datalines;
6000#55#6#290#338#131#99
220000#199#155#291#170#174#259
150000#228#164#252#221#294#262
86000#330#32#15#324#131#50
24000#67#216#77#216#43#229
1200075#285#146#157#161#58#66
#98#151#29#331#233#161
45000#191#82#53#136#73#312
;
run;
Proc format;
  value gfmt
  low-10000     ="        0-10,000"
  10001-30000   ="   10,001-30,000"
  30001-50000   ="   30,001-50,000"
  50001-70000   ="   50,001-70,000"
  70001-100000  ="  70,001-100,000"
  100001-200000 =" 100,001-200,000"
  200001-1000000="200,001-1000,000"
  1000000-high  ="GT 1000,000"
  other         ="ND";
run;

proc sort data=test1;
  by group;
run;

proc print data=test1;
  format group gfmt.;
run;

HTH,

Art, CEO, AnalystFinder.com

 

Solution
‎02-16-2017 06:04 PM
Trusted Advisor
Posts: 1,405

Re: Order in Proc format function

Next code creates the report as desired, but you need decide about the first two rows,

as @art297 noticed at the begining of his post - either to sum or make other calculation

or leave in two rows of same group:

data test1;
infile datalines dlm="#";
input Group Jan17 Feb17 Mar17 Apr17 May17 Jun17 ;
datalines;
6000#55#6#290#338#131#99
220000#199#155#291#170#174#259
150000#228#164#252#221#294#262
86000#330#32#15#324#131#50
24000#67#216#77#216#43#229
1200075#285#146#157#161#58#66
#98#151#29#331#233#161
45000#191#82#53#136#73#312
;
run;
Proc format; value grpc low-10000="1" 10001-30000="2" 30001-50000="3" 50001-70000="4" 70001-100000="5" 100001-200000="6" 200001-1000000="7" 1000000-high="8" other="9"; run; Proc format; value gfmt 1="0-10,000" 2="10,001-30,000" 3="30,001-50,000" 4="50,001-70,000" 5="70,001-100,000" 6="100,001-200,000" 7="200,001-1000,000" 8="GT 1000,000" 9="ND"; run; data to_rep; format gcode; /* force gcode to be first on left */ set test1; gcode = input(put(group,grpc1.),1.); run; proc sort data=to_rep; by gcode;; run; proc print data=to_rep(drop=group); format gcode gfmt.; run;
Contributor
Posts: 43

Re: Order in Proc format function

Hello Everyone,

 

Thanks very much for your contributions. I have solved my objective through the methods you have explained. I really appreciate all your help on this. 

 

Best Regards

☑ This topic is solved.

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

Discussion stats
  • 13 replies
  • 309 views
  • 3 likes
  • 5 in conversation