BookmarkSubscribeRSS Feed
pbravo
Calcite | Level 5

Good Afternoon,

I need to know how I can get this to work for me. Everything but this portion is not working and need to know why the error appears and how to get it to work. Thanks for the help.

TO_DATE(((TO_DATE('01-JAN-1970','DD-MON-RRRR')) + ((h.MODIFIED/86400) + (-7/24)))) as time

All code:

proc sql;

create table ACCESS_SUMMARY as

  select a.type_,s.clinic , a.code, a.appointment, a.createdate,a.date_ , s.custom1 as LOC, a.clinic, a.patient, c.chart, r.typecode,

  A.producer, datepart(A.Date_), month(datepart(A.DATE_)) as month, h.modified as modifiedtime,

  TO_DATE(((TO_DATE('01-JAN-1970','DD-MON-RRRR')) + ((h.MODIFIED/86400) + (-7/24)))) as time,

  (datepart(A.DATE_) - datepart(A.createdate)) / 7 as n_weeks,

  case when (month(datepart(A.DATE_)) in (1,2,3)) then 'Q1'

  when (month(datepart(A.DATE_)) in (4,5,6)) then 'Q2'

  when (month(datepart(A.DATE_)) in (7,8,9)) then 'Q3'

  when (month(datepart(A.DATE_)) in (10,11,12)) then 'Q4'

  else ''

  end as Quarter

  from myora.ax_appoint A,

  myora.ax_patient c,

  myora.ax_producer r,

  myora.ax_clinic S,

  myora.ax_appthis h

  where   c.patient = a.patient

  and A.APPOINTMENT = H.APPOINTMENT

  and r.producer = a.producer

  and a.clinic = s.clinic

  and a.status = 0

  and a.type_ in (2,1,0)

  and h.type_ = 0

  and r.typecode in &DENT

  and A.date_ between &TDDAY and &ENDDATE

  and a.date_ = a.createdate and

   coalesce(a.code, 'unknown') not in ('NEWAPT', 'EVAL','NEWA','NEWA INT','EVAL1','NEWPT','2CH 3U ASST')

  and a.code not in  ('NEWAPT', 'EVAL','NEWA','NEWA INT','EVAL1','NEWPT','2CH 3U ASST')

  

  order by s.custom1;

  quit;

3 REPLIES 3
art297
Opal | Level 21

You might have better luck posting the question in a forum that specializes in Oracle SQL

pbravo
Calcite | Level 5

Unfortunetly, I am working in SAS and need this to work in SAS.

Basically, I need a variable stored in seconds converted to a the date it represents, any ideas?

Reeza
Super User

It doesn't work because those are SQL functions, not SAS functions.

Find the relevant SAS functions.

A SAS datetime is defined as the number of seconds from January 1, 1960.  So exactly how your variable is stored is important.

Some functions to consider:

intnx

intck

input

put

datepart

month/year/day

timepart

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 3 replies
  • 2818 views
  • 0 likes
  • 3 in conversation