topic Re: Macros and Dates in SAS Programming

Macros and Dates

letsdoit — Wed, 28 Aug 2013 21:59:21 GMT

I have the following code that is not working and I get error message. What am I doing wrong? Or is there an easier way to have it done? (I am trying to keep data for a particular date which can be any date this year or few years ago)

YEAR1=2001;%let YEAR2=2002;%let YEAR3=2003;%let YEAR4=2004;%let YEAR5=2005;%let YEAR6=2006;%let YEAR7=2007;

YEAR8=2008;%let YEAR9=2009;%let YEAR10=2010;%let YEAR11=2011;%let YEAR12=2012;%let YEAR13=2013;%let YEAR14=2014;

YEAR15=2015;%let YEAR16=2016;%let YEAR17=2017;%let YEAR18=2018;%let YEAR19=2019;%let YEAR20=2020;%let YEAR21=2021;

YEAR22=2022;%let YEAR23=2023;%let YEAR24=2024;%let YEAR25=2025;%let YEAR26=2026;%let YEAR27=2027;%let YEAR28=2028;

YEAR29=2029;%let YEAR30=2030;%let YEAR31=2031;%let YEAR32=2032;%let YEAR33=2033;%let YEAR34=2034;%let YEAR35=2035;

YEAR36=2036;%let YEAR37=2037;%let YEAR38=2038;%let YEAR39=2039;

MONTH1=01;%let MONTH2=02;%let MONTH3=03;%let MONTH4=04;%let MONTH5=05;%let MONTH6=06;%let MONTH7=07;%let MONTH8=08;

MONTH9=09;%let MONTH10=10;%let MONTH11=11;%let MONTH12=12;

DAY1=1;%let DAY2=2;%let DAY3=3;%let DAY4=4;%let DAY5=5;%let DAY6=6;%let DAY7=7;%let DAY8=8;%let DAY9=9;

DAY10=10;%let DAY11=11;%let DAY12=12;%let DAY13=13;%let DAY14=14;%let DAY15=15;%let DAY16=16;%let DAY17=17;

DAY18=18;%let DAY19=19;%let DAY20=20;%let DAY21=21;%let DAY22=22;%let DAY23=23;%let DAY24=24;%let DAY25=25;

DAY26=26;%let DAY27=27;%let DAY28=28;%let DAY29=29;%let DAY30=30;%let DAY31=31;

%Macro Keep;

%do i = 13 %to 13;

%do j = 8 %to 8;

%do k = 20 %to 20;

data main;

set have;

keep Date Main_&&&&MONTHj..._&&&DAY&k.._&&YEAR&i;

run;

%end;

%Mend Keep;

%Keep;

Thanks!

Re: Macros and Dates

Reeza — Wed, 28 Aug 2013 22:07:08 GMT

IMO use the dictionary tables instead and parse the variable names. Less messy.

1. Get all column names from sashelp.vcolumn for the table
2. Extract the date from the variable name and convert to a SAS date.

3. Filter that based on your criteria

4. Either select the ones you want into a macro list for keep or the ones you want to drop into a drop list.

Re: Macros and Dates

ballardw — Wed, 28 Aug 2013 22:48:04 GMT

I'm going to assume you had a bunch of %let get stripped accidentally when posting the question.

What do your actual variable names look like? I suspect you have too many &&& and ... in the constructions.

If your variable names are Main_02_15_2005 then the reference should look more like

Main_&&month&j.._&&day&k.._&&year&i

And if you want to keep a range as specified by your do loop, the loop boundaries are in the wrong place

%Macro Keep;

data main;

set have;

keep Date

%do i = 13 %to 13;

%do j = 8 %to 8;

%do k = 20 %to 20;

Main_&&month&j.._&&day&k.._&&year&i

%end;

; /* this semicolon closes the keep statement */

run;

%Mend Keep;

Re: Macros and Dates

Quentin — Thu, 29 Aug 2013 10:03:47 GMT

Hi,

When you say you want to keep data for a date, are you trying to select *records* for that date, or *variables*.

The KEEP statement is for selecting *variables*. Does your dataset HAVE have a lot of variables named Main_08_29_2013 Main_08_28_2013 etc? If so, then the keep statement makes sense.

If your dataset HAVE has a variable named DATE in it, and you want to want to select *records*, you need a WHERE statement (or subsetting IF), not KEEP.

HTH,

--Q.

Re: Macros and Dates

Amir — Thu, 29 Aug 2013 11:35:33 GMT

Hi,

Firstly, well done on posting what you're trying to do and providing some code.

1) Please clarify "I get error message" by posting the log (see point 2) showing what the error message is and the code that was executed up to that point.

2) Further, if you haven't already, consider using various macro options to aid diagnostics, e.g.:

options mprint symbolgen mlogic;

3) Lastly some sample input data and desired output data would also be helpful.

Regards,

Amir.

Re: Macros and Dates

Vince28_Statcan — Thu, 29 Aug 2013 13:20:20 GMT

As Ballardw said, it appears as though you are dereferencing macro variables too much for the way you've constructed your macro variables.

Main_&&&&MONTHj..._&&&DAY&k.._&&YEAR&i;

would resolve as follow

first pass:

&& resolves to &, && resolves to & (leaving &&monthj..._)

&& resolves to &, &DAY resolves to an error because there are not macro variables DAY, &k resolves to 20 (leaving &<some error>20._ to resolve)

&& resolves to &, &i resolves to 13 (leaving &year13) to resolve

Your real desired result would be along the lines of what Ballard posted. For each macro variable, you only need to double ampersand the month/day/year so that on first pass, it resolves && to & but the indexes ijk are resolved properly and that then on 2nd pass, you are left with &month1_&day2_&year3 to resolve.

However, there are definitely many "better" ways to achieve your desired results for this example but depending on how you loop, you may or may not want to go one way or another.

An approach would be as follow:

%macro show();

%do i=1 %to 13;

%do j=1 %to 12;

%do k=1 %to 31; /*just to give a broader spectrum*/

%put Main_month%sysfunc(putn(&j, z2.))_day%sysfunc(putn(&k, z2.))_year20%sysfunc(putn(&i, z2.)) ;

%end;

%mend;

%show();

That is, with the use of %sysfunc(putn( ,z2.)) there is no need to force yourself to hard type everything. you can just parse month day and year20 as text (instead of as macro variables) and use putn to do the job of left 0 padding numbers below 10.

Re: Macros and Dates

Liz_LSU — Thu, 29 Aug 2013 14:43:52 GMT

>> I am trying to keep data for a particular date which can be any date this year or few years ago

Are the dates in your data in ONE variable or is there a different variable for EACH DATE (which would be thousands of variables, and a real nightmare!)?

From your code, it looks like you are looking at one date at a time, which leads me to believe that perhaps you have one variable and you want to work with all the observations for a particular date.

If that's the case, you just need to use a WHERE statement if you have a variable that contains the date. You don't even have to create a subset.

proc print data=greatbigdataset;

where transdate = '20AUG2013'd;

var ....;

run;

If this isn't the case, please post some sample data (if even it's very simplified and not real) so we can see the actual structure of your data set.

liz

Re: Macros and Dates

Tom — Thu, 29 Aug 2013 15:24:07 GMT

Do your variable names cover all days in an interval?

Perhaps you just need a simple macro to loop over a date range and generate variable names in the format you want?

%macro genvars(prefix,start,end);

%local date;

%do date=%sysevalf(&start) %to %sysevalf(&end);

&prefix._%sysfunc(translate(%sysfunc(putn(&date,mmddyyd10.)),_,-))

%end;

%mend genvars;

%Put %genvars(main,'25JAN2013'd,'12FEB2013'd) ;

main_01_25_2013 main_01_26_2013 main_01_27_2013 main_01_28_2013 main_01_29_2013 main_01_30_2013 main_01_31_2013 main_02_01_2013

main_02_02_2013 main_02_03_2013 main_02_04_2013 main_02_05_2013 main_02_06_2013 main_02_07_2013 main_02_08_2013 main_02_09_2013

main_02_10_2013 main_02_11_2013 main_02_12_2013

Re: Macros and Dates

letsdoit — Thu, 29 Aug 2013 19:03:41 GMT

I got it working. Thank you all for the helpful insights!!!!