how to keep or specify SAS date format with SQL natural join

[WendyT]

Hello SAS folks!

When I use a natural join in SQL, the date format is lost, even if it's the same in both datasets.  Is there any easy way to keep or specify the date format without specifying columns?

tiny example:

proc sql noprint ;
create table have
as select distinct *
from sashelp.pricedata (keep=date sale) 
natural join 
sashelp.prdsal3(keep=date state) 
where date between '01jan1998'D and '30may1998'D 
  and state='Florida' 
  and sale between 300 and 350 ;
quit ;

data want ; set have ;
format date mmddyy10. ;
run ;

Ideally, I would like to have date come out as mmddyy10., so that I don't have to do an additional step to create 'want'.  I'm running SAS 9.4M5 on Windows with a remote login, if that makes any difference.

Thanks so much for any help you can give me!

Wendy T

[ballardw]

Being a bit more explicit about source and what to keep seems to work:

proc sql noprint ;
create table work.have
as select distinct a.date, a.sale,b.state
from sashelp.pricedata (keep=date sale) as a 
natural join 
sashelp.prdsal3(keep=date state) as b
where a.date between '01jan1998'D and '30may1998'D 
  and state='Florida' 
  and sale between 300 and 350 ;
quit ;

[Reeza]

If your tables are too big or you're just too lazy to list all fields (which I usually am), you can use the feedback option to have the code generated for you.

Add the FEEDBACK option to your PROC SQL statement, get the code from the log and customize it there.

[WendyT]

Ballardw and Reeza-

I try not to specify columns except when it's absolutely necessary.  Either laziness or efficiency, depending on the situation.

Ballardw, you now have me thinking that it's the * rather than the natural join that is causing the loss of format. 

Reeza, the tip on the feedback option is going to be extremely useful for me.

Thanks so much for your help!

WendyT

[ballardw]

---

@WendyT wrote:

Ballardw and Reeza-

I try not to specify columns except when it's absolutely necessary.  Either laziness or efficiency, depending on the situation.

Ballardw, you now have me thinking that it's the * rather than the natural join that is causing the loss of format. 

Reeza, the tip on the feedback option is going to be extremely useful for me.

Thanks so much for your help!

WendyT

---

My guess was actually that since Date was in both sets and you did not specify which one goes into the output that even though the format in both sets was the same that the algorithms used for the combination of Select * and Natural Join just kind of "lost" the format as neither bit was told which format to apply.

I do understand the use of * even though the diehard SQL programmers seem to shudder but I'm a SAS programmer first and Sql way down the list (my experience for SQL is mostly Proc Sql).

[WendyT]

I did a bit more reading, and found that I could add ALTER TABLE to specify a format on the date variable. I'm sure that would make the SQL diehards shudder as well. 

Any other ideas?

Thanks so much for your help!

WendyT

proc sql noprint ;
create table have
as select distinct *
from sashelp.pricedata (keep=date sale) 
natural join 
sashelp.prdsal3(keep=date state) 
where date between '01jan1998'D and '30may1998'D 
  and state='Florida' 
  and sale between 300 and 350 ;

alter table have 
modify date format=mmddyy10.  ;
quit ;

proc print data=have ; run ;