Thanks for the reply xia. I find that this solution works if it only looks 1 record ahead, but if the data had more purchases, how would i be able to capture whether each non preferred product has a later corresponding 'preferred' product? for example: a patient may have purchased 3 alternatives to 456. data have; input ID MC $ ServiceDate : yymmdd10. ProductID; format ServiceDate yymmdd10.; cards; 1 ABC 2014-01-01 123 1 DEF 2014-02-01 456 1 ABC 2014-03-01 222 1 DEF 2014-04-01 333 1 DEF 2014-05-01 335 1 DEF 2014-05-12 456 1 DEF 2014-05-15 334 ; run;
... View more
Hi, I have a table of claims data by ID and medical condition. ID MC ServiceDate ProductID 1 ABC 2014-01-01 123 1 DEF 2014-02-01 456 1 ABC 2014-03-01 222 1 DEF 2014-04-01 333 I also have a mapping table where ProductID is mapped to multiple 'preferred' products NP_ProductID Preferred_ProductID 123 222 123 223 123 224 456 333 456 334 456 335 etc. The objective is to understand whether a patient is switching to a 'preferred' product. I am having troubles trying to figure how to 'look up' whether a patient takes a preferred product at a later period in their data and count it as a switch. The output table I am trying to achieve is: ID MC Switch_Cnt 1 ABC 1 1 DEF 1 I think the approach is to use a hash table, but I am having troubles figuring out the logic and code Thanks in advance for your help.
... View more
I have a list of manufacturers for each category. For example: For Import, if any of the following: acura, toyota, hyundai, kia, honda, etc. For Domestic, if any of the following: ford, chevy, mercury, gm, jeep, pontiac etc.
... View more
Hi, I'm having troubles figuring out what function I should use to search a string variable for a number of words, any help greatly appreciated! I have a table of website descriptions ID SiteDesc 1 acura car dealer 2 acura used car dealer 3 toyota deals 4 deals for acura 5 chevy car sales For records 1-4 i'd like to create a category variable with value of "Import" For record 5, the category would be "domestic" I have a list of 10 manufacturers for each category, i'm not sure what the best way to create this classification by looping through the string for each manufacturer? Thanks for your help.
... View more
Thank you for the reply. I've tried the code, but am getting a 'Range overlap' error. I should probably have mentioned the Prod_Group is not a numeric if that matters. What does the hlo='H'; do?
... View more
Hi, I have a data set of transactions that I would like to look up against a Reference table. Transaction data; ID Trans_Date Prod_Group Qty 1 08Jan2014 1 5 2 08Feb2014 2 9 3 09Mar2014 3 2 ... Reference Table Prod_Group Price Effective_Date Change_Date 1 1 $25 01Jan2014 05Feb2014 1 $22 05Feb2014 . 2 $30 01Jan2014 10Jan2014 2 $25 10Jan2014 . 3 $25 01Jan2014 . The reference table generates a new record once a price change occurs. The Change_Date is recorded to the original price record and the Change_Date becomes the Effective_Date of the new record and the Change_Date of the new record is Null. I'd like to pull the Price of the transaction as of the Transaction Date. Final Table: ID Trans_Date Prod_Group Qty Price 1 08Jan2014 1 5 $25 2 08Jan2014 2 9 $30 3 09Mar2014 3 2 $25 Thank you in advance for the help!
... View more
Thank you. No, the qualify dataset is a subset of the History table for the latest month (in this case Jan). There will never be records into the future. There are many cases where a member will have a transaction across multiple product groups in the same day. When I made the change you suggested, it may pull the exact same transaction that is being evaluated, so the hash variables are equivalent to the qualified transaction. Is there away to 'skip' that transaction and look for the other transaction in the different product group?
... View more
Ah I see, logically, I agree with you, although the business rules says otherwise :smileysilly: I imagine it is in this block the logic will change to include the same day transactions? if inQ then do; rc=lastGroup.find(key:Prod_Group);/*fetch group specific*/ putlog rc=; if rc~=0 or H_Service_Date<=intnx('day',Service_Date,-&LB_Period_Days,'s') then do;/*not found or old entry*/ call missing(of H_:); rc=lastGroup.find(key:0);/*fetch global*/ if rc~=0 or H_Service_Date<=intnx('day',Service_Date,-&LB_Period_Days,'s') then do;/*not found or old entry*/ call missing(of H_:); end; end; output; end;
... View more
Hi Gergely, your solution will give me the the match on Prod_Group, where would you suggest I adjust the code to take into account transactions where there is no previous transactions within the Same product group and pull the latest transaction from ANY product group? Thanks.
... View more
wow thank you. I don't fully understand it yet, but it worked! Do you have any links you can direct me to for a deeper understanding of hash objects?
... View more
Thank you for your help on this. Member_ID is character, Prod_group is a numeric up to 14. I am currently writing the script on the first month (january) and will be running it every month after. At January 2014 the Historical dataset is 6000 records, with approx 800 records added monthly. Yes, allowed to sort historical dataset and store a copy.
... View more
I do not have the most advanced programming skills but here is the logic I am thinking of, and would love any feedback. I'm not sure how to program the per row processing from Table1 but the idea is: For each row (Row.) do: 1. From the HistoryTbl, Select * Where HistoryTbl.Member_ID = Row.Member_ID and HistoryTbl.Transaction_ID NE Row.Transaction_ID ** This will give me all transactions by that member not including the current transaction being evaluated. 2. Output LAST.Prod_Group from above to get most recent transaction across all Prod_Group for that Member_ID ** This will give me the latest transaction by Member by Prod_Group 3. Select Row.Transaction_ID merge by Prod_Group to #2 to get variables related to the SAME Prod_Group (rename the array of variables as InProd_[var_name]) ** This will give me the latest transaction in the Prod_Group for that Member. 4. From 3 merge by Member_ID from #2 to get variables related to the LAST Prod_Group (rename the array of variables as LastProd_[var_name]) ** This will give me the latest transaction in Any Prod_Group for that Member. 5. output record to a master table. END loop. Does this sound reasonable? Here's where i'm unsure of how I can create this macro inside the datastep to process each row. Thanks for your help.
... View more
Hi, I have a table of 'qualified records' for January that looks like: Table1: Member_ID Transaction_ID Service_Date Prod_Group 1 123 2014-01-05 1 1 124 2014-01-08 3 2 126 2014-01-07 2 3 128 2014-01-26 5 ... I have another table of ALL the history of transactions for each member (HistoryTbl) with all the same variables (plus a bunch more which i've suppressed in this example as I don't believe it'll be relevant to the logic). I am trying to pull in a number of variables from HistoryTbl to Table1 based on the following criteria: Look back 365 days from Service_Date of the transaction in Table1 for the Member_ID IF the Member has made a transaction in the SAME Prod_Group: grab following variables of the latest transaction in that Prod_Group from the HistoryTbl: Service_Date, Product_ID, etc. IF no transaction in the same Prod_Group: Get same variables as above, for the most recent Prod_Group transaction. I am unsure of how to approach the programming logic where essentially I am creating dynamic 'look back periods' for each transaction in Table1. Any help greatly appreciated. Thanks in advance.
... View more
