Hi KurtBremser,   I'm trying to move my task from Ms Excel to Sas, that DUP and DIFF is for checking purposes(when i was using Ms Excel, formula is =IF(A2=A3,"DUP","DIF") ), I wanted to keep that in SAS for checking purposes before fully use SAS to do my task. ... View more

Thank Reeza,   it seem not working if code appear more than 2  as per below:-   10034410067CONE - appear 3 times 10051230067PONE - appear 4 times   data have; infile cards ; input code $16.; cards; 10017290064CONE 10019220069CFCP 10019220069PONE 10022640063CLADY 10031300063CONE 10031420069CFCP 10031420069PGEN 10031420069PGEN 10031420069PONE 10031770067CONE 10034410067CONE 10034410067CONE 10034410067CONE 10034480060CONE 10044510062PGEN 10044510062PGEN 10046570064CONE 10046570064CONE 10049240061CONE 10051110061LADY 10051230067PGEN 10051230067PONE 10051230067PONE 10051230067PONE 10051230067PONE ; data want; Set have; By code; If first.code and not last.code then SGCHK='DUP'; Else sgchk='DIFF'; Run; and output is    code SGCHK 10017290064CONE DIF 10019220069CFCP DIF 10019220069PONE DIF 10022640063CLADY DIF 10031300063CONE DIF 10031420069CFCP DIF 10031420069PGEN DUP 10031420069PGEN DIF 10031420069PONE DIF 10031770067CONE DIF 10034410067CONE DUP 10034410067CONE DIF 10034410067CONE DIF 10034480060CONE DIF 10044510062PGEN DUP 10044510062PGEN DIF 10046570064CONE DUP 10046570064CONE DIF 10049240061CONE DIF 10051110061LADY DIF 10051230067PGEN DIF 10051230067PONE DUP 10051230067PONE DIF 10051230067PONE DIF 10051230067PONE DIF The output should be    code SGCHK 10017290064CONE DIF 10019220069CFCP DIF 10019220069PONE DIF 10022640063CLADY DIF 10031300063CONE DIF 10031420069CFCP DIF 10031420069PGEN DUP 10031420069PGEN DIF 10031420069PONE DIF 10031770067CONE DIF 10034410067CONE DUP 10034410067CONE DUP 10034410067CONE DIF 10034480060CONE DIF 10044510062PGEN DUP 10044510062PGEN DIF 10046570064CONE DUP 10046570064CONE DIF 10049240061CONE DIF 10051110061LADY DIF 10051230067PGEN DIF 10051230067PONE DUP 10051230067PONE DUP 10051230067PONE DUP 10051230067PONE DIF ... View more

look like i need to put more time and effort on proc sql. thank you so much, Jag ... View more

Jag, this sql code work like charm, really thank you for your advice. However, i'm just new to SAS, didnt know much about SQL, do you mind to share the logical behind this code, what the process to sort this out. ... View more

Thank you so much Jag, the sql code your provide it work, one more problem i encounter, if data change to below, another 2 row data(last 2 row) added in:- 33333 | 54368 | SILVER | 33333SILVER | S | 50 33333 | 94256 | SILVER | 33333SILVER | S | 175     data have; infile cards dlm='|'; input ACC_NO SUB_ACCNO PRODUCT$ ACC_PLUS_PRODUCT :$20. CATEGORY $ AMT; cards; 11111 | 12345 | GOLD | 11111GOLD | B | 50 11111 | 24895 | BLACK | 11111BLACK | B | 25 11111 | 24445 | BLACK | 11111BLACK | B | 75 11111 | 27894 | SILVER | 11111SILVER | B | 140 11111 | 23456 | SILVER | 11111SILVER | S | 70 11111 | 86475 | GOLD | 11111GOLD | S | 130 22222 | 34567 | SILVER | 22222SILVER | B | 100 22222 | 12458 | GOLD | 22222GOLD | B | 50 22222 | 12557 | GOLD | 22222GOLD | S | 100 33333 | 23785 | GOLD | 33333GOLD | B | 75 33333 | 12968 | SILVER | 33333SILVER | B | 110 33333 | 12789 | SILVER | 33333SILVER | S | 110 33333 | 54368 | SILVER | 33333SILVER | S | 50 33333 | 94256 | SILVER | 33333SILVER | S | 175 ; proc sql; create table want(drop=ord sumamt amt cnt) as select *, case when cnt=1 then sum(sumamt) else amt end as sumamt2,case when category='B' then 1 else 2 end as ord from (select *, count(*) as cnt,sum(amt) as sumamt from have group by ACC_NO, PRODUCT, ACC_PLUS_PRODUCT,CATEGORY) group by ACC_NO, PRODUCT, ACC_PLUS_PRODUCT having min(ord)=ord; quit; output i get: 11111 24895 BLACK 11111BLACK B 25 11111 24445 BLACK 11111BLACK B 75 11111 12345 GOLD 11111GOLD B 180 11111 27894 SILVER 11111SILVER B 210 22222 12458 GOLD 22222GOLD B 150 22222 34567 SILVER 22222SILVER B 100 33333 23785 GOLD 33333GOLD B 75 33333 12968 SILVER 33333SILVER B 1115   instead of expected outcome: 11111 24895 BLACK 11111BLACK B 25 11111 24445 BLACK 11111BLACK B 75 11111 12345 GOLD 11111GOLD B 180 11111 27894 SILVER 11111SILVER B 210 22222 12458 GOLD 22222GOLD B 150 22222 34567 SILVER 22222SILVER B 100 33333 23785 GOLD 33333GOLD B 75 33333 12968 SILVER 33333SILVER B 445   Thank in advance       ... View more

thank Jag for your time, maybe my bad english make you misunderstood. let me elaborate more detail. BASIC will always never delete, SUPPLEMENTARY will delete off from listing and it AMT will be roll up to BASIC ACC in AMT. eg 1: 11111 | 12345 | GOLD | 11111GOLD | B | 50 11111 | 86475 | GOLD | 11111GOLD | S | 130   outcome: 11111 | 12345 | GOLD | 11111GOLD | B | 180   eg 2:(no change as no Supplementary acc under 11111BLACK) 11111 | 24895 | BLACK | 11111BLACK | B | 25 11111 | 24445 | BLACK | 11111BLACK | B | 75   outcome: 11111 | 24895 | BLACK | 11111BLACK | B | 25 11111 | 24445 | BLACK | 11111BLACK | B | 75   eg3: 11111 | 27894 | SILVER | 11111SILVER | B | 140 11111 | 23456 | SILVER | 11111SILVER | S | 70   outcome: 11111 | 27894 | SILVER | 11111SILVER | B | 210   eg4: 22222 | 34567 | SILVER | 22222SILVER | B | 100   outcome:no change as no Supplementary acc under 22222SILVER) 22222 | 34567 | SILVER | 22222SILVER | B | 100   eg5: 22222 | 12458 | GOLD | 22222GOLD | B | 50 22222 | 12557 | GOLD | 22222GOLD | S | 100   outcome: 22222 | 12458 | GOLD | 22222GOLD | B | 150   and so on....hope above clarify ... View more

Thank ballardw for your time, i actually just want to cumulative the AMT from those account with Category(S) to same Product v Category(B). ... View more

attached my code as well, but this code it cumulative the same ACC_PLUS_PRODUCT(in this case 11111BLACK) into one. i just need sup acc to be roll up and no of acc for basic no change. data have; infile datalines dlm='|'; input ACC_NO SUB_ACCNO PRODUCT $ ACC_PLUS_PRODUCT :$12. CATEGORY $ AMT; datalines; 11111 | 12345 | GOLD | 11111GOLD | B | 50 11111 | 24895 | BLACK | 11111BLACK | B | 25 11111 | 24445 | BLACK | 11111BLACK | B | 75 11111 | 27894 | SILVER | 11111SILVER | B | 140 11111 | 23456 | SILVER | 11111SILVER | S | 70 11111 | 86475 | GOLD | 11111GOLD | S | 130 22222 | 34567 | SILVER | 22222SILVER | B | 100 22222 | 12458 | GOLD | 22222GOLD | B | 50 22222 | 12557 | GOLD | 22222GOLD | S | 100 33333 | 23785 | GOLD | 33333GOLD | B | 75 33333 | 12968 | SILVER | 33333SILVER | B | 110 33333 | 12789 | SILVER | 33333SILVER | S | 110 ; proc sort data=have; by ACC_PLUS_PRODUCT DESCENDING CATEGORY; run; data want; set have; by ACC_PLUS_PRODUCT DESCENDING CATEGORY; retain SG_AMT; IF first.ACC_PLUS_PRODUCT then SG_AMT = AMT; ELSE SG_AMT = SG_AMT + AMT; IF LAST.ACC_PLUS_PRODUCT THEN OUTPUT; RUN; ... View more

thank for highlighted else i won't notice the icon.   DATA TESTING ; INFILE DATALINES MISSOVER; INPUT #1 H $ 1-2 I $ 6-9 J 13-21 K 28-37 #2 L $ 1-2 M 13-21 N 20-37 #3 O $ 1-2 P 13-21 Q 20-37 ; DATALINES; D ABC .00 Ef 23.40- 456.23 f 234.50- 10000.00- ; ... View more

Hi KurtBremser,   Attached the sas coding, please advice(let me know if i missunderstood your request). ... View more

Thank you so much Astounding, for your question, just wanted to import to SAS according to the original text format. Or maybe in future, it might change to four-digit years. ... View more

Possible to put it as 00/00/00 string since 00/00/00 is not a valid date? ... View more