CODEWITHSUNDEEP
sqlsas
ZeekDSA
ZeekDSA

Reputation: 86

SAS Count function is not returning Yearwise counts

I am using the following SAS code to generate yearwise count:

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 12Oct2015 17:26 7101 Et1 1 . 2780462.00000 1
US000409 Meas 12Nov2016 17:33 7101 Et1 1 861.26 2780462.00000 1
US000409 Lath 12Oct2015 17:33 7102 Et1 1 861.6 2780462.00000 1
US01036 Meas 12Nov2016 17:26 7101 Et1 2 . 522860.00000 1
US01036 Lath 13Oct2016 17:33 7202 Et1 2 866.68 522860.00000 1
US01036 Meas 13Oct2015 17:33 7101 Et1 2 867.36 522860.00000 1
US02044 Meas 13Nov2016 17:26 7202 Et1 1 . 569298.00000 1
US02044 Lath 13Nov2015 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;

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;

unfortunately the result I am getting looks 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

I was expecting something like this:

Year    Count_of_ET
2015    4
2016    15

I don't know where I am doing the mistake.

Could you please help?

Also, just for time being forget how values for 'MV' column look like in above sample data, What I need to calculate is, 'Date'-wise difference in MVs (something like DIF function or LAG function for such calculation).

Upvotes: 1

Views: 36

Answers (1)

Nick
Nick

Reputation: 7431

The problem is that your new Year field is still a date instead of the year number - you only formatted it to look like a year.

If you instead use the YEAR() function, it will work:

PROC SQL;
   CREATE TABLE HAVE01 AS 
   SELECT t1.*,
          YEAR(t1.Date) LABEL="Year" AS Year /* < Changes made here */
   FROM HAVE t1;
QUIT;

Outputs as you expect now.

Upvotes: 2

Related Questions