topic Re: PROC SQL Date in SAS Programming

PROC SQL Date

KC_16 — Tue, 27 Feb 2018 15:34:27 GMT

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?

Re: PROC SQL Date

Reeza — Tue, 27 Feb 2018 15:51:59 GMT

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?

Re: PROC SQL Date

KC_16 — Tue, 27 Feb 2018 16:42:00 GMT

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;

Re: PROC SQL Date

Kurt_Bremser — Tue, 27 Feb 2018 16:50:17 GMT

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.

Re: PROC SQL Date

Reeza — Tue, 27 Feb 2018 16:50:23 GMT

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

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