individual report output in descending order.

[savvy_sas_user]

Hello All,

The question is you have unique policy ID# and each PolicyID# can have more than 1 vehicleID. So I want an individual report of policy# with the highest # of vehicleIDs. So in this case the policyID 102 had highest vehicleIDs and I want it to be displayed on the top of the report so in descending ORDER.

I know proc report is one way, can you please tell me any other method to get the same output.

Below is code that I used to figure using proc report, everything works but it doesn't create the output in descending. Please HELP...I need the answer soon...Thanks.

data insurance;

input policy_id$ vehicle_id$ ;

cards;

100 ABC

100 XYZ

100 JLI

101 EFG

102 HIJ

102 KLM

102 NOP

102 QRS

102 TUV

;

run;

Proc Report Data=insurance nowd;

by policy_id;

column policy_id vehicle_id;

Define policy_id / group;

Define vehicle_id / order order=freq;

[Cynthia_sas · Accepted solution]

Hi:

  I guess I'm confused. The vehicle_ids are unique. Each one has a freq of 1, so I'm not sure that ORDER=FREQ is going to do anything for you. When you envision the results, do you say you want to see policy_id of 102, followed by 100, followed by 101???

  Also, why do you have BY POLICY_ID? Using a BY statement will force Policy_ID 100, 101, 102 as the order -- no matter what you have in PROC REPORT. I think you need to take off the BY statement completely. There are other ways to get a new page for every POLICY_ID, such as using a BREAK AFTER POLICY_ID/PAGE; statement in PROC REPORT.

  Then, if you want the POLICY_ID with the highest number of rows to be first, then you should consider:

Define policy_id / group order=freq descending;

Define vehicle_id / order;

cynthia

[savvy_sas_user]

Hi Cynthia,

This is exactly what I wanted. I wanted the policy_id with the highest number of rows to be first.. 102 then 100 and then 101.

Is there any other method to do the same thing?

Thanks a lot

[Cynthia_sas]

Hi:

  I really don't understand what you want/mean. Your original posted code was never going to give you what you say you wanted. Did you try the code I posted, with ORDER=FREQ moved to the DEFINE statement for POLICY_ID? You will have to take the BY statement out of the code, too. I suppose there are other ways that I could envision doing what you describe, but they are not as straightforward as using PROC REPORT.

cynthia

[savvy_sas_user]

Yes, i tried the code you posted and it worked.