Count on multiple dates! Help.

[itshere]

Hi. 

I have a dates in the following format. 01JAN2016:12:00:00 (datetime21)

In my example I have 2 columns. One with dates and the other one with Car Models. 
I need to find out all the Nissans that were purchases in Jan, Feb, March, etc ( for the entire year). separated by month. 

Using PROC SQL;

how do i create a query to give me an output that will show 

Column 1: Months  Column 2: Car_Models

Table: January 50

February 80 

etc. 

Thanks!1

[Reeza]

Are you coding or using the GUI in EG?

[itshere]

just coding, creating a query to extract from an already existing table. 

[skg74_mail_umkc_edu]

data out;

   set in;

      month=month(date);****Use your datetime variable here and extract month using month ();

run;

 Proc sql;

      create table out1 as

        select a.month,a.carmodel

         from out as a

         group by month;

quit;

I hope this will be useful.

[itshere]

How do i tell Sas to categorize all January dates form 1 to 31 as JANUARY.. and so on for the other months..

[skg74_mail_umkc_edu]

You want to categorize based on months rite? Month function will help to extract the month value.It is a function.

[Reeza]

Proc SQL;

create table want as 

select year(datepart(datetime)) as year, 

           Month(datepart(datetime)) as month,

           Coumt(*) as count

From have

group by calculated year, calculated month;

quit;

because,you have a datetime variable first use the datepart() function to get a date and then month/year to get the month/year. 

I wasn't sure if you wanted year or not so included it but you can remove it if desired. 

[itshere]

So I did this and it worked but the results showed as MONTH column and the rows were 1-9. What does that mean? My dates vary from 2015 to 2016. I need to capture only 2016 and would need it to say Jan, feb, etc, for just 2016.. how do i include it to say Jan 2016, feb 2016, etc

proc sql;
create table months as
select Month(datepart(purchase_time) as month,
Count(*) as count
From workbook1
where model = "Nissan"
group by month, model;
quit;

[Reeza]

Key concepts:

Date vs Datetime and how to convert between them -> DATEPART()

Apply Formats to get desired format -> PUT()

Format dates -> MONYY7.

Extracting components of a date -> MONTH() /YEAR() functions

The Month() will calculate a month, but all January's will be combined. So January 2016 is the same as January 2015. 

The month function returns the month a 1-12 where 1 corresponds to January and 12 to December. 

Instead of SQL I recommend a proc means as it will accept formats so you don't have to do too much data conversion.

data have1;
set have;

date=datepart(datetime);
run;

proc means data=have N;
class date;
format date monyy7.;
var amount;
run;

Or modified SQL if you choose to stick with SQL:

proc sql;
select put(datepart(datetime), monyy7.) as Year_Char, 
           Count(*) as count
From have
where year(datepart(datetime))=2016
group by calculated year_Char;
quit;

[skg74_mail_umkc_edu · Accepted solution]

proc sql;
create table months as
select year(datepart(purchase_time) as year,Month(datepart(purchase_time) as month,
Count(*) as count
From workbook1
where model = "Nissan"
group by year, month, model
quit;