DATA Step, Macro, Functions and more

Generate Crosstab in SAS

Reply
Contributor
Posts: 39

Generate Crosstab in SAS

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! 

Valued Guide
Posts: 631

Re: Generate Crosstab in SAS

Posted in reply to pmpradhan

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.

Contributor
Posts: 39

Re: Generate Crosstab in SAS

[ Edited ]
Posted in reply to andreas_lds

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!

Respected Advisor
Posts: 3,293

Re: Generate Crosstab in SAS

[ Edited ]
Posted in reply to pmpradhan

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
Contributor
Posts: 39

Re: Generate Crosstab in SAS

Posted in reply to PaigeMiller

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

Respected Advisor
Posts: 3,293

Re: Generate Crosstab in SAS

[ Edited ]
Posted in reply to PaigeMiller

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
Contributor
Posts: 39

Re: Generate Crosstab in SAS

Posted in reply to PaigeMiller

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. Smiley Happy

Super User
Posts: 13,946

Re: Generate Crosstab in SAS

Posted in reply to pmpradhan

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

Contributor
Posts: 39

Re: Generate Crosstab in SAS

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?

Respected Advisor
Posts: 3,293

Re: Generate Crosstab in SAS

[ Edited ]
Posted in reply to pmpradhan

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
Super User
Posts: 13,946

Re: Generate Crosstab in SAS

Posted in reply to pmpradhan

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

Contributor
Posts: 39

Re: Generate Crosstab in SAS

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

Trusted Advisor
Posts: 1,399

Re: Generate Crosstab in SAS

Posted in reply to pmpradhan

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.

Respected Advisor
Posts: 3,293

Re: Generate Crosstab in SAS


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
Contributor
Posts: 39

Re: Generate Crosstab in SAS

[ Edited ]

@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! Smiley Happy

Ask a Question
Discussion stats
  • 15 replies
  • 296 views
  • 2 likes
  • 5 in conversation