Grouping variables in proc report

hari1109
hari1109 used Ask the Experts™
on
Thank you in advance.

i have a requirement where i have to group variables in the final dataset using proc report.
The final grouping and ordering must match the grouping and ordering shown in the below Webfocus output.  
In this example both  rsn, grp  are grouped and pn is ordered based on freq.
Webfocus output:
   rsn                   grp                  pn      name
   146                claims              124      bell
                                                 124      alica
   146                benefits           100      more
   146                 claims               20      mark
                                                   20      white

sas output:

   rsn                   grp                  pn      name
   146                claims              124      bell
                                                 124      alica
                                                   20      mark
                                                   20      white
   146                benefits           100      more
   
i am not able to maintain the order as it is in webfocus. is there any way we can maintain the order
as it is in webfocus.

Thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
if you swap the pn and grp columns, you should be able to get similar output.

does this fulfill your needs?

Author

Commented:
i can swap it but i am looking if it possible to get the output without any swap. The output should be what it is in webfocus. Thank you.
Commented:
This is the best I can come up with.  As noted by Iowa...unless you swap the order of the columns, you cannot get it to group by the way you are trying.

In addition, you can take the original PROC REPORT OUTPUT and have an Excel macro reformat the report by moving the columns.  That is easy to do.
proc report data=test out=test_out;
   column pn grp rsn name;
   define rsn / order;
   define grp / order;
   define pn / order descending;
   define name / display;
run;
   /* Option 1 */
proc print data=test_out;
   var rsn grp pn name;
run;
   /* Option 2 */
proc report data=test_out;
   column rsn grp pn name;
   define rsn / display;
   define grp / display;
   define pn / display;
   define name / display;
run;

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Thank you , to my understanding there is no way you can match the webfocus report unless do some manipulations before you start reporting the final dataset. For example the below code explains what can be done to get the order in final data set .
data FINAL2Z3 ;
set FINAL2Z2 ;
grp1=PUT(grp,8.);

RUN;
PROC SORT DATA=FINAL2Z3 OUT=FINAL11Z3;
BY  PROV_NAME  INQ_CNT1 DESCENDING grp1 pn;
RUN;
DATA FINAL11AZ3;
SET FINAL11Z3;
BY  PROV_NAME  INQ_CNT1 DESCENDING grp1 pn;
IF FIRST.pn THEN DO;
pn=pn;
grp1=grp1;
END;
ELSE DO;
pn='';
grp1='';
END;
RUN;
yes you are absolutely right on this. the other way is to manipulate the data and use proc print to get your report.

Commented:
Agreed, in this case data step and proc print are the way to go.

Author

Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for hari1109's comment http:/Q_27039364.html#35798978

for the following reason:

Implemented successfully

Author

Commented:
Issue solved with the code i am using.
i'm not sure if it's fair to close the question indicating that you have resolved this with your own code. yes, your code does work, with some prep-work required... and you would have to use proc print instead of proc report as indicated in your question as a requirement:

"i have a requirement where i have to group variables in the final dataset using proc report"
my opinion is that given the original requirement in the poster's question to have to use the PROC REPORT procedure, the answer is that there's no way to do so. hence my comment http:#35767039 should be accepted as the answer

Commented:
Agreed, but he did use the PROC PRINT method as suggested by my comment #35771404.  Wouldn't mind if you threw me a bone here with a few points as listing an alternative to PROC REPORT.
tobey1, i was actually waiting for you to make a recommendation :)

well how does 150 for http:#35771404 and 350 for http:#35767039 sound to you? or what do you suggest?

Commented:
I think that is fair.  Been away from the PC, so couldn't respond sooner. :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial