Hi,
I have written a datastep using PROC SQL and have a date variable called 'Date' which is a character format $15. The variable outputs look like
Dec-17
Jan-18 etc.
I would like to create a new variable which provides the month number only but not sure how to do it.
So I would like the following if possible -
"date" variable = Dec-17 then I want "New Date" variable to display 12
"date" variable = Jan-18 then I want "New Date" variable to display 1
It doesn't matter if the year changes, I just need the month number.
Current code:
proc sql;
create table work.data_new as
select
P_usage,
Date,
amount
from R.Mth;
quit;
Any idea how to do this please?
You don't create variables that way in SQL....see the example below
month(input(compress(Date, '-'), monyy5.)) as MONTH,
1. Remove the dash using COMPRESS
2. Convert to date using INPUT()
3. Use MONTH() function to convert to a numeric month
data want;
x='Dec-17';
y = compress(x, '-');
z=input(y, monyy5.);
month_num = month(z);
answer1step = month(input(compress(x, '-'), monyy5.));
run;
proc print;run;
@KC_16 wrote:
Hi,
I have written a datastep using PROC SQL and have a date variable called 'Date' which is a character format $15. The variable outputs look like
Dec-17
Jan-18 etc.
I would like to create a new variable which provides the month number only but not sure how to do it.
So I would like the following if possible -
"date" variable = Dec-17 then I want "New Date" variable to display 12
"date" variable = Jan-18 then I want "New Date" variable to display 1
It doesn't matter if the year changes, I just need the month number.
Current code:
proc sql;
create table work.data_new as
select
P_usage,
Date,
amountfrom R.Mth;
quit;
Any idea how to do this please?
I have tried to include this within my code but can't get it to work and I don't completely understand the syntax you used to help demonstrate the fix sorry?
proc sql;
create table work.data_new as
select
P_usage,
Date,
Date = month(input(compress(Date, '-'), monyy5.)),
amount
from R.Mth;
quit;
You don't create variables that way in SQL....see the example below
month(input(compress(Date, '-'), monyy5.)) as MONTH,
new_date = month(input('01'!!substr(date,1,3)!!'20'!!substr(date,5,2),date9.));
The concatenated strings result in a "typical SAS" date notation, which can be converted to a SAS date, and from that the month() function extracts your desired value.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.