BookmarkSubscribeRSS Feed
WendyT
Pyrite | Level 9

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

 

5 REPLIES 5
ballardw
Super User

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
Super User
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
Pyrite | Level 9

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

@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
Pyrite | Level 9

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.  Smiley Wink

 

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 ;

 

 

 

 

 

 

 

 

 

 

 

 

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 1054 views
  • 6 likes
  • 3 in conversation