topic How to change date in SAS PROC SQL in SAS Procedures

How to change date in SAS PROC SQL

whk2023 — Sun, 05 Nov 2023 06:00:33 GMT

Hello there,

I am trying to change the date variable (original like "16798") to something like 01/01/1990 in PROC SQL. However, below the code seems not correct.

proc sql;
    create table class_bd as
    select Birthdate format=date10.,
        UPCASE(Name)
    from
        class_birthdate
    where
       Height >= 160.0
    order by
        Birthdate desc;
quit;

This is the log:

1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 68         
 69         proc sql;
 70             create table class_bd as
 71             select Birthdate format=date10.,
 72                 UPCASE(Name)
 73             from
 74                 class_birthdate
 75             where
 76                Height >= 160.0
 77             order by
 78                 Birthdate desc;
 NOTE: Table CLASS_BD created, with 0 rows and 2 columns.
 
 79         quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.01 seconds
       user cpu time       0.01 seconds
       system cpu time     0.00 seconds
       memory              5613.40k
       OS Memory           26536.00k
       Timestamp           11/05/2023 05:59:00 AM
       Step Count                        199  Switch Count  4
       Page Faults                       0
       Page Reclaims                     603
       Page Swaps                        0
       Voluntary Context Switches        60
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 
 80         
 81         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 91

There is no error, but I got empty new table.

Re: How to change date in SAS PROC SQL

Kurt_Bremser — Sun, 05 Nov 2023 07:17:46 GMT

You have a WHERE clause which seems to filter out all observations. Look at the attributes and contents of height.

proc sql;
select max(height)
from class_birthdate;
quit;

Re: How to change date in SAS PROC SQL

ballardw — Sun, 05 Nov 2023 11:23:18 GMT

If you want the date value to appear in month, day, year order with slashes such as 01/01/1990 then the format is MMDDYY10. 16798 would be 12/28/2005. If you want that to be day, month, year then the format is DDMMYY10. 16798 would be 28/12/2005.

If you expect the formatted value of 16798 to appear as 01/01/1990 then you have a lot of manipulation to do and are missing the rules for how to do that.

There are many different date formats available, plus you can create your own. So it is a good idea to make sure that you describe using words, or at least abbreviations, of the pieces of the date to display along with an example.