DATA Step, Macro, Functions and more

date functions using global macro variables

Accepted Solution Solved
Reply
Frequent Contributor
Frequent Contributor
Posts: 134
Accepted Solution

date functions using global macro variables

Hello,

I have code that requires using month, year and sometimes prior month (month-1) so I am setting global variables to the dates needed. I would rather just have to enter the full date which is used most often in my code but occasionally I need to just enter a month and year based on the full date. So, I need to create a global variable based on another global variable. The following code does not work but shows what I am looking for.  The enddate macro variable needs to be in the format below because that's how it works with our SQL dataset. Any help would be much appreciated!!

 

/*User Input*/

%LET enddate = '11/1/2016' ;
/*for validation*/
data _NULL_;
format adminyear mmddyy10. ;
adminyear=year(&enddate.) ;
call symput('adminYr',adminyear);
run;


Accepted Solutions
Solution
‎12-22-2016 10:38 AM
Super User
Super User
Posts: 7,961

Re: date functions using global macro variables

Why does enddate have quotes around the value?  That is really not a good idea.  If you get rid of the quotes then you can simply:

%let enddate=11/1/2016;
%let year=%scan(%trim(&enddate.),3);
%put &year;

It is never a good idea to put quotes in macro variables.

View solution in original post


All Replies
Super User
Posts: 19,815

Re: date functions using global macro variables

Year() requires a SAS date. Use input to convert to date, or %SCAN to extract the third term. Be very careful with how the quotation marks are handled. 

SAS Super FREQ
Posts: 708

Re: date functions using global macro variables

Hi

 

I totally agree with @RW9 for not having quotes in macro variable values.

 

You can convert the text representing the date into a SAS date value, you can then use any date related functions on the value.

 

Here is an example

/*User Input*/
%LET enddate = 11/1/2016;
/* convert text date to SAS date */
%let sasDate = %sysfunc(inputn(&endDate, mmddyy10.));
/* print out number representing SAS date and and formatted date */
%put NOTE: &=sasDate %sysfunc(putn(&sasDate, date9.));

/* we can use any date functions with the SAS date */
%let endYear = %sysfunc( year(&sasDate) );
%put NOTE: &=endYear;

Bruno

Frequent Contributor
Frequent Contributor
Posts: 134

Re: date functions using global macro variables

Posted in reply to Bruno_SAS

Thank you. I have made changes to my code and removed quotes for most except one that is a special case. I thought you might be able to help with conditional macro variables. I tried to use macro facility to do this but it is not working. Also since I had to use the %str() for one of my global variables I am not sure how to reference it in the macro - at least it doesn't seem to be catching the CRC if-then statement. Do you see what might be causing the oral1 and oral2 macro variables from not being created?

 

%LET Diag=%str(CRC) ; /*for file names: CRC, MM, NSCLC, Unknown*/
LIBNAME dataPrep "C:\data\&diag.\" ;


%LET enddate = 11/1/2016 ;

 

/*for validation*/
%LET adminyr = %scan(%trim(&enddate.),3);
%LET adminmth = %scan(%trim(&enddate.),1);

 

/*orals*/
%macro orals() ;
%if &diag.='MM' %then %do ;
%LET oral1=lenalidomide ;
%LET oral2=melphalan ;
%end;
%else %if &diag.='CRC' %then %do;
%LET oral1=capecitabine ;
%LET oral2=regorafenib ;
%end ;
%mend ;
%orals;
%put &oral1 &oral2 ;

Super User
Super User
Posts: 7,961

Re: date functions using global macro variables

Sorry, I can't see why you are doing this in macro?  You have some data - enddates, diagnosis etc. So why not put them in a "dataset" and use a "datastep" to process them - this is what SAS is all about.  Macro is nothing more than a text generation tool which assists in generating repetitive code - it does nothing on its own. 

Solution
‎12-22-2016 10:38 AM
Super User
Super User
Posts: 7,961

Re: date functions using global macro variables

Why does enddate have quotes around the value?  That is really not a good idea.  If you get rid of the quotes then you can simply:

%let enddate=11/1/2016;
%let year=%scan(%trim(&enddate.),3);
%put &year;

It is never a good idea to put quotes in macro variables.

Super User
Posts: 11,343

Re: date functions using global macro variables

If you need to send quotes to a remote system do it in the call if all practical, not in the value. Or AFTER creating the date then use something like %quote(&macrovariable).

 

Frequent Contributor
Frequent Contributor
Posts: 134

Re: date functions using global macro variables

thank you! I posted another question in the latest response to my initial problem so if you have time to take a look that would be great.

Frequent Contributor
Frequent Contributor
Posts: 134

Re: date functions using global macro variables

I took out the single quotes on my macro variables and placed double quotes (or single quotes) around the variable when called but the PROC SQL doesn't like it. I'd like to take the warnings people have given me about not putting quotes in the macro values if there is a quick fix otherwise i have to go back to placing single quotes in the macro variable values when using them in PROC SQL. It might have something to do with placing the code within EXECUTE blocks of the proc sql.

 

Note: the 11/1/2015 in the error message below is referencing another variable named &startdate that was closer to the top of code.

 

%let enddate=11/1/2016 ;

 

10853 WHERE StandardDrugType = 'chemotherapy'
10854 AND AdministrationDate < "&enddate."
10855 AND YEAR(DiagStartTherapyDate) - PatientMasterBirthYear >= 18
10856 ) by myconn ;
ERROR: CLI execute error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name
'11/1/2015'. : [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name
'11/1/2016'.

 

The code works when %LET enddate='11/1/2016' ;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 763 views
  • 0 likes
  • 5 in conversation