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;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Check out this tutorial series to learn how to build your own steps in SAS Studio.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.