You could use this technique; Seems to work with this case and it's one data step. *Merge dataset with itself, starting with subsequent values. This will get post values. Output if you don't have 2 trailing zeroes; data want (drop = post_value1 post_value2); merge have have (firstobs = 2 rename = value = post_value1) have (firstobs = 3 rename = value = post_value2); if sum (value, post_value1, post_value2) > 0 then output; run; ... View more

The poster was asking about which key (merging) variables had changed. In your example, no key variables have changed, only the values associated with them. ... View more

*Create an indicator of origin of 'key'; data changes; merge prevdata(in=inprev) currdata(in=incurr); by key; length change_flag $ 24; if incurr and inprev then change_flag = "Both Files --- No Change"; else if inprev then change_flag = "Previous File Only"; else if incurr then change_flag = "Current File Only"; run; *Find frequencies of origins of 'key'; proc freq data = changes; tables change_flag; run; ... View more

If you are able, you could sort in the reverse way and use the lag function to get the "reverse" lag. ... View more

If my understanding of your question is correct, this should get you 80% of the way there. I don't understand the examples you gave. You listed the 3 month sum for department 2 divided by the 3 month sum for both departments and called it average? I believe this calculation is the percentage of department 2's three-month sales. Anyway, here is the code to append the static 3 month averages to the base file:   data test; infile datalines; input Year $ Month $ Dept1N Dept2N ; return; datalines; 2014 1Jun2014 2500 2100 2014 1Jul2014 2330 2220 2014 1Aug2014 1500 2140 2014 1Sep2014 2500 2670 2014 1Oct2014 4500 4100 2014 1Nov2014 2600 7100 ; run; *Create identifiers for each 3 month segment; data want; set test; three_month_count=ceil(_n_/3); run; *Calculate static three month averages for each department; proc summary data = want nway nmiss; class three_month_count; var Dept1N Dept2N; output out = want2 ( drop = _type_ _freq_ ) mean = avg_Dept1N avg_Dept2N; run; *Merge the three month averages back to the base file; data append_averages; merge want want2; by three_month_count; *Add any calculations you need here; run; ... View more

Look into proc migrate. I had a similar issue as yours a while ago and this fixed it.   libname Source <engine> 'source-library-pathname'; libname Target base 'target-library-pathname'; proc migrate in=Source out=Target; run; Reference: http://support.sas.com/documentation/cdl/en/proc/70377/HTML/default/viewer.htm#n0twretj3hlz1pn1cqhio4akyrkp.htm ... View more

I am assuming that the smaller 500,000 observation dataset is unique at the ACCT_ID level.   The methodology is to split the larger files into 10 smaller datasets and append the above smaller dataset to each piece. To test the code, I added names to the 'small' dataset and transaction values to the 'large' dataset.   data small; input ACCT_IND $ name $; datalines; 111 jon 123 bob 124 mary 451 lisa 333 sue ; run; data large; input ACCT_IND $ sales; datalines; 111 10 111 15 111 13 123 18 123 16 123 17 124 19 124 22 451 11 451 8 451 17 451 15 333 14 333 12 333 26 333 22 333 25 ; run; *Number of records in large dataset; %let records = 17; *750000000; *750 million record dataset - Split prior to merging to gain speed when merging; data huge_dataset_0 huge_dataset_1 huge_dataset_2 huge_dataset_3 huge_dataset_4 huge_dataset_5 huge_dataset_6 huge_dataset_7 huge_dataset_8 huge_dataset_9; set large; if _n_ <= &records./10 then output huge_dataset_0; else if _n_ <= 2/10*&records. then output huge_dataset_1; else if _n_ <= 3/10*&records. then output huge_dataset_2; else if _n_ <= 4/10*&records. then output huge_dataset_3; else if _n_ <= 5/10*&records. then output huge_dataset_4; else if _n_ <= 6/10*&records. then output huge_dataset_5; else if _n_ <= 7/10*&records. then output huge_dataset_6; else if _n_ <= 8/10*&records. then output huge_dataset_7; else if _n_ <= 9/10*&records. then output huge_dataset_8; else output huge_dataset_9; run; proc sort data = small; by ACCT_IND; run; *Sort the large datasets and append the smaller dataset to each; %macro sort(i); proc sort data = huge_dataset_&i.; by ACCT_IND; run; data large_&i.; merge small ( in = a ) huge_dataset_&i. ( in = b ); by ACCT_IND; if b then output; run; %mend; %sort(i=0); %sort(i=1); %sort(i=2); %sort(i=3); %sort(i=4); %sort(i=5); %sort(i=6); %sort(i=7); %sort(i=8); %sort(i=9); *Bring the dataset back together; data huge_dataset; set large_0 large_1 large_2 large_3 large_4 large_5 large_6 large_7 large_8 large_9; run; ... View more

Adding to Kurt,   You can read in the data as a character variable and just use the length function in the data step. data l; input number $50.; number_length = length(number); cards; 54498.014 17188.12647 54545 211151.127287128718 123456789012345678.1 ; run;     ... View more