Select only first day of each month over multiple years

[drshashlik]

Hi,

I need to select the first day of each month for multiple years. I have daily admissions for 10 years.  I need this collapsed into one row for the first date of each month over the ten years.  Ultimately, I need only 120 rows rather than 3650 rows.

I have summed daily admissions (to hospital) for each month of each year.  This gives me one column with the total admissions by month/year, but a row for each day of that month.  When I try proc sort with no dupkey by month(date) it fails at the nodupkey step. 

/*Step 1 - works fine*/

proc sql;
create table WANT_1 as select *,
case when eventid>0 then count(eventid) else . end as monthly_admissions
from HAVE
group by month(evstdate);
run;

/*Step 2 - does not work fine*/

proc sort data=WANT_1 out=WANT_2 nodupkey ; by month(admission_date); run;

At step 2, I get this error message beneath "by month(admission_date)" :

ERROR 22-322: Syntax error, expecting one of the following: a name, ;, -, :, DECENDING, DESCENDING, DESENDING, _ALL_, _CHARACTER_,
_CHAR_, _NUMERIC_.

33 proc sort data=count_month out=count_month_a nodupkey ; by month(admission_date); run;
_
200
ERROR 200-322: The symbol is not recognized and will be ignored.

Can anybody help with this?  Many, many thanks!

[Reeza]

Does the log have any messages about re-merging? 

I think it should and you'll always get too many records with the select * approach. Try listing just the fields you want to keep explicitly including month. FYI - I don't think month is the correct grouping level unless you want monthly averages over the 10 years because I'm pretty sure that's what is happening. So you have a single average for all of January, not an average for January 2011, January 2012 etc.

---

@drshashlik wrote:

Hi,

I need to select the first day of each month for multiple years. I have daily admissions for 10 years.  I need this collapsed into one row for the first date of each month over the ten years.  Ultimately, I need only 120 rows rather than 3650 rows.

I have summed daily admissions (to hospital) for each month of each year.  This gives me one column with the total admissions by month/year, but a row for each day of that month.  When I try proc sort with no dupkey by month(date) it fails at the nodupkey step. 

/*Step 1 - works fine*/

proc sql;
create table WANT_1 as select *,
case when eventid>0 then count(eventid) else . end as monthly_admissions
from HAVE
group by month(evstdate);
run;

/*Step 2 - does not work fine*/

proc sort data=WANT_1 out=WANT_2 nodupkey ; by month(admission_date); run;

At step 2, I get this error message beneath "by month(admission_date)" :

ERROR 22-322: Syntax error, expecting one of the following: a name, ;, -, :, DECENDING, DESCENDING, DESENDING, _ALL_, _CHARACTER_,
_CHAR_, _NUMERIC_.

33 proc sort data=count_month out=count_month_a nodupkey ; by month(admission_date); run;
_
200
ERROR 200-322: The symbol is not recognized and will be ignored.

Can anybody help with this?  Many, many thanks!

---

[drshashlik]

Hi Reeza,
no, the log does not mention remerging. And you are correct that I am getting the same values for all Januaries, Februaries etc (which is not what I want). I tried specifying the exact variables to select (rather than select *), but that made no difference apart from reducing the number of columns.

[PGStats · Accepted solution]

You can't sort by an expression in proc sort. And you don't need to.

proc sql;
create table WANT as 
select 
	intnx("month", evstdate, 0) as monthDate format=yymmdd10.,
	count(eventId) as monthly_admissions
from HAVE
group by calculated monthDate;
run;

(untested)

[drshashlik]

Thank you PG - that works perfectly.

[ballardw]

You can't use a FUNCTION such as MONTH() in a BY statement. That is causing your error.

Collapsed how?

It might help to show some example data and what you expect the output to look like.

I might start with a guess with something like

Proc freq data=have noprint;

   where eventid>0;

   tables evstdate / out=want;

   format evstdate yymon. ;

run;