DATA Step, Macro, Functions and more

Date variable formatting in proc sql

Reply
Frequent Contributor
Posts: 138

Date variable formatting in proc sql

Hi,

I am creating a date variable in my dataset out of two other date variables, DateVar1 and DateVar2. If the person has a value for DateVar1, the new date var will take that value; if they do not, the new date var will take the value for DateVar2, like this:

data want;

format new_date_var date9.;

set have;

if DateVar1 ne . then NewDateVar=DateVar1;

else if DateVar1=. then NewDateVar=DateVar2;

run;

Now I'm trying to pull out the dates for which my categorical variable, CategVar, has a value of A, so I can see which dates fall into that category. I tried this:

proc sql;

create table which_dates as

select new_date_var from want

where CategVar='A';

quit;

It runs fine, but the date variable is just filled with asterisks rather than values, even when I insert a formatting statement:

proc sql;

create table which_dates as

select new_date_var format date9. from want

where CategVar='A';

quit;

Any help is much appreciated.

Super User
Posts: 17,831

Re: Date variable formatting in proc sql

What does your log say?

It looks like you're missing the = sign in the format statement

select new_date_var format=date9.

Frequent Contributor
Posts: 138

Re: Date variable formatting in proc sql

My log just says that the which_dates table was created.

NOTE: Table WORK.which_dates created, with 342 rows and 1 columns.

19         quit;

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.01 seconds

      cpu time            0.01 seconds

I tried the = and that didn't make a difference.

Frequent Contributor
Posts: 130

Re: Date variable formatting in proc sql

What do the values of the NewDateVar look like after your DATA step?  Also, I noticed that your NewDateVar has underscores in it in the SQL procedure and not in the DATA step (not sure if it's that way, or just in your example you gave).

Super User
Super User
Posts: 6,500

Re: Date variable formatting in proc sql

Probably because you have applied a DATE format to a DATETIME value.  When the year gets above 4 digits it is hard for the DATE9 format to work.

data _null_;

  date=date();

  datetime=datetime();

  put date= datetime= ;

  put (date datetime) (= date9.);

  put (date datetime) (= dtdate9.);

run;

date=20265 datetime=1750943816.2

date=26JUN2015 datetime=*********

date=01JAN1960 datetime=26JUN2015

Ask a Question
Discussion stats
  • 4 replies
  • 3822 views
  • 1 like
  • 4 in conversation