DATA Step, Macro, Functions and more

Proc SQL - Char to Date

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

Proc SQL - Char to Date

[ Edited ]

Hi,

 

I have a variable called "Date" which is currently in a CHAR format $15 and the dates are displayed as Jan-18, Feb-18 etc.

 

How do I change this to show a date format, ideally to keep the same format if possible like Jan-18, Feb-18. 

 

I have tried this but the new variable just shows a '.'

 

input(date, yymmn6.) as newdate, 


Accepted Solutions
Solution
‎03-01-2018 07:07 AM
Super User
Super User
Posts: 9,866

Re: Proc SQL - Char to Date

Missed a dot:

data have;
  date='Jan-18';
run;
  
proc sql;
  create table want as
  select input(cats('01',compress(date,"-")),date7.) as newdate format=monyy6.
  from have;
quit;

Please note above how simple it is to provide test data, and show code you are using, it saves us guessing.

View solution in original post


All Replies
Super User
Super User
Posts: 9,866

Re: Proc SQL - Char to Date

A date has to have all three components, even if you decide to format it to show less than that.  So you need to append a default day value:

input(cats('01',compress(date,"-")),date7.) as newdate format=monyy6, 
Occasional Contributor
Posts: 19

Re: Proc SQL - Char to Date

Thanks for the reply. I have tried to use this but I get a syntax error

as newdate format=monyy6,
______
22
76
ERROR 22-322: Expecting a format name.

ERROR 76-322: Syntax error, statement will be ignored.
Solution
‎03-01-2018 07:07 AM
Super User
Super User
Posts: 9,866

Re: Proc SQL - Char to Date

Missed a dot:

data have;
  date='Jan-18';
run;
  
proc sql;
  create table want as
  select input(cats('01',compress(date,"-")),date7.) as newdate format=monyy6.
  from have;
quit;

Please note above how simple it is to provide test data, and show code you are using, it saves us guessing.

Occasional Contributor
Posts: 19

Re: Proc SQL - Char to Date

Thank you RW9, that has worked. I appreciate your help once again.
☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 177 views
  • 0 likes
  • 2 in conversation