Creating a macro for the first time

[new_sas_user_4]

hi everyone, 

I am new to the SAS forum and also to the world of SAS.

I have recently started using SAS as part of my daily job!! looking for some guidance and help here !!

I am trying to pull data for :

current month till today 

full prior month (everyday) because its dynamic

prior year MTD(month to date) 

Prior month MTD(month to date)

I want to create a macro which indicates the date/ date range so I dont have to do multiple pulls, instead pull all of the above data in one program..

I hope what I am asking makes sense..

Please help!!

Also, if there is any reference material for SAS macros/automations(getting a report in SAS and then publishing it via SAS) or a good book, please advise..

[art297]

You don't need a macro. You can create all four files, dynamically, with a single data step.

The first two desired files seem simple enough, but you'll have to explain what you mean by your definition of the third and fourth files.

You will need to use the today() and intnx functions.

There are all kinds of free and for pay information about SAS and about SAS macros.

However, first learn SAS before you try to take on the SAS macro language.

Art, CEO, AnalystFinder.com

[Astounding]

I don't know if this is good news or bad news, but macros are not needed here.  

Come back with a working program (with hard-coded dates), and it will be relatively easy to show you how to make it dynamic depending on the date the program is run.

[Reeza]

This is a good beginner reference. 

https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

[TomKari]

Welcome to SAS! I hope you don't find it too traumatic.

You've posted this in the Enterprise Guide forum, but you're asking about a programming construct.

Are you okay writing SAS code, or are you looking to only use the Enterprise Guide "point and click" facilities?

Tom

[new_sas_user_4]

Thank you everyone 🙂

I am new here so I am not sure if I have posted my question at the right place. I write proc sql codes for my data pulls.

Here is what I want:

data for:

1st june, 2018  till today(5th June 2018)

prior month - 1st may to 31st may 2018

prior month to date - 1st may to 5th may 2018

prior year - 1st June to 5th June 2017

These dates will keep changing as this report is required to be run daily:

So on 6th June 2018, I need to see:

1st june, 2018  till 6th June

prior month - 1st may to 31st may 2018(to be pulled again as my data is dynamic)

prior month to date - 1st may to 6th may 2018

prior year - 1st June to 6th June 2017

and so on...I hope my question is clearer now...

so I can start as:

proc sql;

create table X as select a,b,c from table_name

where date between start of month(from intnx function like art297 mentioned)  and today(),

............

Thanks again !!!

[art297 · Accepted solution]

Sounds like you want something like:

proc sql;
  create table current_month as
    select date,a,b,c
      from have
        where date between intnx('month',today(),0,'b') and today()
  ;
  create table prior_month as
    select date,a,b,c
      from have
        where date between intnx('month',today(),-1,'b') and intnx('month',today(),-1,'e')
  ;
  create table prior_month_to_date as
    select date,a,b,c
      from have
        where date between intnx('month',today(),-1,'b') and intnx('month',today(),-1,'s')
  ;
  create table prior_year as
    select date,a,b,c
      from have
        where date between intnx('month',today(),-12,'b') and intnx('month',today(),-1,'s')
  ;
quit;

Of course, can't test it without data, but I'd think that you could take it from here.

Art, CEO, AnalystFinder.com

[new_sas_user_4]

Thank you soooooooo much Art297 🙂 🙂 

[new_sas_user_4]

- For future reference, could you please suggest where should I post questions related for querying data(like this one) or is this the right place?

- Also, I have to create the above query for multiple groups of data which should be automated in SAS such that it can be sent out as a pdf in a mail (from SAS directly at say 7am everyday)....could you direct me to any reference material for this...

Thanks again 🙂

[Reeza]

---

@new_sas_user_4 wrote:

- For future reference, could you please suggest where should I post questions related for querying data(like this one) or is this the right place?

---

Post it here, under Base SAS Programming

---

@new_sas_user_4 wrote:

- Also, I have to create the above query for multiple groups of data which should be automated in SAS such that it can be sent out as a pdf in a mail (from SAS directly at say 7am everyday)....could you direct me to any reference material for this...

Before you build a macro and a big process get it working without macro code for one iteration/situation and then generalize it. I guarantee this is faster than if you go straight into macro code. 

This is a bit of a bigger task, that should be broken into steps and you can ask questions for each step. 

The PDF part is easy, just change the result type to generate a PDF report as well. Anything directed to results would be in the PDF instead. If you want a programming approach, it depends on how complex your reports get, it could be as simple as:

ods pdf file='/folders/myfolders/demo.pdf' style=meadow;

proc print data=sashelp.class;
run;


ods pdf close;

And as complex as this:

https://support.sas.com/resources/papers/proceedings15/SAS1836-2015.pdf

For the email portion:

http://support.sas.com/resources/papers/proceedings17/1060-2017.pdf

[art297]

Like @Reeza said, Base SAS Programming would have been a good choice for your original question.

However, what she didn't mention is that subsequent questions should be posted, wherever they best fit, but as a new thread. In the present case, since you're using EG and are asking about scheduling jobs, I'd post it there (i.e., right where you posted this current thread).

Art, CEO, AnalystFinder.com

[new_sas_user_4]

Thank you 🙂

[new_sas_user_4]

Also a quick question related to the above query:

Will it be possible to get the data for ALL dates using one query ?

proc sql;

create table X as select a, b,c 

from table_name

where date ..............
intnx('month',today(),0,'b') and (today()), /*CM MTD*/
intnx('month',today(),-12,'b') and intnx('month',today(),-12,'s'), /*PY MTD*/
intnx('month',today(),-1,'b') and intnx('month',today(),-1,'s'), /*PM MTD*/
intnx('month',today()-,-1,'b') and intnx('month',today(),-1,'e') /*PM start to end*/
;
quit;

Or I should pull all dates separately as directed by art297 and then join all of them?

Data all_data;

set table1 table2 table3; /*table1 table2 table3 would have the respective pulls for different time periods*/

run;

[art297]

Do you want just one table and, if so, do you want to create a variable that shows the type (and, if so, using what values to represent the four types)?

Also, would you want that file to include the records that don't match the four types?

Art, CEO, AnalystFinder.com

[new_sas_user_4]

I want one table which shows:

all the Dates , week, corresponding A, B, C (a,b,c being the columns which have data for sales, revenue etc)

I don't want the date to be distinguished as in this is CM Data , this is PM data etc 

Is this what you are asking by  "create a variable that shows the type"

I do want a column after Date which would show the week , which I think should be easy to extract just by using the week function..

Thank you!