Hi Kurt Bremser   Thanks for your help it worked.   I forgot to use case with sql in he proc step.   Just one question. why we have to   select *, . as lot,  and then in the other statement we only use  select *,   why select * cant bring everything in. Please advise.   thanks   Tauqeer   ... View more

thanks Reeza, I have worked it out, the path was incorrect. thanks for your help. ... View more

thanks Jag    it worked, I tried it with union all....   thanks   TK ... View more

Thanks , I revert back to the previous version and everything is normal now...thanks for your help. Tk ... View more

Issue to be solved: Write a data extraction macro from start to finish date: And we need to stamp the file name whenever the the data is run instead of naming the file manually. And may be we don’t need the start date , we just need the date on which it’s run and picks all the data for that date going back. TK ... View more

Hi Kurt  thanks for your reply , appreciate it,   so if you have to write this code from start to finish , how would you write  it , say you are writing it in this macro format , how would you define the date stamp macros  as global  or local , please advise what would be your flow.   i am asking these silly questions as its my first attempt to write any macro, so be patient.   as far as the data steps and symput is concerned, that's way outside my scope at this stage, may be later on.   thanks TK     ... View more

Ok Guys   I have simplified the code as much as possible and run it, it ran without any errors..   Please advise if you are happy with this.   I have removed the datestamp variable inside the macro as I want to use datestamp with other programs as well.       %let startdt = 01JAN2016; %let finishdt = 01FEB2017; %let datestamp = %sysfunc(putn("&finishdt"d, yymmn6.));   　 %macro extract(start,finish); data tk.application_extract_&datestamp.;   set sastrain.application_extract; where "&startdt"d <= datepart(d_entry) <= "&finishdt"d;   run; %mend extract; %extract(&startdt.,&finishdt.);     I tried both statements with where and both worked , don't know why, If you could clarify.     where "&start"d <= datepart(d_entry) <= "&finish"d;   and   where "&startdt"d <= datepart(d_entry) <= "&finishdt"d;   see the difference in "&start"d  and  "&startdt"d,  why these both are acceptable, don't know..   ******************************************   I want to ask you Kurt you mention about   data _null_ and the symputx  routines to do the same thing.  Please advise for my learning in which circumstances symputx is necessary and whats the advantage of that as compared to the code that I used, I am sure they do the same thing but whats the advantage of using symputx and why do we use it and prefer over the other code, just need to understand the difference and usage of these routines.      ... View more

Hi Kurt   This is a valid reason. But it gives you even more reason to incorporate the %sysfunc into the data statement data tk.application_extract_%sysfunc(putn(&finish,yymmn6.)); as it removes the ambiguous use of the name datestamp that will cause confusion sometime down the line.     1.  isn't it better to write :   data tk.application_extract_&datestamp.;       rather than replace the &datestamp with  %sysfunc(putn($finish, yymmn6.))   as datestamp will bring the same thing and easy to apply as its already defined as variable and its a simple code so I don't see any confusion about it.  Please clarify your confusion about it.   2.  You mentioned to remove %let startmonth = "&start."d; %let endmonth =  "&finish."d;   so I am confused as  I thought we are using them in the data statement :  Please clarify if that's not the case.     data tk.application_extract_&datestamp.; set sastrain.application_extract; where "&start"d <= datepart(d_entry) <= "&finish"d;   run;     **************************************************   If you have to write this code with global variable as datestamp which you can use anywhere in other programs. How would you write it from start to finish.   %let startdt = 01NOV2016; %let finishdt = 01FEB2017; %let datestamp = %sysfunc(putn("&finishdt"d, yymmn6.));   　 %macro extract(start,finish); %let datestamp = %sysfunc(putn("&finishdt"d, yymmn6.)); %let startmonth = "&start."d ; %let endmonth = "&finish."d ; %put &startmonth.; %put &endmonth.; %put &datestamp.; data tk.application_extract_&datestamp.;   set sastrain.application_extract; where "&start"d <= datepart(d_entry) <= "&finish"d;   run; %mend extract; %extract(&startdt.,&finishdt.);     ... View more

Hi Kurt   I appreciate your comments:   the reason I used the  %let datestamp outside the macro as this variable will be used later on when I create other reports based on the same data extract.   when you say to remove these 02,  I think we are using them with the where statement , don't we, please clarify.   %let startmonth = "&start."d ; %let endmonth = "&finish."d ;    I have removed the %enddate as well.   code:     %let startdt = 01NOV2016; %let finishdt = 01FEB2017; %let datestamp = %sysfunc(putn("&finishdt"d, yymmn6.));     %macro extract(start,finish);   %let datestamp = %sysfunc(putn("&finishdt"d, yymmn6.));     %let startmonth = "&start."d ; %let endmonth = "&finish."d ;     data tk.application_extract_&datestamp.; set sastrain.application_extract; where "&start"d <= datepart(d_entry) <= "&finish"d; run;   %mend extract;   %extract(&startdt.,&finishdt.);     ... View more

Hi Kurt   So please advise what's wrong with this code: This code below worked fine , so why I have to use routines here  , what advantage can symput gives me over this code below and frankly speaking I don't understand these routines anyway...      %let startdt = 01NOV2016; %let finishdt = 01FEB2017; %let datestamp = %sysfunc(putn("&finishdt"d, yymmn6.));     %macro extract(start,finish);   %let datestamp = %sysfunc(putn("&finishdt"d, yymmn6.)); %let startmonth = "&start."d ; %let endmonth = "&finish."d ;     data tk.application_extract_&datestamp.; set sastrain.application_extract; where "&start"d <= datepart(d_entry) <= "&finish"d; run;   %mend extract;   %extract(&startdt.,&finishdt.);     ... View more

Hi Tom   Thanks a lot for taking time and explain this to me in a very easy format.   So I have changed the code as per your instructions and it worked..     Code is :     %let startdt = 01NOV2016; %let finishdt = 01FEB2017; %let datestamp = %sysfunc(putn("&finishdt"d, yymmn6.));     %macro extract(start,finish);   %let enddate = %sysfunc(intnx(month,"&finish."d,0,b),date9.); %let datestamp = %sysfunc(putn("&finishdt"d, yymmn6.));     %let startmonth = "&start."d ; %let endmonth = "&finish."d ;     data tk.application_extract_&datestamp.; set sastrain.application_extract; where "&start"d <= datepart(d_entry) <= "&finish"d; run;   %mend extract;   %extract(&startdt.,&finishdt.);       I just need to ask few questions for my understanding and learning.   In this code,  three variables are defined outside the macro, do we call them global variables as they are defined outside the macro,  I think the reason datestamp Variable is defined outside the macro so we can use this datestamp to other codes in the same project whenever we create other files with same datestamp. So that means Startdt and finishdate could be defined inside the macro or do they have to define outside the macro,  that’s first question.   2nd question:   04 variables are defined inside the macro,   enddate variable is not used anywhere apart from the %put  function to see on the log so that can be removed , isn’t it ? Startmonth and endmonth  variables in the macro , we use “&start.”d ,  so the reason was to define the date literal , and we need to  dot after the start and it has to be in double quotes. Datestamp , I used the same way as it was outside the macro.  We use putn(“&finishdt”d, yymmn6.))..    we use putn  with finishdt to change it to numeric format?  Is it true?  %sysfunction is only used Within macro to able to use putn.   3rd question:   When we define the variable we use   %let startmonth = "&start."d ;   However when we use the where statement , we write as “&start”d  , no dot after the start, please advise on that.   where "&start"d <= datepart(d_entry) <= "&finish"d;     4th question:   If I don’t need start date and only need the finish date so I can extract all data from the finish date , can I write like this: and remove the startmonth variable.   %let finishdt = 01FEB2018; %let datestamp = %sysfunc(putn("&finishdt"d, yymmn6.));     %macro extract(finish);   %let enddate = %sysfunc(intnx(month,"&finish."d,0,b),date9.); %let datestamp = %sysfunc(putn("&finishdt"d, yymmn6.));   %let endmonth = "&finish."d ;     data tk.application_extract_&datestamp.; set sastrain.application_extract; where datepart(d_entry) <= "&finish"d; run;   %mend extract;   %extract(&finishdt.);     question 5:   As I have removed the  Data _null_  And symputx  routines from this code as it was not required.     I am just curious when do we require symput routines and why do we use it?     And if you suggest any good book or any other source to understand macros , that will be really handy.       Please advise.   Thanks for your time. TK ... View more