How to chain format and type changes in Proc SQL?

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

How to chain format and type changes in Proc SQL?

[ Edited ]

I'm trying to chain format changes to go from a date in Date9. format to a character variable which will be the year-month variable of the same date. For example, starting from 05OCT2017 (date type), I want to end up with 201710.

 

I don't want do this with intermediary data steps and I want to do it in PROC SQL while avoiding subqueries as well.

It seemed natural to try the following, but this syntax isn't valid. How do I do this?

Create table want as
Select put(purchase_date format=YYMMN6., YYMMN6.)
  From have
;

 


Accepted Solutions
Solution
2 weeks ago
Super User
Posts: 8,590

Re: How to chain format and type changes in Proc SQL?

Posted in reply to Autotelic

Try this:

create table want as
select put(purchase_date,yymmn6.) as purchase_date
from have
;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
2 weeks ago
Super User
Posts: 8,590

Re: How to chain format and type changes in Proc SQL?

Posted in reply to Autotelic

Try this:

create table want as
select put(purchase_date,yymmn6.) as purchase_date
from have
;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 38

Re: How to chain format and type changes in Proc SQL?

Posted in reply to KurtBremser
Even though I don't understand it, it works.
I thought the second parameter in the put() function should be how one wants to read the first parameter, but the first parameter is Date9., so, in my mind, trying to read Date9. as YYMMN6. should yield a missing value.
Super User
Posts: 8,590

Re: How to chain format and type changes in Proc SQL?

Posted in reply to Autotelic

Autotelic wrote:
Even though I don't understand it, it works.
I thought the second parameter in the put() function should be how one wants to read the first parameter, but the first parameter is Date9., so, in my mind, trying to read Date9. as YYMMN6. should yield a missing value.

You're confusing the put() and the input() function. input() reads, while put() displays. Therefore you also need display formats for put(), but informats for input().

In your case, the date value in the original variable purchase_date is "displayed" in the yymmn6. format, and the resulting character string stored in the new variable which is also named purchase_date.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 80 views
  • 2 likes
  • 2 in conversation