Assistance with splitting data with delimiter ","

[Jeffery_Porter]

Hello and good afternoon,

I am newer to SAS VA and I have a request that I have been stumped with and I am hoping someone can assist.

We have a QCA (sort of like a survey) that feeds into the Data that I use to build dashboards. I am working to create a dashboard at this time and I am unable to paint a good picture of the data because of my limited experience.

I am building a dashboard for reasons why a patient discharged to acute care (basically, why they got worse and had to discharge to another hospital). The staff members then fill out the survey and the data is placed into SAS for me to manipulate. 

One of the questions on the QCA is "What symptoms were present at the time of discharge" and it is a "select all the apply" sort of question, and all of the data comes into SAS as a giant string of information. (see the screenshot #1 below) I do not have access to the MySQl/Coding part of SAS and I am limited to VA only view. I was wondering if there was a way that I can count each time the word "Agitation" was used, or "Fever >100" and be able to display this data in a bar chart that shows which instance is being seen mostly commonly for our RTAs. right now it will display everything that was selected, which in many causes if the patient had multiple symptoms on discharge, is very unhelpful and will lump it as "Agitation,Fever>100,High Heart Rate,Hypoxia/Low O2." Therefore when I go to sort this, it will display the entire item as a single variable, instead of 4 different variables that would need to be considered. (Screenshot 2).

In summary, is it possible to take the symptoms (that are separated by a ,) be able to count each time an item that is contained within the , is found, and then list these individual symptoms in a bar chart that will provide the users of dashboard with clarity on which symptoms are most commonly present at DC for the RTA patient? Thank you!

SS#1

SS#2

[HunterT_SAS]

I'm not 100% certain VA can do exactly what you'd like, but the first place you should check is creating Calculated Items:

It may be that you just need to create a new calculated for each symptom that is something simple. For instance maybe you create one called Fever > 100:

If 'SymptomsCausedPatientTransfer' CONTAINS ('Fever > 100')
RETURN 'Yes'
Else 'No'

There are also several string functions that might be useful as well and if you hover over each, it will tell you what they do:

Hopefully that can get you started!

[Jeffery_Porter]

Thank you for taking the time to respond to my query! I understand this logic, I have attempted this before where if true return 1, else 0. Perhaps I did this incorrectly last time before, so I have 3 calculated measures made now. I have Symp - Agitation | Symp - SOB | Symp - Pain - Other.

How can I go about placing them in a visual that makes it easy to understand? Perhaps that is my problem from the beginning, when I attempt I get something like this:

Is there someway that I can create a single graph that represents the # of times each of these symptoms was selected? perhaps Bar Graph does not have these capabilities.

Or do I need to combine them in some sort of way? 

I apologize for my silly questions, I am relatively new. I will continue to mess around with it to see what I can accidently stumble across! Thank you all again.

[FredrikE]

I think you are on the right track by creating different dimension variables depending on what the "General RTA category" contains. The visualization part can be done by using different objects for each of the variables. Maybe different bar charts, key indicators can be used?
Another way could be to create differnt meassures depending on the contined value and then use these in an bar chart as different measures. In that case tou can create a "total" dimension variable with only one value; "Total". Just my 5 cents, have not tested my thoughts 🙂

[Jeffery_Porter]

Thank you for taking the time to respond!

I am not sure if I understand what you mean, do you have screenshots or a demonstration of what your talking about so I can best understand? Thank you in advance!

[Jeffery_Porter]

Here is where I am at - and this is due to my limited knowledge:

1. I have created all of the calculations that will count 1 overtime a specific term is found in the string.

2. I am attempting to figure out which graph can take all of these "Measures" and display them in such a way where we can see comparison visually, such as a Bar chart.

3. I did find that I can use a Cross tab to display the data, but it does not provide a solid picture for the viewer (see below.)

Please let me know if what I am saying makes some sort of sense, if it does not i will try to provide clarity, however, I hope that someone has dealt this this before! Thank you all again for your assistance.

[Jeffery_Porter]

Hello and good morning, I have not received a reply yet and I am wondering where I could go to potentially have this question answered? Is there a helpline that SAS provides in situations like this that can assist with newer users like me?

Thank you!

[HunterT_SAS]

Ok next thing to try since you've got all of these created is to put them all in a bar chart with a single categorical value. To do that you might try the following:

1. Create a new calculated item that is a character and just a single string, like "Reason For Discharge"

2. Create a new bar chart and add this Reason For Discharge calculated item to it

3. Add all of your calculated measures to it as well. 

Using some sample data I have with some measures, you should end up with something like this:

You could then turn on some other options like data values or segment values, turn off labels, whatever. 

Some challenges though:

- You won't be able to get labels for the measures visible in the bar chart for each bar. Users will have to rely on the legend to see what is what. 

- Sorting might get tricky. You won't be able to sort by "largest value to smallest value" this way. Instead you'll order the measures as you see fit in the roles tab, and then that's how they will stay. 

- If the values of the measures vary too greatly, the bar chart may automatically split into a lattice graph. I don't think you'll see that but FYI anyway.

- You won't be able to use this chart as a source of an action to another chart (i.e. this chart cannot filter another chart). It should work when filtered by another chart or prompt though if you're doing that. 

Unfortunately I don't think VA really has a great native solution here for this kind of problem and something like this might be the best that can be done. Ideally the work for this would be done in your data before loading it to Visual Analytics. 

[Panagiotis]

If I'm understanding your objective correctly, I think your issue is the structure of the data.  You need to get your data to go from something like this where it's a row of data for each patient (wide table):

To having a long table. That is, for each patient's issue, have a row indicating the issue (headache, pain, fever, whatever). I did that then simply created a bar chart.

There could be some unique way of doing this within the Visual Analytics application, but I'm not aware of it. You can easily do it in the SAS Viya environment. You will either need to create a program or use SAS Data Studio (Prepare Data) button in show applications. Data Studio is point and click. Otherwise you can create a SAS/SQL/Python whatever code to restructure that data.

Again, maybe there is a way in Visual Analytics, but I'm not aware of it. I would just create some ETL process to clean and structure the data how I need it prior to bringing it into Visual Analytics.

- Peter

[Jeffery_Porter]

Thank you for posting a response!

Regarding your post, I am now able to access some of the Data preparation features for SAS, would I be able to utilize anything inside of here: (SS#1) to create a function that will seperate by the "," delimiter?

If not, I do like your long table solution, would this allow the user to be able to filter the page if they clicked on, say, "Pain"?

Look forward to your response!

-Jeff

SS#1:

[Jeffery_Porter]

Thank you for this, your solution did work, and your counters to why VA is not the best solution is now understood. I can see some of the downsides to utilizing the data in this way, it is not really "usable" as you eluded to in your post. however, it is a starting point and I thank you very much for your help!

[Panagiotis]

Thank you for this, your solution did work, and your counters to why VA is not the best solution is now understood

Just to be clear, it seems like it SAS Visual Analytics is the final solution you need (dashboard). It reads data from the distributed CAS server and is built for visualizations and modeling of your data using a variety of objects it provides. In your scenario you need to completely restructure the data, which I don't think I don't think Visual Analytics can do. It does provide some data preparation features, but you need to completely rearrange your data for what you want. Which is pretty normal.

SAS Viya has other applications for data preparation like Data Studio, SAS Studio (here you can use point and click tools, SAS/SQL/Python code and more) or you can use open source languages like Python in other programming interfaces.

While you can do some data preparation in Visual Analytics, I personally prefer doing the majority of cleaning and structuring of my data prior to starting my dashboard in Visual Analytics. I like to program (SAS/Python/SQL) though, so I like to use code for my data prep since I have the most control that way and can easily automate that process.