Alex Alex - 5 months ago 22
SQL Question

SAS Datetime Proc SQL

Hello I was wondering how would you write this code in a

PROC SQL
vs the
data step
I wrote below. I am trying to reduce the code, the data is initially in a text file unfortunately the datetime when changed to a
CHAR
(import wizard) is a length of 9 vs 8(computed column) which is the default, hence why i change it in the first data step. I eventually get the results I want but I would like to see if SQL could provide a more efficient solution.

data WORK.CNE_RESI;
SET WORK.cneres_41;
FORMAT RPTDATE_2 $CHAR9.;
IF rptdate = '1/5/2015' THEN RPTDATE_2 = '1/9/2015';
ELSE IF RPTDATE_2 = "" THEN RPTDATE_2=rptdate ;
RUN;

data WORK.CNE_RESI_2;
SET WORK.CNE_RESI;
FORMAT RPTDATE_3 MMDDYY10.;
RPTDATE = input(RPTDATE_2, MMDDYY10.);
RUN;

Answer

Not sure if this is the right way to do it but I had a go.

%let olddate = 1/5/2015;
%let newdate = 1/9/2015;


proc sql;

create table WORK.CNE_RESI_2 as
select a.*,
case when rptdate = "&olddate" then "&newdate"
else rptdate
end as RPTDATE_2 format=$char9.,
input(case when rptdate = "&olddate" then "&newdate"
else rptdate
end,mmddyy10.) as RPTDATE_3 format=mmddyy10.
from WORK.cneres_41 a;
quit;

Of course if you didn't actually need the variable rptdate_2 and were just using that to change format then this should work.

proc sql;
    create table WORK.CNE_RESI_2 as
    select a.*,
    input(case when rptdate = "&olddate" then "&newdate"
          else rptdate
          end,mmddyy10.) as RPTDATE_3 format=mmddyy10.
from WORK.cneres_41 a;
quit;
Comments