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

How to chain format and type changes in Proc SQL?

Autotelic — Tue, 06 Feb 2018 11:59:33 GMT

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
;

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

Kurt_Bremser — Tue, 06 Feb 2018 12:07:32 GMT

Try this:

create table want as
select put(purchase_date,yymmn6.) as purchase_date
from have
;

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

Autotelic — Tue, 06 Feb 2018 12:15:07 GMT

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.

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

Kurt_Bremser — Tue, 06 Feb 2018 12:48:03 GMT

@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.