DATA Step, Macro, Functions and more

PROC SQL Date

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

PROC SQL Date

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?


Accepted Solutions
Solution
‎02-27-2018 12:03 PM
Super User
Posts: 22,874

Re: PROC SQL Date

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

 

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

View solution in original post


All Replies
Super User
Posts: 22,874

Re: PROC SQL Date

[ Edited ]

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?


 

Occasional Contributor
Posts: 18

Re: PROC SQL Date

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;

Solution
‎02-27-2018 12:03 PM
Super User
Posts: 22,874

Re: PROC SQL Date

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

 

month(input(compress(Date, '-'), monyy5.)) as MONTH, 
Super User
Posts: 9,611

Re: PROC SQL Date

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 115 views
  • 1 like
  • 3 in conversation