topic date difference calculation in SAS Procedures

date difference calculation

jkf91 — Fri, 26 Aug 2011 14:31:15 GMT

data cig.date_duration;

set cig.ALL;

by HHID purchase_date;

if first.HHID then do;

if first.HHID then first_date = purchase_date;

if last.HHID then last_date = purchase_date;

end;

duration = first_date - last_date + 1;

if last.HHID then output;

run;

What is wrong with my code? Basically, I want to calculate the difference between last purchase_date - first purchase_date for each household id (HHID) from a data file called cig.ALL.

With the above code, I get last_date only when it's a last_date = first_date (i.e., purchase is made on only one day).

Can someone please help?

Thanks,

date difference calculation

art297 — Fri, 26 Aug 2011 14:38:07 GMT

You have left out the chance to get the date when the last record in a group is read and I think that you reversed the way you want to do the calculation. I think you are actually trying to do something like:

data cig.date_duration;

set cig.ALL;

by HHID;

if first.HHID then first_date = purchase_date;

if last.HHID then do;

last_date = purchase_date;

duration =last_date-first_date + 1;

output;

end;

run;

date difference calculation

jkf91 — Fri, 26 Aug 2011 14:54:50 GMT

The data ALL looks like this:

HHID Purchase Date

1 1/2/2002

1 1/11/2002

1 1/13/2002

I want:

HHID Purchase Date First_Date Last_Date Duration

1 1/2/2002 1/2/2002 1/13/2002 12

date difference calculation

jkf91 — Fri, 26 Aug 2011 14:55:24 GMT

thank you, sir. what does "output;" do? I get the same output without it.

date difference calculation

jkf91 — Fri, 26 Aug 2011 14:59:19 GMT

now, it records the first_date and last_date. But, I still need to collapse it into the table in the post below...

Is there a way I can do it in the same data step without creating extra "sql" command?

many thanks!

date difference calculation

art297 — Fri, 26 Aug 2011 14:59:51 GMT

I'll answer your question but, first, I forgot to include an important line. A retain statment is needed. The code should have read:

data cig.date_duration;

set cig.ALL;

by HHID;

retain first_date;

if first.HHID then first_date = purchase_date;

if last.HHID then do;

last_date = purchase_date;

duration =last_date-first_date + 1;

output;

end;

run;

The output statement, because it is within the do loop where last.HHID is true, causes SAS to only output that one record, rather than all of the records.

date difference calculation

art297 — Fri, 26 Aug 2011 15:07:27 GMT

I'm not sure what your question is. If it is just that you only want to keep the fields you identified, you can either only keep those fields when you set the dataset, keep them in the data statement, or drop the other fields. I think the most efficient way would be to use something like:

data cig.date_duration;

set cig.ALL (keep=HHID purchase_date);

by HHID;

retain first_date;

format first_date last_date date9.;

if first.HHID then first_date = purchase_date;

if last.HHID then do;

last_date = purchase_date;

duration =last_date-first_date + 1;

purchase_date=first_date;

output;

end;

run;

date difference calculation

Ksharp — Mon, 29 Aug 2011 03:31:55 GMT

How to decide which date is PurchaseDate.

Art's code can not work?

data cig.date_duration;

set cig.ALL;

by HHID;

retain first_date ;

if first.HHID then first_date = purchase_date;

if last.HHID then do;

last_date = purchase_date;

duration =last_date-first_date + 1;

purchase_date=first_date;

output;

end;

run;

Code not test.

Ksharp