topic Re: Null Timestamp conversion macro in SAS Programming

Null Timestamp conversion macro

helannivas88 — Wed, 17 Jun 2020 16:35:59 GMT

Hi ,

I'm trying for a sas code for the conversion for data from the sas data set to have it in a txt file. If the target column data type is in timestamp , I'm changing the format of that of timestamp column as in 'YYYY-MM-DD HH:MI:SS'. To achieve this I have used the below proc format.

proc format ;
picture myfmt
other= '%Y-%0m-%0d %0H:%0M:%0s' (datatype=datetime );
run;

I dont see any problem with the above proc format. But from the source, I could see the null timestamp value in the sas data set ( as dot .) and after conversion step , the value for that timestamp column as 'ERROR' instead of dot.

So I have used one more proc format as below

proc format ;
value nmissfmt . = "."
;
run;

and this my actual code for conversion for the timestamp.

data WORK.test_1;
set WORK.test;

%do i = 1 %to &obs;

%if &&target_type_name&i=TIMESTAMP %then %do;
%if %length(&&target_col_name&i)=0 %then %do;
format &&target_col_name&i nmissfmt.;
%end;
%else %do;
format &&target_col_name&i myfmt26.6;
%end;
%end;

But still Im getting value as ERROR on that column in the final dataset ( i.e. test_1). Is there any way to overcome this issue and also instead of using length , I tried initially with missing as like below, but ended up in error.

%if missing(&&target_col_name&i)=0 %then %do;

Please advise me. Thanks in Advance.

Re: Null Timestamp conversion macro

novinosrin — Wed, 17 Jun 2020 16:40:13 GMT

low-high is perhaps what you need in the format instead of other



proc format ;
picture myfmt 
low-high= '%Y-%0m-%0d %0H:%0M:%0s' (datatype=datetime )
other=.;
run;


data want;
dt=datetime();
output;
dt=.;
output;
format dt myfmt.;
run;

Re: Null Timestamp conversion macro

ballardw — Wed, 17 Jun 2020 16:45:03 GMT

You should only need one format. And since Format is a variable property you cannot assign it conditionally.

Consider

proc format ;
picture myfmt
low - high= '%Y-%0m-%0d %0H:%0M:%0s' (datatype=datetime )
.  = '.'
;
run;

Or have any desired text instead of '.' for the display value. I would likely use a blank but if your text file wants a dot to indicate missing the use that.

The Low and High are special keywords for Proc Format values that mean the "smallest non-missing value" and and "the largest value".

Re: Null Timestamp conversion macro

helannivas88 — Thu, 18 Jun 2020 05:56:10 GMT

Thanks both!! It works perfectly.