On 3rd step, after log line 479 you got a message: ERROR: BY variable trans_mo is not on input data set WORK.TR_SUM.   The last dataset WORK.TR_SUM is created in the 2nd step - by SQL. trans_mo is the month used to compute temp_trans_date together with the year. This variable was not selected by SQL, but you can add it.   Do you realy want to ignore the year (trans_yr) in step 3? That can give you false results. I think you should change (log line 475) either to:    BY CH_ID temp_trans_date   -  (mainly year and month as day=01 always) or to:    BY CH_ID TRANS_YR TRANS_MO   -  (you will need to add trans_yr too to the SQL )   Check yourself before going on.   ... View more

Sorry, I don't understand what you mean by "getting rid of records I'd actually want to keep" ? Can you give a schematic example of one group input (detailed) and output expected ? ... View more

You need 2 steps:   1) compute the max spellnr per ID:     proc sql;             create table tmp as select ID, max(spellnr) as maxspellnr               from  have group by ID;     quit; 2) join the max value to the original data:     proc sql;             create table want as             select a.* , b.maxspellnr             from have as a             left join tmp as b             on a.ID = b.ID;     quit;                ... View more

Pay attemtion: variables list, while in SAS data step are seperated by space, while in SQL are seperated by comma. Error on line 462 (in log) is becase you missed comma between "temp_trans_date" and sum(debit_amt) ...   On second thought, change sort type and drop the "descending" (on 2 lines);  (The default is "ascending");   Most of other errors seem to be the result of the first one. Try it again and check, do you get expected results. ... View more

/* step 1 - importing the data into sas */   filename  trans CSV 'C:\  ...path to and file name... .CSV'; data trans;          infile trans dlm=',' truncover;          input TRANSACTION_ID                     TRANSACTION_VALUE                   USER                   MONTH                   YEAR           ;         transaction_month = mdy(month, 01, year);  /* create a date variable */ run;   /* step 2 - summarizing */ proc sql;        create table tr_sum as select        user, transaction_month        sum(transaction_value) as tr_sum        from trans        group by user, transaction_month        order by user, transaction_month descending; quit;   /* step 3 - calculating the flag */ data tr_flag;   set tr_sum;         by user transaction_month descending;         if first.user then flag=0; else         if tr_sum > lag(tr_sum) then flag = 1;         else flag=0; run;   /* step 4 - merging the flag in the original dataset */ proc sql;         create table trans_new as select         t.*, s.flag         from trans as t         left join tr_sum as s        on t.user = s.user and             t.transaction_month = s.transaction_month        order by TRANSACTION_ID, USER,  year, MONTH;    /* adapt it to your needs */ quit;   ... View more

Few steps to achieve your target: 1) Import data into sas dataset. Are your transactions already in a sas table ?      if not - what kind of file is it ? CSV / excel / other ?   2) Sum transactions value per user, each month in different variable (sum01, sum02, ...sum12).     How many months are in the data file ?     Do you have transactions of different years in same data file ?      If yes, you need a year beyond the month.      Thus can be done either by SQL or by base sas;   Th help you we need your answers to the above questions. ... View more

The message says that the dataset was created on a different platform, eg. unix vs windows. SAS recocnize the original platform and gives warning, just in case they are not completely compatible; Usually you can ignore it until you find some suspicious fenomena as text conversion or something else. ... View more

As we need concatenate input lines to create the out line - I missed a retain statement. Here is the code that might work: (NOTE - the last line(s) may be truncated as infile cards doesn't turn end of input flag)   data want (keep=out_line);       length out_line $200;  /* max length expected for a line on output */       retain out_line;       infile cards truncover;       input @2 a_line   $100.  ;       test = substr(a_line,1,7);       if test = "MgrMsg:"  then do;           out_line = a_line;           input @2 a_line  $100. ;           test =  substr(a_line,1,7);           do while (test = ' ');                out_line = catx(' ',trim(out_line), trim(a_line2);                input @2 a_line  $100. ;                test =  substr(a_line,1,7);           end;           output; out_line = a_line;       end;       else do;            out_line = a_line;            output;       end;     return; /*----------*/ cards     ... enter here your input data ...     ... add at the end a line with End Of Input indication ... run;  ... View more