Hi All, I have the following code which runs fine without the where statement. But as soon as I include it, I get an error:     %let Basedate  = %sysfunc(Today(),date9.); /*todays date*/   %let PrevMonthStartdate = %sysfunc(intnx(Month, "&Basedate"d,-1,b), date9.); /*start date of the previous month*/   %let PrevMonthEnddate = %sysfunc(intnx(Month, "&Basedate"d,-1,e), date9.); /*end date of the previous month*/    %put &prevmonthstartdate;    data test; set XXX.TL_X; format date2 date9.; date2=trxn_date; run; procsql; connect to odbc (user="&mida_username." password="&mida_password." DSN="&mida_source.");        execute(  Create table LBG_REPORTING_1 as select  TRXN_DATE as TRANMONTH,              case    when CHANNELTYPE = '@SES' then 'Digital'            when CHANNELTYPE in ('@CALLCENTER','@IVR') then 'Telephony' when CHANNELTYPE is null and TRANSACTIONTYPE in ('OTP','ENROLLMENT') then 'Digi NM' else 'Other' end as CHANNEL,TRANSACTIONTYPE as MODEL, case when (TRXTYPECD = 'InternalTransfer' or TRXTYPECD like '%Me2Me%') then 1 else 0 end as ME2ME, ase when ISFILTERED = 'TRUE' then 1 else 0 end as ISFILTERED, TRXN_ID,CUSTID,AMOUNT from   (select * from XXX.TL_X /*where distinct(trunc(TRXN_DATE,'MONTH')) BETWEEN TRUNC(ADD_MONTHS(SYSDATE, -1),'Month') AND TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1)*/ where datepart(TRXN_DATE) = '&prevmonthstartdate'd )a ) by odbc;   disconnectfrom odbc;   quit; ERROR: CLI execute error: [Oracle][ODBC][Ora]ORA-00907: missing right parenthesis Above is the error I get. I'm guessing I'm missing something obvious? Thanks for your help guys. ... View more

Hi All, I have customer data that contains date joined with site details. But the problem with the dataset is incorrect account data as the mathing process has picked incorrect data. Below is a table showing the dataset that contains incorrect data and what the data needs to look like; Scenario: Customer buys a product but transfer it to someone else. The problem here is the sale has been allocated the wrong account number, the correct account number should be 100 and not 3000. I need help with code that can match the UniqueProductID as the reference but to then look at the sale date and match it to the correct record in the second table. To put a little more detail, a "uniqueproductID" that is transferred will get an account number but it must be matched to the correct account number table. I'm guessing the logic will basically look at date ranges, so account no 3000 started the warranty in June and 100 started in February. The problem is there is no standard number of days after the product has been sold but we can identify the roughly which application was sold in relation to its first usage date. AccountNo UniqueProductID SaleDate SaleID 3000 1612000 15/02/2011 ABXD1 3000 1612000 05/05/2011 AIIIU2 AccountNo UniqueProductID UsageDate 100 1612000 20/02/2011 3000 1612000 03/06/2011 I hope this is clear and unfortuantely there is no saleID against the table that contains all historical account numbers which is why the usage date must be linked into SaleDate (the range of the date in relation to the next one). Thanks ... View more

All, I have created the following array to identify the first valid segment value for a customers, here is a data example of what it does; customer account_type_201102 account_type_201103 account_type_201104 1 TBA MultiSite 2 SmallEnterprise SmallEnterprise MediumEnterprise 3 TBA TBA TBA data folder.test(compress=yes); set work.BadFlags; format account_type_acq $20.;      i=1;      array segmentarray account_type_: ; do while (account_type_acq=''); account_type_acq=segmentarray; i+1; end; run; So a variable is created (account_type_acq) to identify the first allocated segment. So in the case above, customer 1 is "TBA", 2 is "Small Enterprise" etc. The array will check every column until it hits the end, there is no array range as you can see, this is because every month, a file is created to indentify the customer segment. So for new customers that were acquired earlier, the newest file will have the segment. My question is, I want the array to skip "TBA" also and only select a value that is "SmallEnterprise"/"MediumEnterprise"/"Multisite". Currently it skips the blank values but I also want it to skip "TBA". I've tried adding an OR statement to the do while but I receive an error message "Subscript out of range". any help will be appreciated. Thanks, ... View more

hello! I would like some help on running a macro to run programs in an ordered sequence. So for example; the first program to run would be to run the date macro, the second program would run import file A and so on. Can someone give me an idea on how to do this? Thank you for your input. ... View more