I have the attached data set and i am trying to get the output group by weekday , hour and sum energy with in that hour , for that weekday.   any help would be appreciated .      ... View more

I have a txt file which has the data in the below format.   #FILE_HEADER# MOnth|1901124578965 #MEMBERSHIP_PERSON# 9999944999|0009999|05|ABC|F|Medium|REGN|||07/Jun/2017|Pay Pal|||N|DISTIBUTOR|CURRENT|01|Y|Annual|2017|EXISTING|01/Jan/1901 #ADDRESS_LINE# 0059999999|Mr|Test||Test||M|03/Jan/1901|009999900|Online|abc00@abc.com.au|N|N|00244444||11 ABC Street|||SUBURB|3003|SA|Australia|N|N|00999900|Email|abc00@abc.com.au|N|N|||||||Y|Y|0000000001|YZx|0000000001|ZYX|PERSON|N|0000000000||||||0000000000||||| #PAYMENT_LINE# 1234|123|35|First Last #PAYMENT_LINE# 123|123|12|First Last #MLS# 123|123 #SOC_YHC# 00099999|First||Last|01|03/Jan/1901|01/Jan/1901|30|00000|01/Jul/1901|00|C|01/Jul/1901|Y| #SOC_YHC# 00099999|First|F|Last|02|03/Jan/1901|01/Jan/1901|30|00000|01/Jul/1901|00|C|01/Jul/1901|Y| #SOC_MEMBERSHIP# Mr|First||Last|12 ABC Street|SUBURB SA 3003|||3003|C|Couples|996|PRODUCT||01/O1/1901|30.256%|225|211|145 #SIS# N #FILE_FOOTER# 1901eExtractionOptIn_0000.txt|12345|1901124578965   The headers are not in the file.The file layout is below: The data provided above is in between the file header and file footer, and there are multiple rows of data between Header and Footer . Every records starts with #MEMBERSHIP_PERSON. The end goal is to convert this data into a table (columns as below and row data as above)   #FILE_HEADER# <TAXGENERATIONMODE>|<BATCHPROCESSSTARTDATETIME> #MEMBERSHIP_PERSON# <STATEMENT ID>|<MEMBERSHIP NUMBER>|<FUND NO>|<HEALTH FUND ID>|<MEMBERSHIP STATUS>|<STATEMENT COMPLEXITY>|<MEMBERSHIP REBATE STATUS>|<REBATE TIER EFFECTIVE DATE>|<REBATE TIER>|<DATE PAID TO>|<PAYMENT METHOD>|<ORGX TYPE>|<ORGX GROUP DPT>|<REBATE TYPE>|<PERSON CATEGORY>|<PERSON STATUS>|<PERSON SUFFIX NUMBER>|<MULTIPLE ADULTS>|<STATEMENT_TYPE>|<STATEMENT _YEAR>|<STATEMEMT_FREQ>|<STATEMENT GENERATE DATE> #ADDRESS_LINE# <PERSON TITLE>|<PERSON GIVEN NAME>|<PERSON INITIAL>|<PERSON SURNAME>|<PERSON PREFERRED NAME>|<PERSON GENDER>|<PERSON DOB>|<MAILING CONTACT ID>|<MAILING CONTACT TYPE>|<MAILING CONTACT>|<ADDRESS ID>|<ADDRESSEE>|<ADDRESS LINE 1>|<ADDRESS LINE 2>|<ADDRESS LINE 3>|<CITY/TOWN>|<POSTCODE>|<STATE/PROVINCE/REGION>|<COUNTRY>|<ADDRESS UNKNOWN>|<ADDRESS INVALID>|<ONLINE NOTIFICATION CONTACT ID>|<ONLINE NOTIFICATION CONTACT TYPE>|<ONLINE NOTIFICATION CONTACT>|<ONLINE NOTIFICATION CONTACT UNKNOWN>|<ONLINE NOTIFICATION CONTACT INVALID>|<COURTESY NOTIFICATION CONTACT ID>|<COURTESY NOTIFICATION TYPE>|<COURTESY NOTIFICATION CONTACT>|<COURTESY NOTIFICATION CONTACT UNKNOWN>|<COURTESY NOTIFICATION CONTACT INVALID>|<COMBINED AUTHORITY>|<COMPANY REGISTRATION>|<COMMS CATEGORY>|<COMMS CATEGORY ENTITY>|<MEMBERSHIP STATUS>|<PERSON STATUS> #PAYMENT_LINE# <PREMIUMS PAID>|<REBATE RECEIVED>|<REBATE CODE>|<OTHER BENEFICIARY> #MLS# <TOTAL HOSPITAL DAYS>|<TOTAL HOLIDAY DAYS> #SOC_LHC# <MEMBERSHIP NUMBER>|<GIVEN>|<INITIAL>|<SURNAME>|<SUFFIX>|<DOB>|<START-DATE>|<CERT-ENT-AGE>|<TOT-ABSENT-DAYS>|<ABSENT-DAYS-AS-AT>|<LIFE-LOADING>|<CONFIRM-IND>|<ISSUE-DATE>|<PROJ-AB-DAYS>|<BABYCARE EXCESS WAIVER FLAG> #SOC_MEMBERSHIP# <TITLE>|<GIVEN>|<INITIAL>|<SURNAME>|<ADDRESS LINE 1>|<ADDRESS LINE 2>|<ADDRESS LINE 3>|<ADDRESS LINE 4>|<POSTCODE>|<MEMB-CODE>|<MEMB-TYPE LMEMTP>|<PACKAGE-CODE>|<PACKAGE-DESC>|<HOSP-CORE-CODE LHSPCC>|<COVER-DATE-FROM>|<CURR-REBATE>|<HOSP-OPT-ID>|<ANC-OPT-ID>|<NP-OPT-ID> #SIS# <HAS SIS FLAG> #FILE_FOOTER# <FILENAME>|<TOTALRECORDSCOUNT>|<BATCHPROCESSFINISHDATETIME>   Any ideas on this would be great . I am using SAS EG to get this working. ... View more

Hi team ,  I have the below tables.. First table   Member Send_date 5588644 1/04/2016 5588644 5/07/2016 5585645 1/04/2016 5585645 12/07/2016 5585646 1/04/2016 5585647 1/04/2016 5585647 10/10/2016 5585648 1/04/2016 5585649 2/04/2016 5585650 8/04/2016 5585651 1/04/2016 5585651 21/04/2016   Second Table  Member Payment_date payment_amount 5588644 11/04/2016  $                  50.00 5588644 21/04/2016  $                  60.00 5585645 11/04/2016  $                150.00 5585645 22/07/2016  $                200.00 5585646 11/04/2016  $                260.00 5585647 11/04/2016  $                270.00 5585647 23/04/2016  $                270.00 5585647 20/10/2016  $                290.00 5585648 11/04/2016  $                620.00 5585649 12/04/2016  $                750.00 5585650 18/04/2016  $                820.00 5585651 11/04/2016  $                900.00   The output table should be in the below format ..   any ideas would be great ..    Member Send_date Payment _date Payment _Amount 5588644 1/04/2016 11/04/2016 $50.00 5588644 1/04/2016 21/04/2016 $60.00 5588644 5/07/2016 5585645 1/04/2016 11/04/2016 $150.00 5585645 12/07/2016 22/07/2016 $200.00 5585646 1/04/2016 11/04/2016 $260.00 5585647 1/04/2016 11/04/2016 $270.00 5585647 1/04/2016 23/04/2016 $270.00 5585647 10/10/2016 20/10/2016 $290.00 5585648 1/04/2016 11/04/2016 $620.00 5585649 2/04/2016 12/04/2016 $750.00 5585650 8/04/2016 18/04/2016 $820.00 5585651 1/04/2016 11/04/2016 $900.00 5585651 21/04/2016 ... View more

Hi Team,  I have the below table :   membership Email_id Date 9458247 3000 1/08/2016 9458247 3006 12/08/2016 9458247 4001 18/08/2016 9458247 3000 2/09/2016 9458247 3006 14/09/2016   and i am trying for the below output:    membership 3000 3006 4001 Max(date) 9458247 Y Y Y 18/08/2016 9458247 Y Y   14/09/2016   A customer could receive three messages (one set 300,3006,4001) on a marketing campaign ,if the customer is eligible again he will receive another set of three messages next month. For each set i want to capture the max(date) , which is the last message date on that set.   any ideas would be great .. Thanks    ... View more

Hi All, I have the below table : postcode store sales rank 3000 storec 289809 1 3000 storeb 37979 2 3000 storee 17865 3 3000 Restof 9541 4 3162 store3 49700 1 3162 store1 28977 2 3162 store4 18783 3 3162 Restof 28846 4  And i am trying to get the output as below: I have used Case statement to get the below output however no luck, i have more than 1000 postcodes in this data-set, so any ideas would be great.. postcode Store1 store2 store3 store1 spend store2 spend store3 spend rest ofspend 3000 storec storeb storee $289,809.00 $ 37,979.00 $ 17,865.00 $ 9,541.00 3162 store3 store1 store4 $ 49,700.00 $ 28,977.00 $ 18,783.00 $ 28,846.00 thanks ... View more

I have the below : LP_STATUS_ID UPDT_DT CT_KEY VI 29-Dec-14 1 NOLP 21-Jan-15 1 CR 4-Nov-14 2 NOLP 14-Dec-14 2 LP 4-Jan-14 4 NOLP 5-Jul-13 4 LP 21-Jul-14 5 CR 4-Feb-14 5 NOLP 4-Mar-14 5 VI 4-Jun-14 6 NOLP 4-Nov-14 6 CR 4-Oct-14 7 ST 4-Sep-14 7 NOLP 4-Aug-14 7 VI 4-Jul-14 7 I  am trying to get the output table as below CT_KEY Changed   fromVi-NoLP Changed from CR-NOLP 1 1 2 1 Tried several ways  like first and last based on dates but no luck, any help would be great .. ... View more