topic Re: Select only first day of each month over multiple years in SAS Enterprise Guide

Select only first day of each month over multiple years

drshashlik — Wed, 02 Sep 2020 23:14:17 GMT

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!

Re: Select only first day of each month over multiple years

Reeza — Wed, 02 Sep 2020 23:49:45 GMT

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!

Re: Select only first day of each month over multiple years

drshashlik — Thu, 03 Sep 2020 00:09:40 GMT

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.

Re: Select only first day of each month over multiple years

PGStats — Thu, 03 Sep 2020 02:55:47 GMT

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)

Re: Select only first day of each month over multiple years

ballardw — Thu, 03 Sep 2020 15:14:53 GMT

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;

Re: Select only first day of each month over multiple years

drshashlik — Sun, 06 Sep 2020 23:54:43 GMT

Thank you PG - that works perfectly.