topic Re: Order in Proc format function in SAS Programming

Order in Proc format function

Data_User — Fri, 10 Feb 2017 19:31:56 GMT

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

Re: Order in Proc format function

Shmuel — Fri, 10 Feb 2017 19:40:43 GMT

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

Re: Order in Proc format function

Astounding — Sat, 11 Feb 2017 02:52:24 GMT

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.

Re: Order in Proc format function

Data_User — Sat, 11 Feb 2017 14:02:40 GMT

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

Re: Order in Proc format function

Data_User — Sat, 11 Feb 2017 14:07:36 GMT

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.

Re: Order in Proc format function

Shmuel — Sat, 11 Feb 2017 19:11:55 GMT

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.

Re: Order in Proc format function

Astounding — Sat, 11 Feb 2017 19:39:12 GMT

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.

Re: Order in Proc format function

Ksharp — Sun, 12 Feb 2017 13:50:07 GMT

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;

Re: Order in Proc format function

Data_User — Sun, 12 Feb 2017 22:03:34 GMT

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

Re: Order in Proc format function

Data_User — Sun, 12 Feb 2017 22:05:01 GMT

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

Re: Order in Proc format function

art297 — Mon, 13 Feb 2017 00:22:18 GMT

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

Re: Order in Proc format function

Ksharp — Mon, 13 Feb 2017 02:47:00 GMT


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;

Re: Order in Proc format function

Shmuel — Mon, 13 Feb 2017 10:03:07 GMT

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;

Re: Order in Proc format function

Data_User — Thu, 16 Feb 2017 23:04:27 GMT

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