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

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;

%end;

%end;

%Mend Keep;

%Keep;

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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;

%end;

%end;

; /* this semicolon closes the keep statement */

run;

%Mend Keep;

View solution in original post

8 REPLIES 8
Reeza
Super User

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.

ballardw
Super User

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;

%end;

%end;

; /* this semicolon closes the keep statement */

run;

%Mend Keep;

Vince28_Statcan
Quartz | Level 8

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;

%end;

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

Quentin
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Amir
PROC Star

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.

Liz_LSU
Calcite | Level 5

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

Tom
Super User Tom
Super User

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

letsdoit
Fluorite | Level 6

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

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
  • 1010 views
  • 1 like
  • 8 in conversation