date format problem

[dan999]

What am I missing to not get any results in the query?

data table1;

infile datalines;

input did pgm_id user $ dttime DATETIME24.3;

format dttime DATETIME24.3;

return;

datalines;

8 63832680 DAN 23AUG2014:19:42:55.160

2 63832680 JAN 01AUG2013:19:20:58.785

6 63832680 DAN 22AUG2013:13:57:12.506

4 63832680 DAN 13AUG2015:19:29:39.578

7 63832680 DAN 23AUG2013:15:30:00.452

9 63832680 DAN 26AUG2015:20:29:40.386

1 63832680 BOB 14AUG2014:22:24:41.894

5 63832680 DAN 16AUG2017:18:40:52.547

3 63832680 DAN 08AUG2013:19:13:00.356

;

run;

data table2 ;

     set table1;

     format year_month yymmn.;

     format new_time date9.;

     new_time=datepart(dttime);

     year_month=new_time;

run;

proc sql;

     create table temp7 as

     select *

     from table2

     where year_month = 201308

;

quit;

[Tom]

So you took a DATETIME value (number of seconds) and converted it a DATE value (number of days) and stored the same value into two variables for which you attached different display formats.  Note that both variables still have the same values, it is just how they will be displayed that is different.

You then asked to find the values that where exactly 201,308, which is March 1st in the year 2511.

If you want to find the values that fall within a specific month then either test for the range of dates, convert the date to the first of the month and test for that date or test the output of the format.

'01AUG2013'd <= year_month <= '31AUG2013'd
intnx('month',year_month,0,'b') = '01AUG2013'd
put(year_month,yymmn6.)='201308'

[dan999]

i understand what you're saying. The example you give is what I have now. I wanted to simplify it to year month. I don't really need the day. How do I do that?

[Reeza]

You shouldn't, then you lose the ability to deal with it as a date which is important when you need to determine the number of months or when it crosses years. Leaving it formatted as YYMM6 is fine, you just have to know how to specify your query.

You can also modify your WHERE clauses to be:

where year(year_month)=2018 and month(year_month) = 8

Or you could create two variables instead, one that's the year and one that's the month. If you want to do year over year analysis that's sometimes helpful. 

[dan999]

I've tried everything I can think of but I still can't get the format to a number. Here is some of what I tried.

data table2 ;

     set table1;

     format year_month year_month1 yymmn6.;

     format new_time date9.;

     new_time=datepart(dttime);

     year_month=input(new_time,6.);

     year_month1=input(put(new_time,yymmn6.),6.);

     year_month2=input(put(new_time,yymmn6.),6.);

run;

[Reeza]

Why do you want a number?

[Reeza]

Your code is correct, you're just trying to apply a date format still in your format statement.

[dan999]

Because other columns in other tables in our database are in number format.

Year_month2 isn't in the format statement and it doesn't work.

I just realized that in the proc sql that follows the data step I forgot to change year_month to year_month2. It works now. Thanks a lot Reeza.

[Tom]

If you want to create a number by adding the month number to 100 times the year number then it might be clearer if you did just that.

data table2 ;
     set table1;
     date_only = datepart(dttime);
     year_month=100*year(date_only)+month(date_only);
     format date_only date9.;
run;