BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lupegon34
Fluorite | Level 6

hello all,

 

I am trying to convert some dates to quarters and week days as well as calculating length of stay with them using proc sql. I have used the code before and it's worked but today it's been giving me the following notes:

 Capture.PNG

below is a sample of the code that I've been using and my formats:

 

proc sql; create table NEW as

select a.*,

      case when VAR='88'  then 1 else 0 end as CAT,

      put(qtr(VAR1), qr.) as a_qtr,     

     put(qtr(VAR2), qr.) as d_qtr,

      put(weekday(VAR1), wk.) as a_day,

      intck('DAY',VAR2,VAR1) as c_los,

from OLD a

;

quit;

 

value wk
1='SUN'
2='MON'
3='TUE'
4='WED'
5='THU'
6='FRI'
7='SAT'

 

value qr
1='Jan-Mar'
2='Apr-Jun'
3='Jul-Sep'
4='Oct-Dec'

 

What am I doing wrong??

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

@lupegon34 wrote:

They are both numeric variables and contain dates with the date9. format applied to them.

 

So for example, I have 20160103 20160104 20160105. I want to be able to create quarters and specify what day of the week it is from these dates. Then I wanted to apply a specific format


Ouch. A numeric value of 20160103 is not a SAS date value. If it was a SAS date value formatted with date9., it would display as 03jan2016.

But the raw value 20160103 would cause exactly your error message when used in a date-related function (see my other answer).

View solution in original post

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

And what does var1, var2 etc. contain?  Show some test data in the form of a datastep using the code window = {i} above post area and following this post if needed:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

The first two notes are telling you that var1 or var2 contain data which is missing or invalid, and hence a missing value is returned.

lupegon34
Fluorite | Level 6

They are both numeric variables and contain dates with the date9. format applied to them.

 

So for example, I have 20160103 20160104 20160105. I want to be able to create quarters and specify what day of the week it is from these dates. Then I wanted to apply a specific format

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Please post examples as requested.  If you really have numeric date variables and are passing those, then main check I would make is that there aren't observations with missing values, as missing value is invalid.

Kurt_Bremser
Super User

Testing always beats speculating:

data old;
var1 = today();
var2 = 123456789;
var3 = .;
run;

proc sql;
create table new1 as
select
  qtr(var1) as var1x
from old
;
create table new2 as
select
  qtr(var2) as var2x
from old
;
create table new3 as
select
  qtr(var3) as var3x
from old
;
quit;

Log from that:

24         data old;
25         var1 = today();
26         var2 = 123456789;
27         var3 = .;
28         run;

NOTE: The data set WORK.OLD has 1 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
      

29         
30         proc sql;
31         create table new1 as
32         select
33           qtr(var1) as var1x
34         from old
35         ;
NOTE: Table WORK.NEW1 created, with 1 rows and 1 columns.

36         create table new2 as
37         select
38           qtr(var2) as var2x
39         from old
40         ;
NOTE: Invalid argument to function QTR. Missing values may be generated.
NOTE: Table WORK.NEW2 created, with 1 rows and 1 columns.

41         create table new3 as
42         select
43           qtr(var3) as var3x
44         from old
45         ;
2                                                          Das SAS System                             15:35 Friday, January 19, 2018

NOTE: Invalid (or missing) arguments to the QTR function have caused the function to return a missing value.
NOTE: Table WORK.NEW3 created, with 1 rows and 1 columns.

46         quit;

Note that the second value causes the same message you encountered, so you have a numeric value that is outside of the range of valid values for SAS dates. Are you sure it isn't a datetime value instead of a date?

Kurt_Bremser
Super User

@lupegon34 wrote:

They are both numeric variables and contain dates with the date9. format applied to them.

 

So for example, I have 20160103 20160104 20160105. I want to be able to create quarters and specify what day of the week it is from these dates. Then I wanted to apply a specific format


Ouch. A numeric value of 20160103 is not a SAS date value. If it was a SAS date value formatted with date9., it would display as 03jan2016.

But the raw value 20160103 would cause exactly your error message when used in a date-related function (see my other answer).

lupegon34
Fluorite | Level 6

You know, shortly after I posted that I was like "wait a minute .....". lol.

 

Thank you!

 

Apparently all the date variables had been incorrectly formatted due to my company moving over to a new database. So of course, now everything has to be reformatted. *face palm*

 

I appreciate your help!

ballardw
Super User

@lupegon34 wrote:

They are both numeric variables and contain dates with the date9. format applied to them.

 

So for example, I have 20160103 20160104 20160105. I want to be able to create quarters and specify what day of the week it is from these dates. Then I wanted to apply a specific format


If you have values that look like 20160103 what value do you get when you apply date9. format?

If you get something similar to this for YOUR values then you have simple numeric values that are not SAS date values.

1    data _null_;
2       x=20160103;
3       put x= date9.;
4    run;

x=*********

 

The above ****** is because that value is not recognizable as a date to SAS. The current range of values that the date functions will accept ends at 31Dec20000 (yes year 20,000) which corresponds to a simple numeric value (number of days since 1JAN1960) of 6589335. Your 20160103 is about 135570768 days past the end of valid date values.

Likely to get an actual SAS date valued variable that will actually work you need to read that appropriately with something like

data _null_;
   x=20160103;
   sasdate= input(put(x,8.),yymmdd8.);
   put sasdate date9.;

run;

And the formats or functions will work on the sasdate variable constructed as above.

 

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
  • 9 replies
  • 2805 views
  • 1 like
  • 4 in conversation