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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Shmuel
Garnet | Level 18

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

13 REPLIES 13
Shmuel
Garnet | Level 18

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
PROC Star

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
Quartz | Level 8

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
Quartz | Level 8

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
Garnet | Level 18

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
PROC Star

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
Super User
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
Quartz | Level 8

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:

 

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
Data_User
Quartz | Level 8
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
Ksharp
Super User

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
Opal | Level 21

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
Garnet | Level 18

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
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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