Automatic Macros with Month conditions

[kishoresamineni]

Hi,

I have data something like below ID, Stage, Date

1) I need to put an automatic date/month macros to pull data only for last six months.

Expecting results to be like : (please see attachment)

Conditions that i need :

1) Need to show only six months of data in the output

2) I need an automatic macros to pull the data only for last six months based on the input table month name

3) Stage condition: If any ID in stage one for last six months then Month should be blank (e.g., 8923768)

    If ID started with 1 and jumped to stage 2 then starting month of that stage 2 should be  populated in Month column (e.g., 8923767)

    If ID started with staged 2 and moved to stage 3 then stage 3 month should be populated (e.g., 8923769)

    If any ID starts with stage 2 or 3 and in last month ends with 1 then Month column should be blank (e.g., 8923771)

[Tom]

Some of your statements are confusing. 

What do you mean by the phrase "automatic macro"? 

You mention that you want to base something (what exactly?) on "the input table month name".  What does that mean?  Have you coded some type of date into the name of a dataset?  Does the dataset not also contain and actual variable that has that date value?

If you want to select observations based on the last 6 months then just use a WHERE statement in whatever step you are using to read the data.  Depending on your definition of 6 months (is that 180 days? 182 days? 183 days? 5 fulls months just partial date for the last month? Or does in include the partial data for the 6th previous month also?

Is the cutoff from TODAY? Or some other Date?

%let cutoff=%sysfunc(today());
...
where date >= (&cutoff - 180);
where date >= (&cutoff - 182);
where date >= (&cutoff - 183);
where date >= intnx('month',&cutoff,-5,'beginning');
where date >= intnx('month',&cutoff,-6,'same');

[kishoresamineni]

Hi Tom,

Sorry for confusing with my question....

1. Input data is the data that i have as a source data

2. Even though the data might be for years, but i need to pull the data only for last six months for e.g., if i run the report on

1st or 2nd of November then i need to pull the data from May to October. and so on...for Dec 1st or 2nd then data from June to November

3. I need to put the conditions for stage based on the six months data that i have 

[Reeza]

1. Find the maximum date and store it into a macro variable - PROC SQL is good at this. 

2. Use macro variable and INTNX or INTCK within a WHERE clause to filter results to just the 6 months needed.

3. PROC TRANSPOSE will allow you reformat the data into the desired format. 

4. It seems like the 'month' should be the highest value IF it increases - use the functions on the array by checking if the range is increasing or decreasing. 

---

@kishoresamineni wrote:

Hi,

I have data something like below ID, Stage, Date

1) I need to put an automatic date/month macros to pull data only for last six months.

Expecting results to be like : (please see attachment)

Conditions that i need :

1) Need to show only six months of data in the output

2) I need an automatic macros to pull the data only for last six months based on the input table month name

3) Stage condition: If any ID in stage one for last six months then Month should be blank (e.g., 8923768)

    If ID started with 1 and jumped to stage 2 then starting month of that stage 2 should be  populated in Month column (e.g., 8923767)

    If ID started with staged 2 and moved to stage 3 then stage 3 month should be populated (e.g., 8923769)

    If any ID starts with stage 2 or 3 and in last month ends with 1 then Month column should be blank (e.g., 8923771)

---

[kishoresamineni]

Hi Reeza,

would you please help me with the conditions for Month column.

Month column values should be populated based on the stages whether it is in 2 or 3 and which month it has been started from

1 to 2 or 2 to 3 that month should come up in Month column.

[Reeza]

I believe this works for the first two portions, it won't handle the 8923771 case, but you can expand it with another IF condition before the last one.


data want;
set have;

array _month(*) month1-month6;
if range(of _month(*)) = 0 then call missing(month);
else do;
max=max(of _month(*));
index = whichn(max, of _month(*));
month = vname(_month(index));
end;

run;

[SASKiwi]

How about something like this? It will construct a WHERE statement for a 6 month period based on the Run Date you specify. I'm assuming your DATE variable is a proper SAS date. 

%let run_date = 02Nov2019;

data _null_;
  Last_Month = intnx("MONTH", "&run_date."d, -1, "END");
  call symputx('Last_Month', put(Last_Month, date9.));
  Month_Back6 = intnx("MONTH", "&run_date."d, -6, "END");
  call symputx('Month_Back6', put(Month_Back6, date9.));
run;

%put where date between "&Month_Back6."d and "&Last_Month"d;

[kishoresamineni]

Hi,

I was able to pull the data for last six month with macros...after that I am having some difficulty in putting the conditions that I am looking for.

[kishoresamineni]

Based on the stage I want to show the month name .... If they are in stage 2 then I need to show case that month name

[Quentin]

Please post your current code, as well as current output, and describe what output you want (i.e. how it would be different than your current output).

[kishoresamineni]

Hi,

I am sorry I have some real-time act numbers which
I can't post it here.

There is the stage I am in right now
ID Stage Date


Need to create a new column as MONTH and need to
populate month names into MONTH column based on stage and date

[SASKiwi]

Something like this?

month = put(date, monname12.);

[kishoresamineni]

Need to show Month name in a newly created MONTH column based on date column values and stage...
If stage 2 in last few months then starting month of stage 2 should be in MONTH column

[Reeza]

How does the code I provided work? Does it work except for the case identified?

If you can, make fake data and post your code.

[kishoresamineni]

Hi Reeza,

It did work for month part...now I could successfully pull data for last six months by using macro variables

case conditions need to be sorted