Home > Mobile >  How to add Date column in using SAS' proc sql?
How to add Date column in using SAS' proc sql?

Time:12-31

I am trying to add the Today's Date column to my existing dataset. I am using the method provided in my previous question. My code is as follows:

proc sql;
    alter table data1 add today_date char label= "Today's Date" format = $8.;
    update data1 set today_date= today();
quit;

However, I get the following error:

327  proc sql;
328      alter table data1 add today_date char label= "Today's Date" format = $8.;
NOTE: Table DATA1 has been modified, with 11 columns.
329      update data1 set today_date= today();
ERROR: today_date, a character column, can only be updated with a character expression.
330  quit;

The purpose of this is to calculate the tenure in days for the opened accounts existing in the dataset. The tenure, per my understanding, can calculated using the difference between the Today's Date and when the account was opened.

Thanks

CodePudding user response:

I suspect the issue is with the fact that your Today's Date field is in the wrong format, not as date. try this code below:

proc sql;
    alter table data1 
    add todays_date date;
    update data1  
    set todays_date = today();
quit;

CodePudding user response:

Regarding

I am trying to add the Today's Date column to my existing dataset.

A data set has a fixed value for each cell (intersection of row and column). Today's date value tomorrow is yesterday's date value.

A data structure that has dynamic values is known as a view, and some columns in it can be the result of a calculation.

You might want to consider

* Create view once, use tomorrow! ;
proc sql;
    create VIEW data1_v as
    select 
      *
    , today() as today_date format=date9. label= "Today's Date"
    from
      data1
    ;

You might also want to learn more about the nature of SAS date values and SAS date formats.

  • SAS Date value - numeric, the number of days since 01JAN1960
  • SAS date format - how to render a date value (as a sequence of glyphs) for humans.
  • SAS date informat - how to interpret glyphs and convert them into a date value

You can also refer to today's date in computation by simply using the today() function and not rely on a precomposed or precalculated column in a data source.

  • Related