Thanks @Tom I tried your code, but it only grabs birthday when month doesn't end with 07, which is the opposite of what I want. I want to only keep birthday when month ends with 07, that means birthday will be missing (for exmaple) when month does not end with 07. Hope that makes sense.  ... View more

Thanks for the input, I guess my main question is if I have a longitudinal dataset with a time variable (fy, cy, quarter, etc), how to grab the age by the end of each time period? It looks like trying to use a macro is over-complicating the issue.  ... View more

Thanks @ballardw this works perfectly!! ... View more

Thanks @ballardw  will convert it to numeric. Any suggestions as how to write the macro? Thank you! ... View more

That'd be great, thanks @ballardw !! Below  is a snippet of the sample dataset I am dealing with: uci: unique identifier of the data eth2: hispanic or white svscd: type of services each UCI is receiving, categorical claim: total spending rank_caseload and rank_avg are the two ranking variables based on caseload and svsavgclaim cat: 4 categories I created. I want to draw a bubble plot with four quadrant sections (identified by the "cat" column where 4 categories are included: high-frequency, low-cost; high-frequency, high-cost; low-frequency, low-cost; and low-frequency, high-cost). I want the x-axis to be caseload values and the y-axis to be claim values or vice versa. I'd want the two ethnicities: Hispanic and White to be represented using different colors. Ideally each dot would represent an ID, so that I can see how dots are clustering around what section for the two ethnicity groups. Hope that makes sense.   Thanks again! ... View more

Sorry for my delayed response on this last thread, and thank you so much, Tom! This works like a magic.  ... View more

Thanks Tom for your quick response! I plugged in your code and SAS spit out error saying "do loop is not valid in open code", please see below: data base; 27 set %do offset=0 %to %sysfunc(intck(month,&mob.,&moe.)); ERROR: The %DO statement is not valid in open code. 28 lcmf.cm%sysfunc(intnx(month,&mob.,&offset),yymmn6.) WARNING: Apparent symbolic reference OFFSET not resolved. WARNING: Apparent symbolic reference OFFSET not resolved. ERROR: Argument 3 to function INTNX referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number. ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list. Execution of %SYSCALL statement or %SYSFUNC or %QSYSFUNC function reference is terminated. 29 %end; ERROR: The %END statement is not valid in open code. NOTE 139-205: Line generated by the macro function "SYSFUNC". 28 lcmf.cm. ________ 22 ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, ;, CUROBS, END, INDSNAME, KEY, KEYRESET, KEYS, NOBS, OPEN, POINT, _DATA_, _LAST_, _NULL_. NOTE: Line generated by the macro function "SYSFUNC". 28 lcmf.cm. ________ 201 ERROR 201-322: The option is not recognized and will be ignored. 30 ; 31 run; and my code is as below, I only added the libname prefix "lcmf" %let mob=%sysfunc(intnx(month,%sysfunc(today()),-16,B)); %let moe=%sysfunc(intnx(month,%sysfunc(today()),-5,E)); %put &mob.; %put &moe.; data base; set %do offset=0 %to %sysfunc(intck(month,&mob.,&moe.)); lcmf.cm%sysfunc(intnx(month,&mob.,&offset),yymmn6.) %end; ; run; Any idea of what may go wrong?    Thanks again! ... View more

I just thought of a quick fix, which is to shift the start and end date backward by 1, so that SAS is grabbing the correct years I need. No need to tweak the code itself. ... View more

exactly the one you replied to above, I also noticed the data files SAS was grabbing are wrong, instead of grabbing cm201202, cm201302, cm201402, it's grabbing cm201302, cm201402, and cm201502. Is there something that needs to be revised to the line "%let incr=%eval(&incr+1);"? Welcome your thoughts.  %macro loop(start,end); %let start=%sysfunc(inputn(&start,yymmn6.)); %let end=%sysfunc(inputn(&end,yymmn6.)); %let year=&start; %let month=&start; %let incr=0; data current; length dname $41; set %do %while(&year<=&end); %let incr=%eval(&incr+1); %put &=incr; %let year=%sysfunc(intnx(year,&start,&incr,b)); %let filename=cm%sysfunc(putn(&year,year4))%sysfunc(month(&start),z2); lcmf.&filename. (keep=uci status) %end; indsname=dname; source = dname; run; %mend; %loop(201202,201402) ... View more

Thanks Kurt!! Your revision works perfectly except for the new "source" variable. Somehow it shifted by 1 value: currently it shows "cm201302", "cm201402" and "cm201502" instead of "cm201202", "cm201302" and "cm201402", which are the correct values I expect. I know this has something to do with the filename loop set up, do you know any quick fix of this? I actually only want the year and month info, so values like 201202, 201302, 201402 would work as well.      ... View more