BookmarkSubscribeRSS Feed
pmpradhan
Quartz | Level 8

I have a data that looks like as follows:

 

NAMEDOCTORNURSEASSISTANT_PHYSICIANCONSULTATIONSURGERYPRESCRIPTIONCONSULTATIONPROCEDURE
Healing Hospital40311080
Helping Hospital.190.301
Caring Hospital35.1401.
Treatment Hospital285.0109

 

I want to create a cross-tab that looks like the following:

 CONSULTATIONSURGERYPRESCRIPTIONCONSULTATIONPROCEDURE
DOCTOR     
NURSE     
ASSISTANT_PHYSICIAN     

 

Please help! Any advice are welcomed! 

15 REPLIES 15
andreas_lds
Jade | Level 19

You have to describe the logic to be used to transform your data to the requested output-format. And you should post data in a format that we can use in sas code without any additional work => post data as data-step using datalines statement.

 

The column "CONSULTATION" exists twice in your data => this is not possible in a dataset.

pmpradhan
Quartz | Level 8

Hi Andreas,

 

I have revised the question and I'm also including datalines statement for quick recreation of data in SAS.

 

data test;
input name $ Doctor Nurse Assistant_physician Consultation Surgery Prescription Procedure;
datalines;
Healing_Hospital 4 0 3 1 1 0 0
Helping_Hospital . 1 9 0 . 3 1
Caring_Hospital 3 5 . 1 4 0 .
Treatment_Hospital 2 8 5 . 0 1 9
;
run;

 

I want the output table as

 CONSULTATIONSURGERYPRESCRIPTIONPROCEDURE
DOCTOR    
NURSE    
ASSISTANT_PHYSICIAN    

 

Thank you!

PaigeMiller
Diamond | Level 26

You said you want crosstabs in a particular formatted table.

 

Are the numbers in the tables the SUM of all values that go into that table? Or is it the average? Or is it something else?

 

Since there are multiple nurses in the data, yet you have one row for NURSES, does that mean we just average or sum the number of consultations across all nurses? Why would that be different than the average or sum across all doctors? Why would that be different than the average or sum across all physician assistants?

 

In other words, it's still very unclear to me how to go from the input data to the final table.

--
Paige Miller
pmpradhan
Quartz | Level 8

The numbers in the result table should have SUM of all the values in the dataset.

PaigeMiller
Diamond | Level 26

Okay, it's the SUM.

 

Now please explain the logic used to obtain the values in the table, as I asked:

 

Since there are multiple nurses in the data, yet you have one row for NURSES, does that mean we just sum the number of consultations across all nurses? Why would that be different than the sum across all doctors? Why would that be different than the sum across all physician assistants?

 

In other words, it's still very unclear to me how to go from the input data to the final table.

--
Paige Miller
pmpradhan
Quartz | Level 8

Apologies, I forgot to answer the latter question. 

Since there are multiple nurses in the data, yet you have one row for NURSES, does that mean we just sum the number of consultations across all nurses? Yes

 

Why would that be different than the sum across all doctors?

Why would that be different than the sum across all physician assistants?

The only reason I can think of is because the total no of nurses, doctors and physician assistants is different.

 

If you are asking that if there is a way to attribute each consultation, surgery types of care to nurses and doctors? then, I don't think there is a way to distinct that which is why wanted to hear experts opinion. 🙂

ballardw
Super User

@pmpradhan wrote:

Hi Andreas,

 

I have revised the question and I'm also including datalines statement for quick recreation of data in SAS.

 

data test;
input name $ Doctor Nurse Assistant_physician Consultation Surgery Prescription Procedure;
datalines;
Healing_Hospital 4 0 3 1 1 0 0
Helping_Hospital . 1 9 0 . 3 1
Caring_Hospital 3 5 . 1 4 0 .
Treatment_Hospital 2 8 5 . 0 1 9
;
run;

 

I want the output table as

  CONSULTATION SURGERY PRESCRIPTION PROCEDURE
DOCTOR ?  ?  ?  ?
NURSE ?   ?  ?  ?
ASSISTANT_PHYSICIAN  ?  ?  ?  ?

 

Thank you!


Given your example data what number should go where the question marks are above?

I actually do not see any obvious connection in the source data that let me say anything about an intersection of Doctor and Consultation

pmpradhan
Quartz | Level 8

Exactly that was where I was confused as I well. 

Do you think a cross tab of such data could be done or the data lacks enough details to make it a cross tab?

PaigeMiller
Diamond | Level 26

@pmpradhan wrote:

Exactly that was where I was confused as I well. 

Do you think a cross tab of such data could be done or the data lacks enough details to make it a cross tab?


It is entirely up to you what to do with the data. It's your problem. You have to decide, not us. None of us here understand the problem.

 

This is no longer a SAS problem, but a problem of defining what needs to be done.

--
Paige Miller
ballardw
Super User

Is this supposed to be a data set for the result or is this a report (to be read by people?)

 

And you should provide what kind of numbers go in the result as it isn't obvious

pmpradhan
Quartz | Level 8

This is a report. The numbers should be SUM from the given dataset.

mkeintz
PROC Star

Then, from the sample data you provided, manually fill in sample values in the cell question marks in @ballardw's sample table.  We'll be better able to understand the rules you have in mind.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
PaigeMiller
Diamond | Level 26

@mkeintz wrote:

Then, from the sample data you provided, manually fill in sample values in the cell question marks in @ballardw's sample table.  We'll be better able to understand the rules you have in mind.


No, that's not sufficient. He needs to fill in the table as you said, and then EXPLAIN how he got those numbers in those cells.

 

@pmpradhan we have been asking you to give us the details, but this has been a series of asking for more information about this problem, and you not providing it. We shouldn't have to ask for details repeatedly. Explain!

--
Paige Miller
pmpradhan
Quartz | Level 8

@mkeintzThank you for your kind suggestion.

 

Here is the data table:

NAMEDOCTORNURSEASSISTANT_PHYSICIANCONSULTATIONSURGERYPRESCRIPTIONPROCEDURE
Healing Hospital4031100
Helping Hospital.190.31
Caring Hospital35.140.
Treatment Hospital285.019
Total9141725410

 

The result cross tab:

 CONSULTATIONSURGERYPRESCRIPTIONPROCEDURETotal
DOCTOR11(i.e. total doctor 9+ total consultation 2)14(i.e. total doctor 9+ total surgery 5)13 (i.e. total doctor 9+ total prescription 4)19 (i.e. total doctor 9+ total procedure 10)57
NURSE16 (i.e. total nurse 14+ total consultation 2)19 (i.e. total nurse 14+ total surgery 5)18 (i.e. total nurse 14+ total prescription 4)24 (i.e. total nurse 14+ total procedure 10)77
ASSISTANT_PHYSICIAN19 (i.e. total assistant_physician 17+ total consultation 2)22 (i.e. total assistant_physician 17+ total surgery 5)21 (i.e. total assistant_physician 17+ total prescription 4)27 (i.e. total assistant_physician 17+ total procedure 10)89
Total46555270223

 

Can you please help me generate this output. Thank you! 🙂

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
  • 15 replies
  • 1481 views
  • 2 likes
  • 5 in conversation