Try this for a start, assumin indicator is what you want the moving 3 months average. Average3months = mean (indicator, lag(indicator), lag2(indicator)) ; NB it will calculate a one month and a 2 month average for the first 2 rows. I anticipate this will rais further questions, perhaps about grouping on an individaual. Richard ... View more

For character variables the formats and informats you mention are redundant.  They probably indicate the length of the respective variables in their tables, both of them overkill if they ar for the postcodes.  You can compare values with different lengths from each of these tables without making any further adjustments, SAS will just pad out the rest of the length (if any) with blanks.  Just do it. Richard ... View more

Have you investigated Proc Rank?  It is designed to do this job for you. Richard ... View more

In simple terms SAS has come to the end of the process for the data currently in the PDV and now will start again at the top of the data step code with the next refresh of data in the PDV. Note that statements like Format and Length, which relate to the configuration of the data, and those like Retain and Array, which relate to the configuration of the process, are not executed each time.  They only need to be used once, before the first loop through the program code. Richard ... View more

I would still expect you to get the warning WARNING: The query as specified involves ordering by an item that doesn't appear in its SELECT clause. However, that's just there to check you know what you are doing.  In this context it can be ignored. Another thought - maybe the DISTINCT was responsible for the unwanted order - SAS SQL will order output if it has to group, and you could argue DISTINCT is a form of grouping.  So try it without the DISTINCT and without the ORDER  BY. Richard ... View more

Have you tried PROC SQL NOPRINT;       SELECT DISTINCT UPCASE(name)       INTO   :ListOfVars    SEPARATED BY "# "       FROM   contents       ORDER BY varnum; QUIT; Richard ... View more

You undersold yourself on SAS skills!  Had you supplied your code in the first post we could have given you more specific advice. I would recommend you do not sort your data, because it is already in date time order and a very large dataset consumes mega resources in sorting.  Also, unless you are careful, the sort may disturb the existing order. To get distinct values of spot, and subsequently split your data Proc SQL Noprint ;      Crate table spot_list as           Select distinct spot           from test2           ;      Select distinct spot into :spotlist separated by ' '  /* space */           from spot_list ;      Select count * into :spotcount           from spot_list ; Quit ; %Let spottables  = %sysfunc (translate(&spotlist, _, / )) ; %macro Whenspots ;      %do i = 1 to &spotcount ;           When "%Scan(&Spotlist, &i)" then output %Scan(&spottables, &i) ;      %end ; %mend ; Data &spottables ;      Set test2 ;      time5min = int(timesec/300) ;      /* calculate row average - not sure i understand your requirements fully here */      ...      Format     time5min     time. ;      Select spot ;           %Whenspots ;           Otherwise ;      End ; Run ; From there you can modify my SQL code to get averages across 5 min intervals. Another SQL step (or Proc Summary with a class statement) can count your As, Bs, etc, and sum volumes, using "group by time5min, type" then followed by Proc transpose to put all the counts/sums into the same row.  A datastep using first.time5min and last.time5min (times need not be on the exact boundary) can be used for calculations specific to those rows (if required.) Here is an outline fo at least soe of what you want %Macro AnalyseSpottbl (Spottbl) ;      Proc SQL ;           Create table &Spottbl._summary as                Select  date                     ,     spot                     ,     time5min                     ,     type                     ,     'count_' || type as countname                     ,     'bidvol_' || type as bidname                     ,     'askvol_' || type as askname                     ,     count (*) as count5min                     ,     sum (bidvol) as bidvol5min                     ,     sum (askvol) as askvol5min                from test2                group by 1, 2, 3, 4, 5, 6, 7                ; Quit ; Proc transpose data = &Spottbl._summary (rename = (bidname = _name_))                     out = &Spottbl._bidvol (drop = _name_)                     ;           By date spot time5min ;           var bidvol5min ; Run ;           Proc transpose data = &Spottbl._summary (rename = (askname = _name_))                     out = &Spottbl._askvol (drop = _name_)                     ;           By date spot time5min ;           var askvol5min ; Run ; Proc transpose data = &Spottbl._summary (rename = (countname = _name_))                     out = &Spottbl._counts (drop = _name_)                     ;           By date spot time5min ;           var count5min ; Run ; Data &Spottbl._allstats ;      Merge &Spottbl._counts                &Spottbl._bidvol                &Spottbl._askvol                ;           By date spot time5min ; Run ; %Mend ; %Macro DoAnalysis (num) ;      %Do I = 1 to &num ;           %Let Spottbl = %Scan(&spottables, &i) ;           %AnalyseSpottbl (&Spottbl) ;      %End ; %Mend ; %DoAnalysis (&spotcount) ; Good luck Richard ... View more

I'm not sure I can answer all your questions but I think I can assist you part of the way. You do not need to sort your data in order to split it out. Can I assume you have used Proc Import to get the data into SAS?  Because you have no header row you probably have your table with columns called COL1, COL2, COL3 etc or something similar.  To check, use (assuming your table is called imported) Proc Contents data = imported ; run ; Note from the output whether the date and time values have been correctly imported as numbers.  In the case of dates, most probably not. Do the split first. Data eur_usd, usd_chf gbp_usd ;       Set imported ;      Rename  COL1 = datechar                     COL2 = time          /* or timechar if it is not numeric */                     COL3 = currency_pair                     /* etc */                     ;      Select COL3 ;           When 'EUR/USD' then output eur_usd ;           When 'USD/CHF' then output usd_chf ;           When 'GBP/USD' then output gbp_usd ;           Otherwise return ;      End ; Run ; Now you should have 3 tables with just the values you want. If all is well you can save much space Proc Delete data = imported ; run ; (Can always be imported from the text file) Perform the same calculations on each new table, I'll use gbp_usd as an example. Data gbp_usd_1 ;      Set gbp_usd ;      Date = input (datechar, MMDDYY.) ;      Format date MMDDYY10. ;      /* If needed      Time = input (timechar, time.) ;      Format time time. ;      */      timesec = int(time) ;      time5min = int(timesec/300) ;      Format     timesec                     time5min     time. ;      /* more stuff - your averageing goes here */      Drop     datechar /* timechar */ ; run ; Your description of the averaging has been somewhat 'lost in translation'.  I am guessing you need a row average of bid and ask, and then this values averaged over the 5 minute period.  To do this I would replace /* more stuff - your averageing goes here */ with      row_average = mean (COL5, COL6) ;     /* use the renamed values instead */ Then to get the 5 min average Proc SQL ;      Create table gbp_usd_5min_average as           Select  date                ,     time5min                ,     currency_pair                ,     MEAN (row_average)     As _5min_average           From    gbp_usd_1           Group  By     1, 2, 3           ; Quit ; Try this out and let us know whether it meets your requirement, and if not try and explain your requirements even more clearly.  If you encounter errors please copy the LOG record so we can assist. Richard ... View more