I have a data that looks like as follows:
NAME | DOCTOR | NURSE | ASSISTANT_PHYSICIAN | CONSULTATION | SURGERY | PRESCRIPTION | CONSULTATION | PROCEDURE |
Healing Hospital | 4 | 0 | 3 | 1 | 1 | 0 | 8 | 0 |
Helping Hospital | . | 1 | 9 | 0 | . | 3 | 0 | 1 |
Caring Hospital | 3 | 5 | . | 1 | 4 | 0 | 1 | . |
Treatment Hospital | 2 | 8 | 5 | . | 0 | 1 | 0 | 9 |
I want to create a cross-tab that looks like the following:
CONSULTATION | SURGERY | PRESCRIPTION | CONSULTATION | PROCEDURE | |
DOCTOR | |||||
NURSE | |||||
ASSISTANT_PHYSICIAN |
Please help! Any advice are welcomed!
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.
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!
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.
The numbers in the result table should have SUM of all the values in the dataset.
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.
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. 🙂
@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
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?
@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.
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
This is a report. The numbers should be SUM from the given dataset.
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.
@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!
@mkeintzThank you for your kind suggestion.
Here is the data table:
NAME | DOCTOR | NURSE | ASSISTANT_PHYSICIAN | CONSULTATION | SURGERY | PRESCRIPTION | PROCEDURE |
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 |
Total | 9 | 14 | 17 | 2 | 5 | 4 | 10 |
The result cross tab:
CONSULTATION | SURGERY | PRESCRIPTION | PROCEDURE | Total | |
DOCTOR | 11(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 |
NURSE | 16 (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_PHYSICIAN | 19 (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 |
Total | 46 | 55 | 52 | 70 | 223 |
Can you please help me generate this output. Thank you! 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.