Do you need to keep the 13 after the V?  If so use the num_id line.  If you want to drop anything that has a character use the new_id line.  Run this and you'll see what I mean: data have; infile cards dsd; input Name$    ID$; cards; A,12 B,25 C,V13 D,24 ; data want; set have; new_id = input(id,8.); num_id = input(compress(id,'a','a'),8.); run; ... View more

This will get you the result you want, but based on the way you ask your question I'm not sure that you don't want to use proc format: data have; infile cards dsd; input id$ type$; cards; 1234,old 1234,new 2345,old 4567,old 4567,old 5678,new 5678,new ; proc sort data=have;by id type; data want; set have; by id; if last.id; run; ... View more

Without your code or log I can only speculate to what the error is, make sure you are naming the tables and using the reference from that table to identify what variables you want.  Notice here I name the table 'a' and 'b' and use the corresponding letters to identify the source for the variables I'm keeping. data have1; infile cards dsd; input id name phone email; cards; 1,,, 2,,, 3,,, ; data have2; infile cards dsd; input id v1 v2 v3; cards; 1,,, 2,,, 3,,, ; proc sql; create table want as select a.id,a.name,b.v1 from have1 a inner join           have2 b on a.id = b.id; ... View more

The data was added using cards only to show an example. How's this: data whatever; set static_summit; by key_hedge; if first.key_hedge then running_sum = 0; running_sum + dealt_amount; run; ... View more

Here you go, this is set up to start the running_sum as new for every new key_hedge, if that is not what you want you should remove the line that starts with 'if'. data have; infile cards dsd; format Key_hedge $14. Dealt_amount comma10.; input Key_hedge$    Dealt_amount; cards; 421240GapClose,15000000 421241GapClose,1000000 421241GapClose,1000000 421241GapClose,1000000 421241GapClose,1000000 421241GapClose,1000000 421242GapClose,1000000 421242GapClose,1000000 ; data want; format Key_hedge $14. Dealt_amount Running_sum comma10.; set have; by key_hedge; if first.key_hedge then running_sum = 0; running_sum + dealt_amount; run; ... View more

data prep; set have; format l_transaction_date date8.; retain first_transaction; by customer_id transaction_date; l_transaction_date = lag(transaction_date); if not first.customer_id then do; date_diff = transaction_date-l_transaction_date; end; if first.customer_id then first_transaction = transaction_date; if last.customer_id then last_transaction = transaction_date; if last.customer_id then first_last_diff = last_transaction-first_transaction; if 182.5 <= date_diff <= 365 then month6_diff = date_diff; if date_diff > 365 then month12_diff = date_diff; year = year(transaction_date); run; proc sql; create table want as select distinct customer_id,transaction_date,date_diff,first_last_diff,avg(date_diff) as avg_days,avg(month6_diff) as avg_6month,avg(month12_diff) as avg_12month from prep group by customer_id; ... View more

You have to explain what you mean with 6 and 12 month intervals.  I added an intck() to get the month difference and fields to get the difference but your explanation isn't very thorough about what you mean.  Take a look at the month6_diff and month12_diff fields. data have; infile cards dsd; informat customer_id $7. transaction_date date8.; format customer_id $7. transaction_date date8.; input customer_id$    transaction_date; cards; 440767,13-Jan-07 440767,5-Jan-12 440767,3-Jun-09 440767,17-Jan-12 440767,9-Apr-09 440767,10-Aug-08 1795023,10-Jul-12 1795023,11-Aug-11 1795023,30-Nov-09 1795023,9-Jan-12 1795023,20-Jan-11 1795023,10-Apr-09 1795023,2-Nov-11 1795023,8-Feb-12 1795023,17-Aug-11 1795023,19-Nov-13 3627695,29-May-06 3627695,15-May-03 3627695,23-Mar-15 3627695,18-May-06 ; proc sort data= have;by customer_id transaction_date; data prep; set have; format l_transaction_date date8.; retain first_transaction; by customer_id transaction_date; l_transaction_date = lag(transaction_date); if not first.customer_id then do; date_diff = transaction_date-l_transaction_date; month_diff = intck('month',l_transaction_date,transaction_date); end; if first.customer_id then first_transaction = transaction_date; if last.customer_id then last_transaction = transaction_date; if last.customer_id then first_last_diff = last_transaction-first_transaction; if month_diff >= 6 then do; month6_diff = transaction_date-l_transaction_date; end; if month_diff >= 12 then do; month12_diff = transaction_date-l_transaction_date; end; run; proc sql; create table want as select distinct customer_id,transaction_date,date_diff,first_last_diff,avg(date_diff) as avg_days,month_diff,month6_diff,month12_diff from prep group by customer_id; ... View more

This should be a pretty good start. data have; infile cards dsd; informat customer_id $7. transaction_date date8.; format customer_id $7. transaction_date date8.; input customer_id$    transaction_date; cards; 440767,13-Jan-07 440767,5-Jan-12 440767,3-Jun-09 440767,17-Jan-12 440767,9-Apr-09 440767,10-Aug-08 1795023,10-Jul-12 1795023,11-Aug-11 1795023,30-Nov-09 1795023,9-Jan-12 1795023,20-Jan-11 1795023,10-Apr-09 1795023,2-Nov-11 1795023,8-Feb-12 1795023,17-Aug-11 1795023,19-Nov-13 3627695,29-May-06 3627695,15-May-03 3627695,23-Mar-15 3627695,18-May-06 ; proc sort data= have;by customer_id transaction_date; data prep; set have; format l_transaction_date date8.; retain first_transaction; by customer_id transaction_date; l_transaction_date = lag(transaction_date); if not first.customer_id then do; date_diff = transaction_date-l_transaction_date; end; if first.customer_id then first_transaction = transaction_date; if last.customer_id then last_transaction = transaction_date; if last.customer_id then first_last_diff = last_transaction-first_transaction; run; proc sql; create table prep2 as select distinct customer_id,transaction_date,date_diff,first_last_diff,avg(date_diff) as avg_days from prep group by customer_id; ... View more

I think what you want to do is use the sum statement but your question isn't very clear.  If this doesn't give you the desired out put try attaching what it is you are looking for: data have; infile cards dsd; input policy$ amount date; cards; 1,50,1 1,20,3 1,30,7 1,60,9 2,120,12 2,80,14 2,100,18 ; data want; set have; sum_pay + amount; run; ... View more