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.
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.