data have; infile datalines delimiter = " "; input ID_number ID_type :$2. paid_out; datalines; 1 AA 150 1 BB 200 2 CC 100 2 CC 50 2 AA 20 3 BB 10 4 BB 50 4 AA 60 5 BB 70 6 CC 90 7 AA 80 7 CC 70 7 BB 20 8 BB 100 8 CC 10 9 AA 10 9 CC 30 9 BB 30 10 BB 20 ; run; proc sort data = have; by id_number id_type; run; data want (keep = id_number id_type_combined total rename = (id_type_combined = id_type)); do until (last.id_number); set have; length id_type_combined $10.; by id_number; if first.id_number then total = paid_out; else total + paid_out; if first.id_number then id_type_combined = id_type; else id_type_combined = catx("-", id_type_combined, id_type); end; run; ID_number id_type total 1 AA-BB 350 2 AA-CC-CC 170 3 BB 10 4 AA-BB 110 5 BB 70 6 CC 90 7 AA-BB-CC 170 8 BB-CC 110 9 AA-BB-CC 70 10 BB 20 ... View more

This way gets rid of those pesky blanks that occur:   data want; var = "&x."; do i = 1 to countw(var, ","); wantvar = scan(compress(var), i, ","); output; end; drop var i; run; ... View more

@PaigeMiller's code does provide that variable in the output unless I'm missing something. data have; infile datalines dlm = ',' dsd; input company :$50. year nic; datalines; 20 Microns Ltd.,2011,8 Ashapura Minechem Ltd.,2011,8 A D F Foods Ltd.,2011,10 Adani Wilmar Ltd.,2011,10 Agro Tech Foods Ltd.,2011,10 Ajanta Soya Ltd.,2011,10 Ambar Protein Inds. Ltd.,2011,10 Andrew Yule & Co. Ltd.,2011,10 Arcuttipore Tea Co. Ltd.,2011,10 Associated Alcohols & Breweries Ltd.,2011,11 Devyani International Ltd.,2011,11 Eco Friendly Food Processing Park Ltd.,2011,11 Elegant Floriculture & Agrotech (India) Ltd.,2011,11 Flex Foods Ltd.,2011,11 Freshtrop Fruits Ltd.,2011,12 Sree Jayalakshmi Autospin Ltd.,2011,12 A I Champdany Inds. Ltd.,2011,13 A K Spintex Ltd.,2011,13 A P M Industries Ltd.,2011,13 Aarvee Denims & Exports Ltd.,2011,13 Aditya Spinners Ltd.,2011,13 Alka India Ltd.,2011,13 Alok Industries Ltd.,2011,13 Alps Industries Ltd.,2011,13 ; run; proc freq data=have; tables nic/noprint out=nic_frequency; run; data want; merge have nic_frequency(keep=nic count); by nic; run; It's just named `COUNT` which is an automatic variable from `PROC FREQ`. You can rename it on the `MERGE` statement.   Please mark Paige's response as the correct one. ... View more

data have; infile datalines dsd missover; input var :$100.; datalines; xxxxx -> xxxx -> x xx -> xxxx -> xxxxxxxx xxxx -> xxx -> xxxxxxxxxxxxxxxxx ; run; data want; set have; want = strip(scan(var, -1, '->')); run;     You may not need `STRIP`. -1 gets the last in the string before a given delimiter. I specified the -> as the delimiter. There are a whole host of options in the documentation - very useful function.   https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.2/lefunctionsref/p0jshdjy2z9zdzn1h7k90u99lyq6.htm ... View more