BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
new_sas_user_4
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

 

View solution in original post

19 REPLIES 19
art297
Opal | Level 21

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

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.

TomKari
Onyx | Level 15

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
Obsidian | Level 7

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
Opal | Level 21

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
Obsidian | Level 7

Thank you soooooooo much Art297 🙂 🙂 

new_sas_user_4
Obsidian | Level 7

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

@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
Opal | Level 21

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
Obsidian | Level 7

Thank you 🙂

 

new_sas_user_4
Obsidian | Level 7

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
Opal | Level 21

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
Obsidian | Level 7

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 19 replies
  • 1487 views
  • 2 likes
  • 5 in conversation