BookmarkSubscribeRSS Feed
dropez
Calcite | Level 5
Hi there.

I'm trying to create a custom format for a datetime column. The result of aplying the format (label) should be a date value or missing in case the format value is out of range.
My problem is when the value is out of range. Instead of setting it to missing, SAS just returns the value as is, not formated.
The code is the following:

data intervals;
retain fmtname 'myformat';
length start end 8;
informat start end datetime19.;
format start end datetime19.;
set syt10 end=last;
start=lag(datetimevalue);
end=datetimevalue;
label=lag(datevalue);
if _n_^=1 then
output;
if last then do;
start=end;
end='31dec2100:23:59:59'dt;
label=datevalue;
output;
start=.;
end=.;
hlo='O';
label= . ;
output;
end;
keep start end label hlo;
run;

proc format cntlin=intervals;
run;

The procedure runs with no errors.
Let's assume that the first datetime value on the input dataset (syt10) is '31oct2010:00:00:00'dt. Basically, what i need is that every value bellow that datetime is set to missing, and the rest to be set with the datevalue of their corresponding interval. All values in the intervals are well formated, but the ones bellow aren't.

Look at this example output please:

data a;
var='23oct2010:01:01:01'dt;
varf=put(var,myformat.);
put 'format result: ' varf;
var='01nov2010:01:01:01'dt;
varf=put(var,myformat.);
put 'format result: ' varf;
run;

format result: 16E8
format result: 18566


What am i doing wrong here?

Regards in advance.
7 REPLIES 7
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
To start, your KEEP list is incomplete. Must also have FMTNAME. Suggest you unload your format using CNTLOUT= for review/verification against some "unformatted" data values in the file you are using with your PUT function. Also, with your forum post, it's more useful to paste your SAS log, not just the program -- I have to wonder what SAS format was actually created, given you have no FMTNAME variable in the KEEP list.

Scott Barry
SBBWorks, Inc.
dropez
Calcite | Level 5
My original code is a bit more complex as i'm creating several formats from the same input table so I edited some of the code to be more perceptible and forgot to edit the keep part.
I'll go for cntlout option and see if i can find the answear to my problem.

Thks.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Consider that it does help to be forthright (or include some disclaimer) rather than wasting others' time, when posting incomplete or inaccurate material. Which is exactly the reason why I would suggest SAS log (with all SAS code revealed) rather than code-piece, whether or not it's complete. Just a thought for next time.

Scott Barry
SBBWorks, Inc.
dropez
Calcite | Level 5
I'm sorry if i wasted your time in anyway. The goal in simplyfing the question was exactly the opposite, so you wouldn't have to be understanding very complex code to answear a simple question.
Next time i'll put you all code and log here, even if it takes four or five posts to do so.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
When posting content, data-volume is not usually an issue - what characters are posted is more of a concern (for truncation). This previous post helps - recommend bookmarking it for future reference:

http://support.sas.com/forums/thread.jspa?messageID=27609

Scott Barry
SBBWorks, Inc.
dropez
Calcite | Level 5
It is now bookmarked, thank you.
ArtC
Rhodochrosite | Level 12
When we ask for simplification what we need is essential code - the part that you think is giving you the problem. That helps us home in on the true issue. I suspect that there is more than one. A couple potential ones have already been identified. Here is a 'simple' format that may answer a couple of the questions.

Notice the KEEP= option and especially the use of the HLO variable to allow a nested format. Finally I included some dummy data to test the format.

[pre]data intervals(keep=fmtname start end label hlo);
retain fmtname 'myfmt';
start = '12jan2000:01:01:01'dt;
end = '24nov2005:11:12:13'dt;
label = 'datetime18.';
hlo = 'F';
output;
start=.;
end=.;
hlo='O';
label= '.' ;
output;
run;

proc format cntlin=intervals;
run;

data tryit;
value= '10jan2000:01:01:01'dt; put 'Below range ' value= datetime. value=myfmt.;
value= '10jan2004:01:01:01'dt; put 'In range ' value= datetime. value=myfmt.;
value= '10jan2006:01:01:01'dt; put 'After range ' value= datetime. value=myfmt.;
run;[/pre]

When writing a format using a control data set it is sometimes helpful to know some of the values that can be taken on by the supporting variables, such as HLO. I sometimes write a dummy format using the VALUE or INVALUE statement and then print out the data set:
[pre]
proc format ;
value testdt
low- < '01jan2011'd = 'early'
'01jan2011'd - '01jan2012'd = [date9.]
other = 'unk';
run;
proc format cntlout=testdt(where=(fmtname='TESTDT'));
run;
proc print data=testdt;
run;
[/pre]
Art

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1097 views
  • 0 likes
  • 3 in conversation