Data manipulation 1

[ctse]

Hi,

I have a table in sas which looks like this:

Mydate Mytime MyPrice MyAveragePrice
30Jan06 16:45:01 52.35 53.65
30Jan06 16:45:23 52.37 53.66
. . . .
. . . .
. . . .
31Jan06 09:45:01 65.41 61.44
31Jan06 09:45:01 65.41 61.44
31Jan06 09:45:02 65.41 61.44

The table is huge and the dots just mean that the table continues.


How can I:

(1) calculate a single price (i.e. MyPrice column) for each day (e.g. 30JAN06) where the price is the sum of all prices occurring on that day and get SAS to output the results in a table? (Bear in mind that there are weekends or even public holidays where the difference between days can be more than one)

(2) If the time is the same (e.g on the 31JAN06) the same transaction took place at the same time same price (eg. 09:45:01 @65.41), how can I set a rule to tell SAS to simply pick the average price (i.e set MyPrice equal to MyAveragePrice column) and collapse this to one line (i.e. for the two transactions on the 31JAN06 that took place @ 09:45:01, I only want one line in the table that gives me the date as 31JAN06, Time 09:45:01 and MyPrice as 61.44).

(3) After collapsing/eliminating all the duplicates in question (2) and setting MyPrice equal to MyAveragePrice, I want SAS to do Step (1) above for all dates.


Thanks,
c+

 

[klaz2002]

There are a few ways to do what you need. I think that you should use the SQL procedure for this task.

First you should convert your date and time columns to both a SAS date and a SAS datetime value.

Then you need to find your average price for all transaction that happen. Sales with more than one transaction will be averaged.

After you average the transaction have your code sum them.

Here is an example:

proc sql;
 create table test as
 select yourSASdate, YourSASdatetime ,avg(price) as avg_price
 from yourdata
 group by YourSASdatetime ;

 create table table2 as 
 select yourSASdate,  sum(avg_price) as total_price
 from test
 group by yourSASdate;

 quit;
proc print data=test2;
run;

There are many ways to do this. This is only one method.

Klaz

 

[ctse]

Thanks again Klaz.