topic Re: Count on multiple dates! Help. in SAS Enterprise Guide

Count on multiple dates! Help.

itshere — Thu, 13 Oct 2016 15:26:06 GMT

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

Re: Count on multiple dates! Help.

Reeza — Thu, 13 Oct 2016 15:28:08 GMT

Are you coding or using the GUI in EG?

Re: Count on multiple dates! Help.

skg74_mail_umkc_edu — Thu, 13 Oct 2016 15:41:37 GMT

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.

Re: Count on multiple dates! Help.

itshere — Thu, 13 Oct 2016 15:44:42 GMT

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

Re: Count on multiple dates! Help.

itshere — Thu, 13 Oct 2016 15:47:08 GMT

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

Re: Count on multiple dates! Help.

skg74_mail_umkc_edu — Thu, 13 Oct 2016 15:49:21 GMT

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

Re: Count on multiple dates! Help.

Reeza — Thu, 13 Oct 2016 15:55:08 GMT

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.

Re: Count on multiple dates! Help.

skg74_mail_umkc_edu — Thu, 13 Oct 2016 16:07:21 GMT

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;

Re: Count on multiple dates! Help.

itshere — Thu, 13 Oct 2016 16:12:34 GMT

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;

Re: Count on multiple dates! Help.

Reeza — Thu, 13 Oct 2016 19:22:29 GMT

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;