topic Re: Automatic Macros with Month conditions in SAS Programming

Automatic Macros with Month conditions

kishoresamineni — Sun, 17 Nov 2019 17:52:20 GMT

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)

Re: Automatic Macros with Month conditions

Tom — Sun, 17 Nov 2019 18:15:59 GMT

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');

Re: Automatic Macros with Month conditions

kishoresamineni — Sun, 17 Nov 2019 18:23:19 GMT

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

Re: Automatic Macros with Month conditions

Reeza — Sun, 17 Nov 2019 19:32:12 GMT

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)

Re: Automatic Macros with Month conditions

kishoresamineni — Sun, 17 Nov 2019 19:40:46 GMT

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.

Re: Automatic Macros with Month conditions

Reeza — Sun, 17 Nov 2019 20:10:53 GMT

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;

Re: Automatic Macros with Month conditions

SASKiwi — Sun, 17 Nov 2019 20:50:04 GMT

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;

Re: Automatic Macros with Month conditions

kishoresamineni — Mon, 18 Nov 2019 14:46:09 GMT

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.

Re: Automatic Macros with Month conditions

kishoresamineni — Mon, 18 Nov 2019 14:47:13 GMT

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

Re: Automatic Macros with Month conditions

Quentin — Mon, 18 Nov 2019 15:53:09 GMT

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).

Re: Automatic Macros with Month conditions

kishoresamineni — Mon, 18 Nov 2019 18:20:24 GMT

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

Re: Automatic Macros with Month conditions

SASKiwi — Mon, 18 Nov 2019 19:17:18 GMT

Something like this?

month = put(date, monname12.);

Re: Automatic Macros with Month conditions

kishoresamineni — Mon, 18 Nov 2019 19:25:02 GMT

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

Re: Automatic Macros with Month conditions

Reeza — Mon, 18 Nov 2019 20:31:25 GMT

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.

Re: Automatic Macros with Month conditions

kishoresamineni — Mon, 18 Nov 2019 20:39:00 GMT

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

Re: Automatic Macros with Month conditions

Reeza — Mon, 18 Nov 2019 20:40:09 GMT

That's not clear. Is your question answered then? If not, please be very specific with what's needed now. It would hlep if you can show more data/code otherwise you're going to just get general approaches, which is fine, but it does take longer.

Re: Automatic Macros with Month conditions

kishoresamineni — Mon, 18 Nov 2019 20:46:34 GMT

Hi Reeza,

My first portion which is to pull the data for last 6 months using macros is sorted out.

Pending thing : conditions where stage is 2 or 3 and month name should be highlighted in a MONTH column by using Date column
Stage conditions might be different if it is 1 then MONTH should be blank. (As shown in attachment with results example)

Re: Automatic Macros with Month conditions

Reeza — Mon, 18 Nov 2019 20:57:57 GMT

Have you tried modifying my code?

RANGE() provides the difference between the max and min value in the row.

You need to add another ELSE IF statement (to my code) that considers if you start with 1 and end with 1 although there's a two in the middle.

Re: Automatic Macros with Month conditions

kishoresamineni — Mon, 18 Nov 2019 21:01:14 GMT

I haven't tried that piece of code... would you please elaborate that snippet

Re: Automatic Macros with Month conditions

Reeza — Mon, 18 Nov 2019 21:23:06 GMT

I'd rather you tried and then I'd be happy to help you fix it as needed.