Order in Proc format function

[Data_User]

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

[Shmuel]

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= ...;

[Astounding]

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.

[Data_User]

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

[Data_User]

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.

[Shmuel]

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.

[Astounding]

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.

[Ksharp]

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;

[Data_User]

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 :(. 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:

Obs	Group	42752	42783	42811	42842	42872	42903
1	0-10,000	55	6	290	338	131	99
2	200,001-1000,000	199	155	291	170	174	259
3	100,001-200,000	228	164	252	221	294	262
4	70,001-100,000	330	32	15	324	131	50
5	10,001-30,000	67	216	77	216	43	229
6	GT 1000,000	285	146	157	161	58	66
7	ND	98	151	29	331	233	161
8	30,001-50,000	191	82	53	136	73	312

[Data_User]

Obs	Group	17-Jan	17-Feb	17-Mar	17-Apr	17-May	17-Jun
1	0-10,000	55	6	290	338	131	99
2	200,001-1000,000	199	155	291	170	174	259
3	100,001-200,000	228	164	252	221	294	262
4	70,001-100,000	330	32	15	324	131	50
5	10,001-30,000	67	216	77	216	43	229
6	GT 1000,000	285	146	157	161	58	66
7	ND	98	151	29	331	233	161
8	30,001-50,000	191	82	53	136	73	312

[Ksharp]

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;

[art297]

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

[Shmuel · Accepted solution]

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;

[Data_User]

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