BookmarkSubscribeRSS Feed
kishoresamineni
Quartz | Level 8

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.

 

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

 

 

21 REPLIES 21
Tom
Super User Tom
Super User

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
Quartz | Level 8

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
Super User

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.

 

samp_stage.JPG
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
Quartz | Level 8

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
Super User
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
PROC Star

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
Quartz | Level 8
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
Quartz | Level 8
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
Super User

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

The Boston Area SAS Users Group is hosting free webinars!
Next up: Joe Madden & Joseph Henry present Putting Power into the Hands of the Programmer with SAS Viya Workbench on Wednesday Nov 6.
Register now at https://www.basug.org/events.
kishoresamineni
Quartz | Level 8
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
PROC Star

Something like this?

month = put(date, monname12.);
kishoresamineni
Quartz | Level 8
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
Super User
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
Quartz | Level 8
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

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 21 replies
  • 4412 views
  • 4 likes
  • 5 in conversation