BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
itshere
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
skg74_mail_umkc_edu
Obsidian | Level 7

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

9 REPLIES 9
Reeza
Super User

Are you coding or using the GUI in EG?

itshere
Obsidian | Level 7

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

skg74_mail_umkc_edu
Obsidian | Level 7

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
Obsidian | Level 7
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
Obsidian | Level 7
You want to categorize based on months rite? Month function will help to extract the month value.It is a function.
Reeza
Super User

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
Obsidian | Level 7
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
Super User

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
Obsidian | Level 7

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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