PROC SQL Date

[KC_16]

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?

[Reeza]

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,
amount

from R.Mth;
quit;

Any idea how to do this please?

---

[KC_16]

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;

[Reeza · Accepted solution]

You don't create variables that way in SQL....see the example below

month(input(compress(Date, '-'), monyy5.)) as MONTH, 

[Kurt_Bremser]

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.