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

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;

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

8 REPLIES 8
Reeza
Super User

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. 

BrunoMueller
SAS Super FREQ

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

CP2
Pyrite | Level 9 CP2
Pyrite | Level 9

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 ;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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. 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

ballardw
Super User

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

 

CP2
Pyrite | Level 9 CP2
Pyrite | Level 9

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.

CP2
Pyrite | Level 9 CP2
Pyrite | Level 9

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' ;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 8 replies
  • 6761 views
  • 0 likes
  • 5 in conversation