BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
KC_16
Fluorite | Level 6

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

4 REPLIES 4
Reeza
Super User

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
Fluorite | Level 6

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
Super User

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

 

month(input(compress(Date, '-'), monyy5.)) as MONTH, 
Kurt_Bremser
Super User
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.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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