DATA Step, Macro, Functions and more

Decrement date

Reply
Super Contributor
Posts: 647

Decrement date

  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?

Valued Guide
Posts: 3,208

Re: Decrement date

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. 

---->-- ja karman --<-----
Respected Advisor
Posts: 4,646

Re: Decrement date

What are your columns called, exactly?

PG
Super Contributor
Posts: 647

Re: Decrement date

Date columns are: dt_01Jun2013    dt_24Jun2013

Super User
Posts: 17,819

Re: Decrement date

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?

Respected Advisor
Posts: 4,646

Re: Decrement date

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;

  

PG

PG
Super Contributor
Posts: 297

Re: Decrement date

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;

Ask a Question
Discussion stats
  • 6 replies
  • 664 views
  • 0 likes
  • 5 in conversation