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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.