data test; y='ic icc 1icc 1ic'; length y2 $40 ; y2=prxchange('s/\b(ic|icc)\b/1icc/i',-1,y); run; ... View more

LOL, it's validating to see that this is indeed bad database administrating going on. I can also think of another data warehouse that provides data with these issues (we just asked the data provider to delete the entire variable)...so it's a bit surprising to me that they seem to either be incompetent, or inexperienced at understanding how to correctly CSV files. Thank you so much for your help and insight! ... View more

I don't see the need for fancy regex, the duo countw + scan seem capable of solving the problem:   data want; have='pn1111-pn2222-pn3333-pn4444-1icc1111-1icc2222-1icc3333-2icc1111'; length term $10; do i = 1 to countw(have, '-'); term = scan(have, i, '-'); output; end; run; ... View more

Hi @appleorange,   I frequently use FINDSTR from the command line (cmd.exe) to search keywords and phrases in .sas files. If that didn't work I would probably turn to the "Find/replace in files" feature of my text editor, but I wouldn't even think of using the Windows file search. ... View more

I can guarantee that GCHART will be much harder to work with.   The fit of your axis labels could be addressed by use of a different format for if the Month variable is an actual date value which seems likely. The note about uneven spaces is because months are not the same length so you can pretty much ignore that. The SAS YYMON6. format will display values as a 2-digit year followed by month abbreviation. If you want to force the appearance of a - then a custom format. proc format library=work; picture yy_MON (default=7) low - high = '%y-%b' (datatype=date) ; run; data example; x= '15Jan2018'd; put x yy_mon.; run; Xaxis option splitchar of fitpolicy with split while using the above custom format to split text on the - may help a bit.   If your users know each bar should represent a month then THIN option reducing the text for some ticks may be appropriate.   Reducing the size of the font for the axis may help as well as increasing the size of the display area for the graph. ... View more

Well, changing your data structure slightly makes this a lot simpler. That's the first step I do, make it a long data set so TYPE is a variable that holds values of A/B and add a variable called VALUE that holds the amounts.   Here's the PROC TABULATE solution. Notice the use of the ALL keywords that control which totals you get and where they go. If you put ALL before the variable name for example, totals will be your first row.     data long; set tabletest; Type='A'; Value = typeA; output; Type='B'; value = typeB; output; drop typeA typeB; run; proc tabulate data=long; class referral_total referral_source type; var value; table (referral_total*(referral_source all) all), (type all)*value=''*sum=''*f=8.; run; Here's a decent paper on the topic if you need to understand the details or what anything in particular is doing.   http://support.sas.com/resources/papers/proceedings11/260-2011.pdf ... View more

Yes, my main issue was with the multiple columns. I think I have a good starting point to approach this, so thanks again. And definitely, I had made a mistake manually adding up the columns so I wanted to find out a way to do this without having to edit the columns whenever the original columns change out of my control. ... View more

Yes, exactly.  I ended up finding a calculator developped by Prescription Drug Monitoring Program Training and Technical Assistance Center (PDMP TTAC) that has a spreadsheet within the tool with the dosage strength information for patches, solutions, etc for opioids.     I think all of the drug name info. are formated like that, yes.  So although I did find a shortcut with the tool, I will try the scan method which might help for all drug classes, not just opioids.  Thank you. ... View more

Yes. You are right. That will expand your small into a big table .. "change the table from a range horizontally to vertically" do Anchor_Date=Begin    to   End ;   output; end; ... View more