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
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;
Are you coding or using the GUI in EG?
just coding, creating a query to extract from an already existing table.
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.
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.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: