how to do do some means and other calculations for daily data

Solved
Frequent Contributor
Posts: 87

how to do do some means and other calculations for daily data

[ Edited ]

I have the following dataset and what I want to do is listed below:

``````data have;
infile cards truncover expandtabs;
input MC \$ ET \$ Date :date9. Time :time. PMC \$ PXMC \$ Site \$ Dia MV SF;
format date date8. Time HHMM.;
cards;
US000409 Meas 12Nov2016 17:26 7101 Et1 1 . 2780462.00000 1
US000409 Meas 12Nov2016 17:33 7101 Et1 1 861.26 2780462.00000 1
US000409 Lath 12Nov2016 17:33 7102 Et1 1 861.6 2780462.00000 1
US01036 Meas 12Nov2016 17:26 7101 Et1 2 . 522860.00000 1
US01036 Lath 13Nov2016 17:33 7202 Et1 2 866.68 522860.00000 1
US01036 Meas 13Nov2016 17:33 7101 Et1 2 867.36 522860.00000 1
US02044 Meas 13Nov2016 17:26 7202 Et1 1 . 569298.00000 1
US02044 Lath 13Nov2016 17:33 7202 Et1 1 865.32 569298.00000 1
US02044 Meas 14Nov2016 17:33 7202 Et1 2 865.68 569298.00000 1
US318 Lath 14Nov2016 17:26 7101 Et2 2 . 2630856.00000 1
US318 Meas 14Nov2016 17:33 7202 Et2 3 863.26 2630856.00000 1
US318 Lath 14Nov2016 17:33 7202 Et2 3 863.94 2630856.00000 1
US000409 Meas 15Nov2016 21:56 7202 Et2 3 860.98 2780462.00000 1
US000409 Meas 15Nov2016 21:56 7203 Et2 4 861.5 2780462.00000 1
US01036 Lath 16Nov2016 21:56 7101 Et2 4 866.64 522860.00000 1
US01036 Meas 16Nov2016 21:56 7202 Et2 4 867.34 522860.00000 1
US02044 Lath 17Nov2016 21:56 7203 Et2 1 865.3 569298.00000 1
US02044 Meas 17Nov2016 21:56 7204 Et2 3 865.68 569298.00000 1
US318 Lath 17Nov2016 21:56 7204 Et2 2 863.24 2630856.00000 1
;
run;``````

I want to compuet:

1. Daily Mean of 'Dia' for each 'MC'

2. For each 'PMC', step 1 above

3. For each 'MC', what is the difference between 'MV'

4. For each Year in 'Date', how many conts 'Lath' are there for each 'MC'

Accepted Solutions
Solution
‎11-18-2016 05:10 AM
Super User
Posts: 9,849

Re: how to do do some means and other calculations for daily data

Well, look at the code you have written versus the code I provided.  You will note that you both select, and group by a variable called year, which is in fact a date, applying a format does not change the underlying data, only how it is displayed.  So behind the scenes you are grouping your data based on the full data.  Why did you not use the year() function as I provided above?

Oh, and @imanojkumar1 is correct, the functions are year() not yearpart():

```proc sql;
create table WANT as
select  year(DATE) as YR,
count(ET) as RESULT
from    HAVE
group by year(DATE);
quit;```

All Replies
Super User
Posts: 9,849

Re: how to do do some means and other calculations for daily data

[ Edited ]

Well (note, make sure dataset is sorted)

1)

proc means ...;

by mc date;

...

run;

2)

proc means ...;

by pmc date;

...

run;

3)

Not sure what you mean, do you mean difference between largest, and smallest, first/last, sum them all - first?

4)

proc sql;

create table WANT as

select  yearpart(DATE) as YR,

count(ET) as RESULT

from   HAVE

group by yearpart(DATE);

quit;

The above have many examples in the SAS documentation, and on here.

Do also note, you could do all of the given problems in one datastep, use retain variables, and set your by groups, but maybe best to learn to do each bit before jumping in.

Frequent Contributor
Posts: 87

Re: how to do do some means and other calculations for daily data

Thank you so much.

in 3).  I means MV2 - MV1 i.e. current - lag value (but datewise)

in 2).  Daily Mean of 'Dia' for each 'MC' grouped by

I appreciate your advice. I just started learning SAS and miles to go.

Super User
Posts: 13,941

Re: how to do do some means and other calculations for daily data

[ Edited ]

imanojkumar1 wrote:

Thank you so much.

in 3).  I means MV2 - MV1 i.e. current - lag value (but datewise)

If you want to calculate the current record difference from the previous record for the same variable SAS provides a function DIF to do that.

However do you want that different only with the same date (from your datewise comment) or the max difference between two days, the range for a given date (maximum - minimum)  or something else? And do you what that comparison to go across ET? Is the data allowed to be sorted so that all of MC are together?

Another fundamental question is also do you expect a data set as the result or a report for people to read? It may help a bit to show what you would expect to see from your given example data.

Frequent Contributor
Posts: 87

Re: how to do do some means and other calculations for daily data

yes Sorting is possible on all the variable except for SF. Thanks for the advice.

Could you please let me know how DIF can be used here?

Frequent Contributor
Posts: 87

Re: how to do do some means and other calculations for daily data

I think is it...

``````proc sql;
create table WANT as
select  year(DATE) as YR,
count(ET) as RESULT
from   HAVE
group by year(DATE);
quit;

*For timestamp it will be year(datepart(datetimestamp));``````
Frequent Contributor
Posts: 87

Re: how to do do some means and other calculations for daily data

[ Edited ]

proc sql;
create table WANT as
select year(DATE) as YR,
count(ET) as RESULT
from HAVE
group by year(DATE);
quit;

does not work

in the result there are 19 rows and each rows has 2016 and 19 as YR and Result.

2016 19
2016 19
2016 19
2016 19
2016 19
. .
. .

Then,

I tried this:

``````PROC SQL;
CREATE TABLE HAVE01 AS
SELECT t1.*, t1.Date FORMAT=YEAR4. LABEL="Year" AS Year
FROM HAVE t1;
QUIT;

PROC SQL;
CREATE TABLE WANT AS
SELECT Year, (COUNT(ET)) AS COUNT_of_ET
FROM HAVE01
GROUP BY Year;
QUIT;``````

And expected somthing like this:

``````Year    Count_of_ET2015    4
2016    15``````

But got like this:

``````Year    Count_of_ET2015    2
2015    1
2015    1
2016    1
2016    2
2016    1
2016    4
2016    2
2016    2
2016    3``````

Solution
‎11-18-2016 05:10 AM
Super User
Posts: 9,849

Re: how to do do some means and other calculations for daily data

Well, look at the code you have written versus the code I provided.  You will note that you both select, and group by a variable called year, which is in fact a date, applying a format does not change the underlying data, only how it is displayed.  So behind the scenes you are grouping your data based on the full data.  Why did you not use the year() function as I provided above?

Oh, and @imanojkumar1 is correct, the functions are year() not yearpart():

```proc sql;
create table WANT as
select  year(DATE) as YR,
count(ET) as RESULT
from    HAVE
group by year(DATE);
quit;```
☑ This topic is solved.