topic Re: How do I count the number of surveys by month for any given year? in SAS Programming

How do I count the number of surveys by month for any given year?

crankydogg — Mon, 04 Oct 2021 19:00:40 GMT

I am trying to figure out how many surveys have been completed each month so far this year. I used the following:

count=intck('survey_complete','01apr2021'd, '01may2021'd)

And received the following error:

ERROR 180-322: Statement is not valid or it is used out of proper order.

I am also unsure if this is a stand alone statement, a data step, or something else entirely.

Re: How do I count the number of surveys by month for any given year?

PaigeMiller — Mon, 04 Oct 2021 19:04:57 GMT

Your statement using INTCK can only be valid inside a data step, and must end with a semi-colon. The first argument must be a calendar interval like 'month' or 'year', it can't be 'survey_complete' which is not recognized.

Please show some sample data and explain what you are trying to do in more detail.

Re: How do I count the number of surveys by month for any given year?

Reeza — Mon, 04 Oct 2021 19:21:33 GMT

If I understand your problem correctly, you should be using PROC FREQ most likely. Assuming you have a variable named 'survey_complete', which is a variable that has the date the survey was completed in a data set called 'have' your code would look like the following. Note the usage of a format on the date so that the data is aggregated at the monthly level rather than the daily level.

proc freq data=have;
table survey_complete /missing out=summary_month;
format survey_complete yymmn6.;
run;

Re: How do I count the number of surveys by month for any given year?

crankydogg — Mon, 04 Oct 2021 19:45:56 GMT

Thank you for the information. I just realized that the survey_complete variable also has a time stamp so the information is shown as:

9/14/2021 2:21:00 PM

My ultimate goal is to be able to have the data to create a table like the following:

Month	Surveys Completed
January	3
February	5
March	10

Re: How do I count the number of surveys by month for any given year?

Reeza — Mon, 04 Oct 2021 20:12:32 GMT

That means you have a datetime variable, not a date variable.

Change the format in my code to :

DTMONYY5 then to show the dates as Mar2018.
https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/leforinforref/p0b84w2ylz4givn13sgq4umehexc.htm

If you would like a different format, in a prior step convert the datetime variable to a date variable using the DATEPART() function THEN reapply my original solution.

Re: How do I count the number of surveys by month for any given year?

crankydogg — Mon, 04 Oct 2021 20:27:05 GMT

This really helped, thanks! The table prints just fine, but the months are not merging. Please see example below. Any suggestions?

proc freq data=grace;
table survey_timestamp /missing out=summary_month;
format survey_timestamp;
run;

survey_timestamp	Frequency	Percent	Cumulative	Cumulative
Frequency	Percent
	22	52.38	22	52.38
5/24/2021	1	2.38	23	54.76
6/10/2021	1	2.38	24	57.14
6/21/2021	1	2.38	25	59.52
7/14/2021	1	2.38	26	61.9
7/15/2021	1	2.38	27	64.29
7/9/2021	1	2.38	28	66.67
8/13/2021	1	2.38	29	69.05
8/18/2021	1	2.38	30	71.43
8/20/2021	2	4.76	32	76.19
8/23/2021	1	2.38	33	78.57
8/31/2021	1	2.38	34	80.95
9/1/2021	1	2.38	35	83.33
9/13/2021	1	2.38	36	85.71
9/14/2021	1	2.38	37	88.1
9/15/2021	1	2.38	38	90.48
9/2/2021	1	2.38	39	92.86
9/8/2021	1	2.38	40	95.24
[not completed]	2	4.76	42	100

Re: How do I count the number of surveys by month for any given year?

Reeza — Mon, 04 Oct 2021 20:37:16 GMT

Look at my code and yours, specifically, where did you put the format.

Re: How do I count the number of surveys by month for any given year?

crankydogg — Mon, 04 Oct 2021 21:15:28 GMT

Hello. Below is my code for the table (I did add in your DTMONYY5 statement).

proc freq data=grace;
table survey_timestamp /missing out=summary_month;
format survey_timestamp DTMONYY5;
run;

I formatted it after the code you initially sent:

proc freq data=have;
table survey_complete /missing out=summary_month;
format survey_complete yymmn6.;
run;

I am still getting the dates separately, not combined/counted.

Re: How do I count the number of surveys by month for any given year?

PaigeMiller — Mon, 04 Oct 2021 21:26:53 GMT

At this point, you need to show us some sample data. Plaese follow these instructions. Do not attach files or provide screen captures.

Re: How do I count the number of surveys by month for any given year?

Reeza — Mon, 04 Oct 2021 22:12:30 GMT

The YYMMN6 format WILL NOT work on a datetime variable.

The DTMONYY5 format WILL work on a datetime variable.

Did you use a datetime variable or date variable? Is your variable a SAS date/datetime, ie numeric with a format attached? If its a character value that may explain what you're seeing to some degree but then you should have a note or error in the log.

Here's an example of how this works, converting months to years in this case:
https://gist.github.com/statgeek/0cae5568752959b035516d6ac07a20fb

Re: How do I count the number of surveys by month for any given year?

crankydogg — Tue, 05 Oct 2021 13:21:25 GMT

Thank you again for responding. I did use the DTMONYY5 format and it ran without an error. I appreciate your help.

On a separate note regarding the date/time, I later converted this variable to a character variable in the format of "Month ID" and was able to summarize the data based on this month-id level.

Re: How do I count the number of surveys by month for any given year?

Reeza — Tue, 05 Oct 2021 15:29:07 GMT

If your month are now character values, then the months won't sort in order if they have values like Jan2021, Feb2021 they sort alphabetically instead.