Hi, I’m trying to extract a price from a string and I’m having some with my code. This is what I start with: PROMOTION_TITLE Aug Earn $25 v2 external Aug Earns $125 v2 external Aug Earns $50 v2 external This is what I need: Price $25 $125 $50 This is my program so far. I can't seem to get the syntax right. data test (keep=PROMOTION_TITLE price) ; set  a.promo;   price =(input(prxchange('s/(\w+)_(\d+)_(\w+)/$2/',-1,PROMOTION_TITLE),8.))/100;   format price dollar8.2; run; Any assistance will be greatly appreciated. Thanks!  ... View more

Hi All, I'm trying to right a program which will sum up all the unique words in a variable -  this needs to be not case sensitive.  I need to use just Base SAS. CONTENT signs that I might be experiencing Candida? A. Here is our list of 100 Common Candida Symptoms associated with systemic. Use zyrtec i    a week of sttn.. about 3 weeks ago.. LO had bad allergies so he had to take some zyrtec but he slept sooo good Allergy Aid Cleansing Expert services Are Necessary Some folks are incapable of having a great night's sleep So the output I'm looking for is something like this (this is not the full list): Word Count zyrtec 2 Some 2 of 3 I 2 he 2 had 2 experiencing 1 Common 1 Cleansing 1 Candida 1 a 2 Is there a way to write a do loop? It would obviously way too difficult  to you just string functions to do this. Any assistance is greatly appreciated. Thanks!  ... View more

Hi all, I’m using ods tagsets.excelxp to create a formatted excel report. Right now the number of times I have to run the report is based off of a variable called Test_Number (numeric values 1, 2, 3, 4….).  I have a macro variable Test which I have to hard code for each pass.  What I would like to have is a macro that runs through all the possible values in test and outputs to a multisheet workbook;  So, each sheet is a test in the workbook . I have used ods tagsets.excelxp to produce multisheet workbooks before but I can’t figure out how to write a macro which produces the number of sheets based on the values  in a variable. Below is my program. Any assistance will be greatly appreciated. Thanks! /***Choose Test Number*****/ %let Test= 1 ;run; ods listing close; options rightmargin=.1in leftmargin=.1in Bottommargin=.1in topmargin=.1in; ods tagsets.excelxp file="C:\Users\Desktop\Test Table.xml" style=minimal options(sheet_interval='none'  ORIENTATION='portrait' AUTOFIT_HEIGHT='yes' PAGE_ORDER_ACROSS='yes'); proc report data= title nowd ; column Test_Title; define Test_Title / display style(column)={ just=right  font_size=10pt  cellwidth=1.7in}   style(header)={ just=center foreground=white background=white}; where Test_Number=&Test; run; proc report data=Label nowd style(column)={just=center font_size=10pt font_face='Arial Narrow'cellwidth=2.7in} Style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};   column  dum Control Test;   define dum / display   style(column)={ just=right  font_size=10pt  cellwidth=1.4in}   style(header)={ just=center foreground=white background=white};   where  Test_Number=&Test;   run; proc report data=Final nowd style(column)={just=center font_size=10pt font_face='Arial Narrow'cellwidth=2.7in} Style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};   column KPI Control Test lift prob Statistically_Significant_95 percent_Significant;   define KPI / display   style(column)={ just=right  font_size=10pt font_face= 'Arial Narrow' FONT_WEIGHT =BOLD  cellwidth=1.6in}   style(header)={ just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};   define Control / display   style(column)={just=center  font_size=10pt font_face= 'Arial Narrow'cellwidth=.7in background=#FFCC99}   style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};   define Test  / display   style(column)={just=center  font_size=10pt font_face= 'Arial Narrow'cellwidth=.7in }   style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};   define lift  / display   style(column)={just=center font_size=10pt font_face= 'Arial Narrow'cellwidth=.7in}   style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};   define prob  / display   style(column)={just=center font_size=10pt font_face= 'Arial Narrow'cellwidth=.7in}   style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};   define Statistically_Significant_95 / display   style(column)={just=center font_size=10pt font_face= 'Arial Narrow' cellwidth=1.in  FONT_WEIGHT =BOLD}   style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};   define Percent_Significant/ display   style(header)= {just=center  font_size=10pt FONT_WEIGHT=BOLD font_face= 'Arial Narrow' background=cxcccccc}   style(column)= {just=center font_size=10pt FONT_WEIGHT=BOLD font_face= 'Arial Narrow'  cellwidth=.9in}; where Test_Number=&Test; run; title; ods _all_ close; ... View more

Hi all, I've written a program using tagsets.excelxp and proc report. There are a few formatting things/Styling  I’m having trouble figuring out how to program. I have attached a spreadsheet with examples of my output now and my desired output. 1.  In the media method column I need to vertical center the values “Direct Mail” and “Email” so they are centered to the month groups and remove the cell boarders.  2.  The header for the totals is outputting “SubT” when it should be “Direct Mail Sub Total:”, “Email Sub Total:” and “Grand Total: “and the headers need to be bold and the “Grand Total:” total header needs to be an 11 point font. 3.  In both the subtotal rows I need the total  lift% calculated in the lift column, so ((test/control)-1). 4.  I would like a gray shaded row between the email subtotal and the grand total and make the top border of the grand total row thicker. Below is what I have for my now program including sample data and again I’ve attached a spreadsheet with examples of my output now and my desired output.  Any help on any of these points will be greatly appreciated! Thanks.  Sample Data data have; length MEDIA_METHOD $13 Month_Year $15 Control 8  Test 8  Lift 8; INPUT MEDIA_METHOD $1-13 Month_Year $14-27 Control Test Lift ; format Lift percentn.;   datalines; Direct Mail  February 2014 1 1 0.00 Direct Mail March 2014    2 1 -0.50 Direct Mail April 2014    1 1 0.00 Direct Mail May 2014    3 3 0.00 Direct Mail June 2014    2 1 -0.50 Direct Mail July 2014    2 1 -0.50 Direct Mail August 2014 2 2 0.00 Direct Mail September 2014 2 2 0.00 Direct Mail October 2014 1 1 0.00 Direct Mail November 2014 2 2 0.00 Direct Mail December 2014 2 1 -0.50 Direct Mail January 2015 2 2 0.00 Email    February 2014 7 7 0.00 Email    March 2014    8 6 -0.25 Email    April 2014    6 6 0.00 Email    May 2014    8 6 -0.25 Email    June 2014    15 11 -0.27 Email    July 2014    11 6 -0.45 Email    August 2014 12 7 -0.42 Email    September 2014 11 8 -0.27 Email    October 2014 10 6 -0.40 Email    November 2014 11 7 -0.36 Email    December 2014 21 11 -0.48 Email    January 2015 13 7 -0.46 ; Program %let rstart='February 2014'; %let rend='January 2015'; ods listing close; options rightmargin=.1in leftmargin=.1in Bottommargin=.1in topmargin=.1in Papersize='legal'; ods escapechar='^'; ods tagsets.excelxp file="C:\Users\Desktop\Monthly Test.xls" style=minimal options(sheet_interval='none'  ORIENTATION= 'Landscape' AUTOFIT_HEIGHT= 'yes' embedded_titles= 'yes' sheet_name="Average HH Touches"); title; TITLE JUSTIFY=l FONT='Arial Narrow'  HEIGHT=12pt "^S={font_face='Arial Narrow' FONT_WEIGHT =BOLD background=CXFFE4E1 font_size=13pt} Average Touches per Household ^S={}"  ; Title2 JUSTIFY=l FONT='Arial Narrow'  HEIGHT=11pt "^S={font_face='Arial Narrow' FONT_WEIGHT =BOLD background=CXFFE4E1 font_size=13pt} Reporting Period: &rstart to &rend^S={}" ; proc report data=Hh_touches nowd style(column)={just=c font_size=10pt font_face= 'Arial Narrow'} style(header)={font_size=10pt font_face= 'Arial Narrow' FONT_WEIGHT =BOLD background=CXFFE4E1}; column ("^S={font_face='Arial Narrow' FONT_WEIGHT =BOLD background=CXFFE4E1 font_size=13pt} Average Touches per Household ^S={}"  MEDIA_METHOD Month_Year  Control Test lift); define MEDIA_METHOD / group style(column)={just=left  font_size=10pt font_face= 'Arial Narrow'cellwidth=1.3in } style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc}; define Month_Year / display style(column)={just=left  font_size=10pt font_face= 'Arial Narrow'cellwidth=1.2in } style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc}; define Control / display style(column)={just=center  font_size=10pt font_face= 'Arial Narrow'cellwidth=.7in } style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc}; define Test  / display style(column)={just=center  font_size=10pt font_face= 'Arial Narrow'cellwidth=.7in } style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc}; define lift  / display style(column)={just=center font_size=10pt font_face= 'Arial Narrow'cellwidth=.7in} style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc}; define MEDIA_METHOD / group; define Control / sum; define test / sum; break after MEDIA_METHOD / summarize skip ol; rbreak after / summarize skip ol; compute after MEDIA_METHOD; MEDIA_METHOD= catx(MEDIA_METHOD,' ', 'SubTotal'); endcomp; run; ods tagsets.excelxp close; ods listing; ... View more

Hi. This is a tricky data set transformation. I need to create a summary dataset/report which tracks the flow of these purchases over time. I have a dataset which gives a signup date for an overall service and 9 variables which give the purchase dates for different add on products.  If the add on variable dates match the signup date then those add on products were included with the signup package.  Any add on variable purchase date that comes after the signup date are products which are purchased during the history of the active account.  This is what it looks like: ID signup_DT preferredhd_tv_estbd_dt ultimate_estbd_dt quant_estbd_dt FullyLoaded_estbd_dt HB_estbd_dt Cin_estbd_dt time_estbd_dt router_estbd_dt internet_estbd_dt 98663699 4/7/14 4/9/14 4/7/14 9/12/14 10/15/14 . 4/7/14 4/7/14 4/12/14 . 33663798 4/11/14 . 4/11/14 . 4/11/14 4/11/14 4/11/14 4/11/14 6/11/14 7/15/14 43663463 5/12/14 5/12/14 5/12/14 9/5/14 9/17/14 . . . . . 77661437 5/16/14 . 5/16/14 . 10/31/14 . 5/16/14 5/16/14 11/16/14 . 85662295 5/29/14 . . 5/29/14 . 6/12/14 . . 11/16/14 . 36656756 6/4/14 . . . 6/4/14 6/4/14 6/12/14 6/4/14 6/4/14 12/4/14 67662646 6/14/14 . 6/14/14 8/31/14 . . 6/17/14 6/14/14 . 6/22/14 55663786 6/26/14 . . . 8/14/14 6/26/14 7/8/14 6/26/14 11/30/14 . 44663191 8/21/14 . 9/30/14 . . . . 1/12/15 . 10/31/14 The variables I’m trying to produce are: Signup month (easy to do) A count of the total number of signups for that month (easy to do) A overall count of additional products which  included with sign up A variable which has all add on product values (transposed from original dataset). A count of the different products purchased on the startup date A count of add on products purchased after the signup date that were purchased in the same month of the signup date Then month variables which count the additional add on products by mont If I take just April, this is the output I'm looking for: Sign_up_Month Sign_up_count Initial_Products_total Products Prod_Purchased_on_Signup AddPro_ April_After_SU May June July August September October April 2 8 preferredhd_tv_estbd_dt 1 April 2 8 ultimate_estbd_dt 2 April 2 8 quant_estbd_dt 1 April 2 8 FullyLoaded_estbd_dt 1 1 April 2 8 HB_estbd_dt 1 April 2 8 Cin_estbd_dt 2 April 2 8 time_estbd_dt 2 April 2 8 router_estbd_dt 1 1 April 2 8 internet_estbd_dt 1 I have attached an excel workbook with an example of the full output and the mock data set. I having been messing with arrays to try and accomplish this  but I've been having trouble. Any assistance is greatly appreciated. ... View more

Hi I'm getting an error message I don't understand when I try to UPDATE a table. Here is my update query: proc sql;      update  tvtable      set  FullyLoaded_estbd_dt =      max(HBO_estbd_dt,          Cinemax_estbd_dt,          Showtime_estbd_dt,       STARZ_estbd_dt,       Encore_estbd_dt,       TMC_estbd_dt,       EPIX_estbd_dt)      where FullyLoaded_estbd_dt = '31DEC9999'd      and HBO_estbd_dt       < '31DEC9999'd      and Cinemax_estbd_dt   < '31DEC9999'd      and Showtime_estbd_dt  < '31DEC9999'd      and STARZ_estbd_dt     < '31DEC9999'd      and Encore_estbd_dt    < '31DEC9999'd      and TMC_estbd_dt       < '31DEC9999'd      and EPIX_estbd_dt      < '31DEC9999'd; quit; Here is the error message  im getting: ERROR: File WORK.tvtable cannot be updated because its encoding does not match the        session encoding or the file is in a format native to another host, such as        SOLARIS_X86_64, LINUX_X86_64, ALPHA_TRU64, LINUX_IA64. Did I not write the update query correctly? Any feedback is greatly appreciated. Thanks! ... View more