topic Re: Decrement date in SAS Programming

Decrement date

SASPhile — Thu, 18 Jul 2013 15:44:08 GMT

There is a dataset that has date columns from Jul1,2013 to Dec1,2011.

Every week I need to process data taking last 36 weeks starting from the current date.

How to use this in a loop to use in a put statement to output it to a text file alsong with Id .

i.e put @01 id dt_01JUL2013 dt_24JUN2013 dt_17Jun2013?

Re: Decrement date

jakarman — Thu, 18 Jul 2013 20:31:32 GMT

When you are asking:

I have a dataset with varying colum-names (dt_&&date&i) wanting in put statement than:

Define an array in the datastep to process all columns.

SAS(R) 9.4 Statements: Reference

The variability of all the dates can be solved by first defining macro variables with the names.

Re: Decrement date

PGStats — Thu, 18 Jul 2013 21:10:08 GMT

What are your columns called, exactly?

Re: Decrement date

SASPhile — Thu, 18 Jul 2013 21:19:27 GMT

Date columns are: dt_01Jun2013 dt_24Jun2013

Re: Decrement date

Reeza — Fri, 19 Jul 2013 00:04:54 GMT

That's a bad way to store information, but probably besides the point now.

I think you need to provide more info on your data structure. For example, if it is weekly data the 1st of June is a Saturday, but the 24 is a monday, so how are the columns names determined. I'm assuming you'd need to dynamically generate the columns you'd need to pull. Do you have to do a data step with put or can you use a proc export or tagset to export?

Re: Decrement date

PGStats — Fri, 19 Jul 2013 01:46:06 GMT

I agree with Reeza about your data structure, but anyway, here is how to get what you want, or something close to it :

proc sql;
select name into :keepCols separated by ","
from dictionary.columns
where libname="WORK" and memname="MYTEST" and
input(substr(name,4),? anydtdte9.) >= intnx("WEEK", today(), -36, "S");
create table keep36weeks as
select id, &keepCols from mytest;
quit;

Re: Decrement date

Scott_Mitchell — Fri, 19 Jul 2013 08:03:57 GMT

This assumes that your variables are in a sequential order. I used week.3 because you mentioned the specific dates 01JUL2013 24JUN2013 17JUN2013.

%LET FIRSTMTH = DT_%SYSFUNC(PUTN(%SYSFUNC(INTNX(WEEK.3,%SYSFUNC(TODAY()),-36,END)),DATE9.));

%LET LASTMTH = DT_%SYSFUNC(PUTN(%SYSFUNC(INTNX(WEEK.3,%SYSFUNC(TODAY()),-1,END)),DATE9.));

DATA WANT;

SET HAVE (KEEP = ID &FIRSTMTH. -- &LASTMTH.);

RUN;