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.
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 25. Read more here about why you should contribute and what is in it for you!
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.