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

I have insurance claim database. I want to know from which hospital I got maximum claims. How can I do this in PROC SQL procedure?

Thanks in advance.

Arun

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

Linlin,

Count(*) will count all of the rows. In the cases when only populated fields need be counted, the variable names will be needed:

data have;

input hospital $ claim;

cards;

a 23

b 45

a .

b 43

a 55

;

proc sql ;

      select

     hospital,count(claim) as count

             from have

                  group by hospital

                            order by count desc;

 

  quit;

Haikuo

View solution in original post

8 REPLIES 8
Haikuo
Onyx | Level 15

Can you post some sample/fake data that we can have a taste of it?

Aquarian27
Calcite | Level 5

Here is sample data for Claim table:

BenefUserIDClmAmountApproveAmountStatusNameTreatmentNameHospId
1082013317121SettledSurgical ManagementHopID-846
1143700028080SettledCataractHopID-490
1174000040000SettledSurgical ManagementHopID-32
141140780CancelledCataractHopID-766
1471683715637SettledSurgical ManagementHopID-490
1481205311953SettledMedical Management ( Conservartive Management)HopID-846
1595000020000SettledSurgical ManagementHopID-499
1862123116000SettledMedical Management ( Conservartive Management)HopID-490

  1. Which hospital has got more number of patients?
Haikuo
Onyx | Level 15

If one row is equivalent to one patient, then linlin's solution is good for you. if you only care the number of different patient, the use: count (distinct BenefUserID)

Regards,

Haikuo

Linlin
Lapis Lazuli | Level 10

do you want something like below?

data have;

input hospital $ claim;

cards;

a 23

b 45

a 56

b 43

a 55

;

proc sql outobs=1;

      select

     hospital,count(claim) as count

             from have

                  group by hospital

                            order by count desc;

        

  quit;

updated after Doc Muhlbaier and Haikuo's comments.  Thank you!

Doc_Duke
Rhodochrosite | Level 12

Linlin -- slick!  Learned something new; good way to start the day.

One correction, no parentheses around the OUTOBS=1 option.

Haikuo
Onyx | Level 15

Linlin,

Count(*) will count all of the rows. In the cases when only populated fields need be counted, the variable names will be needed:

data have;

input hospital $ claim;

cards;

a 23

b 45

a .

b 43

a 55

;

proc sql ;

      select

     hospital,count(claim) as count

             from have

                  group by hospital

                            order by count desc;

 

  quit;

Haikuo

DF
Fluorite | Level 6 DF
Fluorite | Level 6

A nice way of limiting the output - looks like it'll be a handy workaround for the lack of "top" in SAS's SQL implementation.

One thing worth noting though is that in the event of a tie it would only print out the first.  For example if two hopitals both had 50 claims, only the first would be output - presumably this is determined by input data order, but I guess we'd have to take it as arbitrary.

art297
Opal | Level 21

DF: While I, too, learned something new from Linlin's post, I agree that neither of the suggestions address ties.  How about:

data have;

input hospital $ claim;

cards;

a 23

b 45

a .

b 43

a 55

c 55

;

proc sql ;

  select hospital from

    (select hospital,count(claim) as counts

       from have

         group by hospital)

           having counts=max(counts)

   ;

quit;

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
  • 8 replies
  • 4030 views
  • 6 likes
  • 6 in conversation