Desktop productivity for business analysts and programmers

Count on multiple dates! Help.

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

Count on multiple dates! Help.

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

 


Accepted Solutions
Solution
‎10-13-2016 03:49 PM
Occasional Contributor
Posts: 11

Re: Count on multiple dates! Help.

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;

View solution in original post


All Replies
Grand Advisor
Posts: 17,327

Re: Count on multiple dates! Help.

Are you coding or using the GUI in EG?

Contributor
Posts: 26

Re: Count on multiple dates! Help.

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

Occasional Contributor
Posts: 11

Re: Count on multiple dates! Help.

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.

 

 

 

 

 

 

 

Contributor
Posts: 26

Re: Count on multiple dates! Help.

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

Re: Count on multiple dates! Help.

You want to categorize based on months rite? Month function will help to extract the month value.It is a function.
Grand Advisor
Posts: 17,327

Re: Count on multiple dates! Help.

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. 

Contributor
Posts: 26

Re: Count on multiple dates! Help.

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;
Grand Advisor
Posts: 17,327

Re: Count on multiple dates! Help.

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;
Solution
‎10-13-2016 03:49 PM
Occasional Contributor
Posts: 11

Re: Count on multiple dates! Help.

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 396 views
  • 3 likes
  • 3 in conversation