topic Re: Creating a macro for the first time in SAS Enterprise Guide

Creating a macro for the first time

new_sas_user_4 — Tue, 05 Jun 2018 20:13:43 GMT

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

Re: Creating a macro for the first time

art297 — Tue, 05 Jun 2018 20:31:12 GMT

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

Re: Creating a macro for the first time

Astounding — Tue, 05 Jun 2018 20:34:03 GMT

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.

Re: Creating a macro for the first time

Reeza — Tue, 05 Jun 2018 20:45:45 GMT

This is a good beginner reference.

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

Re: Creating a macro for the first time

TomKari — Tue, 05 Jun 2018 21:31:41 GMT

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

Re: Creating a macro for the first time

new_sas_user_4 — Tue, 05 Jun 2018 23:43:33 GMT

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 !!!

Re: Creating a macro for the first time

art297 — Wed, 06 Jun 2018 01:24:48 GMT

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

Re: Creating a macro for the first time

new_sas_user_4 — Wed, 06 Jun 2018 01:54:21 GMT

Thank you soooooooo much Art297 🙂 🙂

Re: Creating a macro for the first time

new_sas_user_4 — Wed, 06 Jun 2018 01:59:53 GMT

- 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 🙂

Re: Creating a macro for the first time

Reeza — Wed, 06 Jun 2018 03:05:37 GMT

@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

Re: Creating a macro for the first time

art297 — Wed, 06 Jun 2018 03:13:34 GMT

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

Re: Creating a macro for the first time

new_sas_user_4 — Wed, 06 Jun 2018 18:30:23 GMT

Thank you 🙂

Re: Creating a macro for the first time

new_sas_user_4 — Thu, 07 Jun 2018 14:18:03 GMT

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;

Re: Creating a macro for the first time

art297 — Thu, 07 Jun 2018 14:30:23 GMT

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

Re: Creating a macro for the first time

new_sas_user_4 — Thu, 07 Jun 2018 14:37:29 GMT

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!

Re: Creating a macro for the first time

art297 — Thu, 07 Jun 2018 14:58:30 GMT

Then why not just use one sql call OR'ing the four conditions? If the week function provides what you want, you can easily add that to your select clause.

Art, CEO, AnalystFinder.com

Re: Creating a macro for the first time

new_sas_user_4 — Thu, 07 Jun 2018 15:20:11 GMT

Thanks Art297 !!

I was wondering if that would pull unnecessary data but it didn't

Thank you again!!

Re: Creating a macro for the first time

new_sas_user_4 — Fri, 08 Jun 2018 17:43:15 GMT

Hello everyone,

I am a bit stuck on a query. Below are the codes and the results are attached :

Please help!!

proc sql;
create table X as select distinct DT,CYCLE_MTH
from table_name where
DT between intnx('month',today()-4,0,'b') and (today()-4)
or DT between intnx('month',today()-4,-12,'b') and intnx('month',today()-4,-12,'s') /*PY MTD*/
and month(CYCLE_MTH)= month(today()-4)+1;
quit;

proc sql;
create table X1 as select distinct DT,CYCLE_MTH
from table_name where
DT between intnx('month',today()-4,0,'b') and (today()-4)
and month(CYCLE_MTH)= month(today()-4)+1;
quit;

proc sql;
create table X2 as select distinct DT,CYCLE_MTH
from table_name where
DT between intnx('month',today()-4,-12,'b') and intnx('month',today()-4,-12,'s') /*PY MTD*/
and month(CYCLE_MTH)= month(today()-4)+1;
quit;

I have attached the results from the 3 pulls below.

My concern is : If I am pulling data separately as in X1 and X2 , the results are as I expect them to be!

but in extract X, I get values such as DT = 01JUN2018 and CYCLE_MTH = 01JUN2018 which isn't expected.

My desired result is X1 and X2 as one single table (CYCLE_MTH 01JUN2018 should not come up.) OR X but without CYCLE_MTH 01JUN2018 .

Could this be because I am using AND and OR conditions together? or could this be a database error?

Thank you!!!

Re: Creating a macro for the first time

art297 — Fri, 08 Jun 2018 19:17:46 GMT

Always use parentheses to specify a complex where statement. Otherwise you risk getting what you asked for, rather than what you thought you asked for:

proc sql;
  create table X as select distinct DT,CYCLE_MTH
    from table_name
      where (DT between intnx('month',today()-4,0,'b') and (today()-4) 
        or DT between intnx('month',today()-4,-12,'b') and intnx('month',today()-4,-12,'s')) /*PY MTD*/
      and month(CYCLE_MTH)= month(today()-4)+1
  ;
quit;

Art, CEO, AnalystFinder.com

Re: Creating a macro for the first time

new_sas_user_4 — Mon, 11 Jun 2018 13:07:08 GMT

Thank you Art297 🙂