topic Re: Missing Dates Problems in SAS Procedures

Missing Dates Problems

AAdams — Tue, 23 Mar 2010 21:02:22 GMT

Hello,
I want to set some dates to missing. I've tried two ways, based on the documentation below.

http://support.sas.com/onlinedoc/913/getDoc/da/lrcon.hlp/a001292604.htm

First Attempt:
data Pt08;
set LDL0809;
Year = 2008;
if year(datepart(Date)) ne 2008 then Date = . and Value = .;

Second Attempt:
data Pt09;
set LDL0809;
missing X;
Year = 2009;
if year(datepart(Date)) ne 2009 then Date = X and Value = .;

What is happening is that instead of being truly missing, it seems that Date is set to the SAS date value of 0, mean it is showing up as 01JAN1960:00:00:00.

If it helps, my goal is that I have a bunch of data where I have the date (Date) and the value of the measurement taken on that date (Value). A Example of the type of data I have:

PtID Date Value
15 4/15/08 92
15 5/19/09 106
16 8/12/08 84
17 10/7/09 127

However, I need to find the percent of patients for whom a measurement was taken on a yearly basis. So I want to create "missing" dates/values for any year that the measurements were not taken, so that I can work with the data more easily. Like this:

PtID Date Value Year
15 4/15/08 92 2008
15 5/19/09 106 2009
16 8/12/08 84 2008
16 . . 2009
17 . . 2008
17 10/7/09 127 2009

But, as explained above, anytime I try to set the dates to missing, I get 01JAN1960:00:00:00.

What I am missing?!

Thanks!

Re: Missing Dates Problems

Cynthia_sas — Tue, 23 Mar 2010 21:21:18 GMT

Hi:
Your IF statement syntax is incorrect:
[pre]
if year(datepart(Date)) ne 2008 then Date = . and Value = .;
[/pre]

When you use a simple IF statement, you can only perform 1 action as a result of the THEN. So you could do this:
[pre]
if year(datepart(Date)) ne 2008 then Date = .;
if year(datepart(Date)) ne 2008 then Value = .;
[/pre]

Or you could do this:
[pre]
if year(datepart(Date)) ne 2008 then do;
Date = .;
Value = .;
end;
[/pre]

When you use a DO/END in conjunction with an IF statement, you can execute multiple statements conditionally.

The program below illustrates the wrong way and the right way to code an IF statement. In my test data, all the values for DATE are the same and I am setting variables to missing based on the value of the NAME variable.

cynthia
[pre]
data class;
set sashelp.class;
date = '15nov1950:07:35:15'dt;

if name in ('Alfred', 'William', 'Barbara') then do;
date = .;
age = .;
height = .;
weight = .;
end;
run;

proc print data=class;
title 'Using IF statement correctly';
format date datetime18.;
run;

data badif;
set sashelp.class;
date = '15nov1950:07:35:15'dt;

if name in ('Alfred', 'William', 'Barbara') then
date = . and age = .;
run;

proc print data=badif;
title 'Using IF statement incorrectly';
format date datetime18.;
run;

[/pre]

Re: Missing Dates Problems

AAdams — Tue, 23 Mar 2010 21:49:21 GMT

Thank you!

Re: Missing Dates Problems

ChrisNZ — Wed, 24 Mar 2010 02:40:36 GMT

To complete Cynthia's usually comprehensive answer:

date = .and age = . ;

is read by sas as

date = (boolean test);

specifically:

date = (. and age = .);

since age=. is false, the expression is:

(. and 0);

which yields 0

Try with

date = 1 and age ne . ;

The boolean test is true and therefore returns 1, and the output is 01JAN60:00:00:01

Re: Missing Dates Problems

Cynthia_sas — Wed, 24 Mar 2010 03:41:45 GMT

Thanks, Chris. I meant to put something about boolean expressions in the response, but apparently, the mind-to-screen interface is not yet working.

cynthia

Re: Missing Dates Problems

SUN59338 — Wed, 24 Mar 2010 19:19:34 GMT

your example data are not clear:
if each PtID will have either one OBS or two OBS and different in year of Date, then you can try this:
proc sort data= yourdata;
by PtID date;
run;
data new;
set yourdata;
by PtID date;
year=year(datepart(date));
output;
if first.date and last.date then do;
date=.;
value=.;
if year =2008 then year=2009;
else year=2008;
output;
end;

but if yourdata contains more OBS for each PtID, you may need simplify your description;

Re: Missing Dates Problems

ChrisNZ — Wed, 24 Mar 2010 21:26:16 GMT

Hi Cynthia,

Re-reading my entry, I realise reading my wording was not very good.

I meant "Cynthia's usually comprehensive answer" to mean:

- "as usual", as in the opposite of "Cynthia's unusually comprehensive answer"

not to mean:

- Cynthia forgot to give half of the answer this once

I am sure you didn't take umbrage as you seem such a nice person, but just to make it clear this was meant to be a compliment. Everyone loves and highly regards your knowledgeable and detailed replies here. 🐵

Re: Missing Dates Problems

Cynthia_sas — Thu, 25 Mar 2010 02:05:57 GMT

Chris:
Thanks for the clarification. I took your remark as a compliment, really! Thanks! As soon as I saw your post I realized that I'd -thought- of saying something about boolean expressions and had then neglected to actually -type- what I'd thought of.

And, I am happy that you like my responses....and that they're useful.

cynthia