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

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

View solution in original post

4 REPLIES 4
Cynthia_sas
SAS Super FREQ

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
Calcite | Level 5

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
SAS Super FREQ

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
Calcite | Level 5

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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 757 views
  • 0 likes
  • 2 in conversation