In the absence of a reply, here's some code that solves what I presume is the typical "bringing back the future" problem.  For each record, a missing value is replaced by the earliest following non-missing value, within the same group.  This means of course that any group that ends with missing values will not have those values replaced:   data ds_missing_values; infile datalines dlm=','; input group $ var1 var2 var3; datalines; A, 1, 2, 3 A, ., 2, 5 A, 3, 4, . A, 8, ., . B, ., ., . B, ., 2, 6 B, 3, 1, 8 C, 9, 4, 8 run; data want (drop=_:); set ds_missing_values (in=firstpass) ds_missing_values (in=secondpass); by group; array var {3} var1-var3; array history {3,20} _histv1_1-_histv1_20 _histv2_1-_histv2_20 _histv3_1-_histv3_20 ; retain _: ; if first.group then call missing(_f,_s,of _:); _f+firstpass; if firstpass then do _v=1 to 3; history{_v,_f}=var{_v}; end; if secondpass; _s+secondpass; call missing(history{1,_s},history{2,_s},history{3,_s}); if var1=. then var1=coalesce(of _histv1:); if var2=. then var2=coalesce(of _histv2:); if var3=. then var3=coalesce(of _histv3:); run; The program passes through eac group twice: the first time to build a history (up to 20 obs in this case) of values for VAR1, VAR2, and VAR3.  The second pass will tap into that history, replacing a missing value with the earliest following non-missing (that's what the coalesce functiom does).  Also during the second pass, current data in the history array is eliminated, so only future values are exposed to the coalesce functions. ... View more

@BrahmanandaRao wrote: Thank You, Mkeintz for your solution  in your tricky code how to filter Nth max values  suppose i want 5 max value where i have to change in this code    Do you want the 5th largest age, or all observations with the 5th largest or larger? ... View more

@BrahmanandaRao  This forum is about SAS users supporting other SAS users. What's appreciated is if you demonstrate some own effort as else you might end-up with people no more providing answers to your questions. ... View more

PROC REPORT will do this for you, and on top of it from the original long dataset, without the need to transpose to wide. No need to trouble yourself with a DATA step.  ... View more

Just for fun adding a RegEx option. It also covers your later requirement to uppercase the last character in the string. data test; length source target $5; do source=' ','a','aa','aaa','aaaa','aaaaa'; target=prxchange('s/^(.)(.)(.*?)(.?)$/$1\u$2$3\u$4/oi',1,strip(source)); output; end; run; proc print data=test; run;   ... View more

Instead of creating two variables we can perform with one  data edit; set sashelp.class; if age >= 15 then do; applied='Major'; end; else if age<15 then do; applied='Minor'; end; run; proc print data = edit; run; ... View more

Now, what do you expect for a "first" and "last" name from these names   Sun Flower Moon Child John Richard II Van Opdorp Maria Garcia Y Mendoza Smith, John David Junior Junior Childs Junior   There are rules for parsing names that are cultural and almost any simple one or two line programming solution won't "work" for all of them and may not work for large numbers.   If the question is "what are the first two words out of this list of 3 words" that is one thing. ... View more

Please explain how input dates of 3Mar2012 5Mar2012 become 06MAY2064 in your "required output using proc sql". Really? You want a date that is 50 or more years later than the dates given??? And in a different month? ... View more

Your SQL code works with your sample Excels. What's the problem/your question. In case in your real data the columns in the Excels are not always in the same order: Use  union all CORR   ... View more

A similar question was already asked here few times e.g., https://communities.sas.com/t5/SAS-Programming/How-to-split-a-variable-into-200-Character-without-chopping-a/m-p/620115 or https://communities.sas.com/t5/SAS-Programming/split-name-as-single-letter/m-p/878153   Try to improve your googling skills 😉   Bart ... View more

Testing if the first or last character is a K is easy enough. For example you could use FINDC(). startK=1=findc(name,'k','i'); lastK=length(name)=findc(name,'k',-length(name),'i'); Or just =: operator. startK=upcase(name)=:'K'; lastK=upcase(reverse(strip(name)))=:'K'; Testing if there is an L anywhere the middle can be done with FINDC(). middleL=1<findc(name,'L','i')<length(name); Then just require that all three are true. want = startK and lastK and middleL ; Of course if the first and last have to be K then you don't really care where the L is since it will NOT be the first or the last so it will be in the middle. want=upcase(name)=:'K' and upcase(reverse(strip(name)))=:'K' and findc(name,'L','i') ; ... View more

Perhaps you are asking about automatic variables?  https://documentation.sas.com/doc/en/lrcon/9.4/p0e0mk25gs9binn1s9jiu4otau29.htm ... View more

I would start with a bare import and then look at the results of Proc Content for the properties of the data set. Because of the many questionable things I see done in spreadsheet files it is quite possible that the "ord_date" isn't even a data value in the file, just a number impersonating a date because people see it that way.   So try running: proc import datafile='/home/u35263349/orders.xlsx' out=order_table dbms=xlsx ; run; proc contents data=order_table; run; Then share the results of the Proc Contents output. Hint: if you see that your Ord_date is numeric with a format like BEST12. then the value is not a date and you need to do more get one. Not hard, one line of code in a data step to create and another to assign a format, just routine. ... View more