How should this cases be handled? Some missing values, but all non-missing values are identical ... View more

Agree with the advice.  A small tweak to the solution: do i=1 to 1 + countw(dates,','); ... View more

Of course you're right here.  Time to invest on my side:  a new pair of bifocals and a new coffee maker.  Happy 2025 to all. ... View more

@mkeintz ,   I'm usuallly right there with you, trying to use the simplest and most direct tools.  But this is a case where I feared PROC RANK would put us on the inexorable path toward 20 posts before hitting a solution.  Here's what I expected:   Poster would actually try a PROC RANK solution, then complain that it didn't work. Someone would post that "didn't work" is awfully vague and would request a copy of the log. Poster would post the log, but as text so that it is difficult to read. Someone would post instructions on the right way to post a log. Poster would actually post the log in a readable form. Nothing would appear to be wrong, and someone would ask the original poster why s/he insists that it didn't work. Poster would eventually say that there is nobody assigned to percentile 1 or 2, and that the lowest salary starts with percentile 3. I would get to post why that happens and say, "That's what you asked for." Poster would reply that percentiles should be assigned differently... You must get the idea by now.  Let me skip some of the process and jump right to the issue. PROC RANK processes the 0 salary values into percentile 1.  Once this statement runs, nobody is left in the first percentile:  if salary = 0 then percentile = 0; Percentile 2 might be a little light as well. I was imagining an approach where percentiles get assigned based only on the positive salaries (still assigning salary=0 to percentile 0).  This could easily be achieved by cleaning the data now:  if salary = 0 then salary = .;  But for some reason, it seems the original poster is not allowed to do this.  Cleaning the data first would solve for: percentile assignment using a simple PROC RANK detecting other bad data.  For example if one data entry person used salary=0 for missing values, perhaps another used salary=-999. duplicate entries for the same person.  If the current form of the data is acceptable, I'm not going to try to explain what happens in a many-to-many merge I'm not claiming that my posted solution is best or even that it works.  LIke you, I don't have any data to use to test it.  Unlike you, I haven't had SAS available for a few years.  (No, I'm not in jail, just not motivated to fiddle with my ancient desktop machine.)  Once the data is clean, another viable approach (even with 0 representing missing values) might be to separate the data into two sets.  One holds salary=0 observations, and one holds salary > 0 observations.  Run the PROC RANK on the salary > 0 observations, then put the groups back together again. Anyway, we'll see where this journey goes.  Best of luck to all of us along the way. ... View more

Here's a reasonable way: proc sort data=have; by salary; run; proc summary data=have; where salary > 0; output out=n_salaries n=n_salaries; run; data want; if _n_=1 then set n_salaries; set have nobs=_nobs_; by salary; retain group; if salary > 0 then n + 1; if first.salary then do; if salary <= 0 then group = 0; else group = ceil(100 * n/n_salaries); end; run; It's untested, since there's no data.  But it should be in the ballpark.  It uses GROUP to represent the percentile, and it does allow a largest percentile of 100. ... View more

Here's a question you will need to address.  The methods you choose (whether PROC RANK or any other method) will not let you avoid answering this question.    How do you want to handle ties?  To illustrate, let's say you have 100,000 observations.  But 5,000 of them have the exact same score.  How do you distribute them among percentiles?  Will all 5,000 go into the same percentile (leaving some other percentile with 0 observations)?  Or will you assign some of the 5,000 to one percentile and some of the 5,000 with the exact same score to a different percentile? ... View more

@Tom ,   Will this work with the WHERE= that you have: where = (type2 = 1) or will it require referring to the original variable name? where = (type = 1)   ... View more

It might have been easier to fix the data before comparing.  Given that you woulod like to fix the output data set instead, what do you know about it?  Do you know the name of the output data set, and the variables within?  You will need to write a program, since that's the usual starting point to be able to write a program. ... View more

I can see this is getting too complex, and that MLOGIC might not be enough to paint the right picture.  Here's some more detail. Macro programming statements such as %IF %THEN are never part of a DATA step.  Your program generates this code instead: %if &MOD = N %then %let M1 = N; /* As Tom points out, &MOD has the value of the letters MOD */ %else %if %EVAL(&MOD in 6 8 9) %then %let M1 = Y; %else %let M1 = N; /* Always assigned as N, and never changed during the DATA step */ data TEST1; infile datalines; input ID $ MOD $; length PRODUCT $ 10; if &ID = "0001" and "&M1" = "Y" then PRODUCT = "SCOOTER"; else if &ID = "0001" then PRODUCT = "BIKE"; else if &ID = "0002" then PRODUCT = "SKATEBOARD"; datalines; 0001 N 0001 0 0001 6 0002 8 ; run; The %IF %THEN statements execute just once, before the DATA step even begins to run.  The DATA step does contain the DATA step programming statements such as IF THEN that generates PRODUCT.  But the macro %IF %THEN statements run just once, before the DATA step begins to execute.  Use DATA step programming statements to change the values of DATA step variables as the DATA step executes. ... View more

Since you have gotten this far, I would suggest you would learn more by solving this one yourself. Add this statement at the beginning:   options MLOGIC;   That will show you what macro language is trying to do at each step of the way. ... View more

It seems you often look for ways to shorten your code.  This type of code is a good candidate: if B not in (0, .) then C = A / B; It can be shortened to: if B then C = A / B; When SAS interprets logical expressions as true or false, 0 and missing values are false and all other values (including negative numbers) are true. ... View more

A simple solution for the simplest form that the problem might take on: data want; set have end=done; output; if done; value = 0; if month < 12 then do month = month + 1 to 12; output; end; run; If more is required, let us know why. ... View more

As you can see, this has the possibility of being a complex problem.  At the cost of some processing time, let me show you how to turn it into an easy problem, one that most junior programmers would use and maintain. %macro RRR; %local month. %do YYMM=2306 %to 2403 ; %let month = %substr(&YYMM, 3, 2); %if &month > 0 and &month < 13 %then %do; Data ttt; set Mon_tbl&YYMM.; run; proc append data=ttt base=want force;quit; %end; %end; %mend; %RRR So you go through 100 iterations of a %DO loop, and only process the values that represent a valid month.   ... View more