So you require the keyword parameters? - positional parameters are not an option?  If you can use positional parameters then this should be possible: %macro dotable( popfl, positional_parameter); proc sort data = adam.adae out = adae; by usubjid; where &popfl = 'Y' and &positional_parameter; run; %MEND; %dotable(saffl , astdy >=28);     ... View more

I would need to see more than the template to help you. ... View more

First, @raajdesaii , I will leave it to you to add the Visitdate column as you would like it.  Aside from that, this class of problem is interesting to me because I face it in my work too.  I think this is the way I handle this kind of problem.  It needs these five steps, I say, if for anything, to be able understand what we had programmed when we look back six months later. data IDs; set have (Keep=ID Startdate Enddate); by ID; if first.ID; run; data AllNewDates (sortedby=ID Newdate); set IDs; Newdate=intnx('month',Startdate,0); do while (Newdate<Enddate); Count=0; output; Newdate=intnx('month',Newdate,1); end; run; data Newdates0 (Keep=ID Newdate); set have (Keep=ID Visitdate); Newdate=intnx('month',Visitdate,0); format Newdate date9.; run; proc freq data=Newdates0; by ID; table Newdate / out=Newdates(drop=PERCENT) noprint; format Newdate date9.; run; data want; update AllNewDates Newdates; by ID Newdate; format Newdate monyy7.; run;   ... View more

I qualified my use of the value 0.7 as the value that worked for me.  If you search this board, you will find other people using different values of WIDTH_FUDGE.  You may need to make WIDTH_FUDGE 0.75, or higher.    I have never used WIDTH_POINTS.  Googling it I saw a mention of a problem note number, check for SAS notes incase you are encountering problems described in any SAS Notes.  ... View more

what if the number of visits is greater than the number of months between Startdate and Enddate? ... View more

A problem explanation than can be contradictory implies a problem that was written in a mathematical logic.  Our friend gave us an example of what they are looking for.  I formed an opinion of what they wanted, and offered an my best educated guess.  I though it was a better answer than Mr. @mkeintz , before he retracted it. ... View more

I agree the definition of "round" is not standard.  What I think is being described is a rounding, in terms of scientific notation, where the significand or mantissa is rounded to the nearest 0.5.  If that is so, I find the mantissa, round, and convert back to normal, non-scientific-notation form.     %macro RandBetween(min, max); (&min + floor((1+&max-&min)*rand("uniform"))) %mend; data want ; do i = 1 to 10; p=%RandBetween(2,nobs); set SASHELP.GEOEXM(Keep=last) point=p nobs=nobs; last_power = 10**floor(LOG10(last)); drop last_power ; last_new = round(last,0.5*last_power); output; end; stop; proc sort; by last; data want; set want; i=_N_; run;   i Last last_new 1 6187 6000 2 9933 10000 3 15660 15000 4 36662 35000 5 45234 45000 6 47104 45000 7 64690 65000 8 72860 75000 9 76560 75000 10 124603 100000   ... View more

  I wrote this code to see how different, if different, the other spreadsheets would be, using SASHELP.CARS.  The answer was yes it allows different alignments.  It attempts to autofit column widths to the data.  But it doesn't do it well IMO. SAS doesn't "know" what your Excel's default font is so it, I believe, makes a conservative guess.   Another way to put it, SAS can't Autofit column widths like Excel, it must calculate the column widths, with an educated guess, before Excel is open to view the file.  The question becomes, to me, how do you get to SAS to fit column widths well...  as well as Excel?   ODS TAGSETS.EXCELXP did this job better.  It had the option WIDTH_FUDGE.  On my Excel, with the font I use, I always set WIDTH_FUDGE=0.7.  I was almost always, 99.99%, happy with its choice of automatically calculated column widths.  You might try TAGSETS.EXCELXP.   data cars; retain N (0); set sashelp.CARS; by Make; if first.Make then N+1; run; %MACRO Rpt /parmbuff; ODS Excel OPTIONS (Orientation = 'landscape' sheet_name = "OB List" sheet_interval = 'Proc' embedded_titles='yes' AUTOFILTER ="All" FLOW="TABLES" ROW_HEIGHTS="39, 18"); ods listing close; proc print NoObs data=work.Cars(obs=30); where &syspbuff; var _all_; run; %Mend; %macro byMake; %do n=1 %to 3; ODS Excel OPTIONS (Orientation = 'landscape' sheet_name = "OB List" sheet_interval = 'Proc' embedded_titles='yes' AUTOFILTER ="All" FLOW="TABLES" ROW_HEIGHTS="39, 18"); %Rpt(N=&n) %end; %Mend; ODS Excel file="test.xlsx"; %Rpt(1); %byMake; ODS EXCEL CLOSE;     ... View more