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.
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;
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;
Thanks both!! It works perfectly.
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".
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: