topic Re: how to do do some means and other calculations for daily data in SAS Enterprise Guide

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

imanojkumar1 — Thu, 17 Nov 2016 16:37:40 GMT

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'

Please help me learning SAS. Thanks.

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

RW9 — Thu, 17 Nov 2016 16:43:35 GMT

Well (note, make sure dataset is sorted)

proc means ...;

by mc date;

...

run;

proc means ...;

by pmc date;

...

run;

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

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.

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

imanojkumar1 — Thu, 17 Nov 2016 20:58:24 GMT

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. 🙂

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

ballardw — Thu, 17 Nov 2016 23:09:46 GMT

@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.

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

imanojkumar1 — Thu, 17 Nov 2016 21:56:28 GMT

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?

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

imanojkumar1 — Thu, 17 Nov 2016 22:07:33 GMT

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));

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

imanojkumar1 — Thu, 17 Nov 2016 22:37:29 GMT

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_ET
2015    4
2016    15

But got like this:

Year    Count_of_ET
2015    2
2015    1
2015    1
2016    1
2016    2
2016    1
2016    4
2016    2
2016    2
2016    3

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

RW9 — Fri, 18 Nov 2016 09:13:45 GMT

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;