You pasted the data into your question as a table.  Does that mean it is coming to you in a spreadsheet?  Is it a .xlsx file (current standard) or the older .xls file?   Or did the data come to you in a TEXT file?  Perhaps it was a delimited text file, such as a .csv file, and your PC automatically open it with a spreadsheet program instead of text file?  If is a text file then share some lines of the text file (with appropriate redactions).   Are there always three and only ever three patients per hospital?   Why does one hospital have MV and VASODIL and the other does not?   Can you explain why it make sense to only keep the first patients LOS.  And why it makes sense to associate the second patient id with the INDCOST and the third patient with the PHARM costs.     ... View more

Please show the code you used to try and match the records.  You mentioned SQL but only showed DATA step code.  Did you try to do the matching using SQL?   A couple of issues to check. When you shorten the length of variable the way you did SAS will take the first 9 characters of the field.  If your 9 digit numbers were right aligned in the 20 character variable then the first 9 characters are all spaces. If the character variables where created by letting SAS autoconvert a numeric variable to character then SAS will use the BEST12 format.  Which for the examples you showed would result in POLICY numbers without the leading zeros (but with the leading spaces).  So a numeric value like 718,124 would have been converted to '      718124' instead of '000718124'.   ... View more

It should not matter how large the database being searched is (other than performance).  But if you meant that you need to allow the user to enter a list of a million customer names then do not use that type of prompt.   Instead have them upload a DATASET.   The point is that your code it not even trying to use all of the values the user entered.   Your code is only using the first value.  The first value will be placed in the macro variable without a numeric suffix and also into the macro variable with a suffix of 1.  The second value will only be placed in the macro variable with the suffix of 2.   So what code do you want to generate when the user enters more than one name? Perhaps something like using FINDW() to check if any of the names appears in any of the variables of interest. and (findw(catx('|',R_SURNAME_OR_LEGALENTITYNAME,R_NAME_OR_TRADINGNAME,INDIVIDUAL3PARTYSURNAME,INDIVIDUAL3PARTYNAME) ,"&resident_customer1",'|','i') or findw(catx('|',R_SURNAME_OR_LEGALENTITYNAME,R_NAME_OR_TRADINGNAME,INDIVIDUAL3PARTYSURNAME,INDIVIDUAL3PARTYNAME) ,"&resident_customer2",'|','i') ... ) ;  You can use a %DO loop to generate that code. ... and ( %do i=1 %to &resident_customer_count ; %if &i=1 %then %let resident_customer1=&resident_customer; %else or ; findw(catx('|',R_SURNAME_OR_LEGALENTITYNAME,R_NAME_OR_TRADINGNAME,INDIVIDUAL3PARTYSURNAME,INDIVIDUAL3PARTYNAME) ,"&&resident_customer&i",'|','i') %end; )     ... View more

That GRAYCODE function is interesting. Why not use it to generate a score dataset that you could use with PROC SCORE? %let n=6; data have; input id target var1-var&n ; cards; 1 10000 -2000 10000 3000 7000 12000 30 2 7000 5000 1500 500 6500 750 . ; data score; _type_='SCORE'; length _name_ $32 ; array var[&n] ; nterms=-1; do j=1 to 2**&n; r=graycode(nterms,of var[*]); _name_=cats('X',of var[*]); if nterms then output; end; keep _type_ _name_ var: ; run; proc score data=have score=score out=wide; var var:; id id target; run; Then you could use PROC TRANSPOSE to get to something similar to the requested output. proc transpose data=wide name=inputs out=solutions(where=(col1=target)); by id target; var x: ; run; data solutions; sol+1; set solutions; by id target ; output; if last.target then sol=0; drop col1; run; Results     ... View more

Why not use FETCH ?  Been using it for a LOOONG time. https://github.com/sasutils/macros/blob/master/nobs.sas   ... View more

I still bet it was improper reading of text file. Example: data have; input @1 dob :mmddyy. date :mmddyy. @1 bad_dob mmddyy10. bad_date mmddyy10. ; format _all_ date9.; cards; 1/1/1960 4/28/1987 1/1/1960 9/28/2021 1/10/1960 12/18/2012 1/9/1960 6/5/2007 12/31/1959 1/11/2024 ; Results Obs dob date bad_dob bad_date 1 01JAN1960 28APR1987 01JAN1960 . 2 01JAN1960 28SEP2021 01JAN1960 . 3 10JAN1960 18DEC2012 10JAN1960 18DEC2012 4 09JAN1960 05JUN2007 09JAN1960 . 5 31DEC1959 11JAN2024 31DEC1959 11JAN2024 Log 1 data have; 2 input 3 @1 dob :mmddyy. date :mmddyy. 4 @1 bad_dob mmddyy10. bad_date mmddyy10. 5 ; 6 format _all_ date9.; 7 cards; NOTE: Invalid data for bad_date in line 8 11-20. RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+---- 8 1/1/1960 4/28/1987 dob=01JAN1960 date=28APR1987 bad_dob=01JAN1960 bad_date=. _ERROR_=1 _N_=1 NOTE: Invalid data for bad_date in line 9 11-20. 9 1/1/1960 9/28/2021 dob=01JAN1960 date=28SEP2021 bad_dob=01JAN1960 bad_date=. _ERROR_=1 _N_=2 NOTE: Invalid data for bad_date in line 11 11-20. 11 1/9/1960 6/5/2007 dob=09JAN1960 date=05JUN2007 bad_dob=09JAN1960 bad_date=. _ERROR_=1 _N_=4 NOTE: The data set WORK.HAVE has 5 observations and 4 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds ... View more

The WHERE clause limits the cartesian product results that are considered to just those where the salary from a the second copy is larger than the current salary.  Then the HAVING clause further limits the records to only those that have exactly two different salaries that are larger.      Which is ONE definition of the third largest.  In this algorithm multiple similar salaries are counted as one.  So in the series 100,100,95,95,95,90,80 it will pick 90.    Another definition might select 95 instead since there are  two observations (both with salary=100) above it.  That you could do even simpler in SQL if the goal is just for a HUMAN to see the answer (and not put the answer into a dataset or something a computer could use.  Just use the OUTOBS= option of PROC SQL. proc sql outobs=3; select * from have order by salary desc ; quit; Note that with PROC RANK you can choose between these two and many other options for how to rank the values. ... View more

@TimurShangareev wrote: Is there any method, where I can extract dataset name from expressions like: <work.ds(...where=(...))> <ds(... where=(...))> or somehow check that this expression is valid in macro calls (wthout PROC or DATA statements)? It would help if you explained in more detail what you actually mean. Perhaps some example use cases would help?   You mentioned macro calls.  So in macro code here are a couple of simple things. %let dsn=sashelp.class(where=(age>11)); %let valid=FALSE; %let dsid=%sysfunc(open(&dsn)); %if &dsid %then %do; %let valid=TRUE; %let dsid=%sysfunc(close(&dsid)); %end; %let name_only=%scan(&dsn,1,()); %let options=%substr(&dsn%str( ),%length(&name_only)+1); %let memname=%scan(&name_only,-1,.); %let libref=%scan(WORK.&name_only,-2,.); %put &=dsn &=valid ; %put &=name_only &=options; %put &=libref &=memname ; ... View more

What do you mean by "the macro should return the value of tier_5"? In general the macro language just generates code.  SAS itself is what runs the code.   In your case the macro is generating a DATA step. So what is the data step that you want to run?  Type that code out by hand and then you can begin to work on creating a macro that generates the code you want.    Do you even need to generate any code?? (and hence do you even need to create a macro??)   Why not just use an ARRAY? Perhaps something like this?   data test; merge v (in=a) trans (in=b) ; by site_number; if a; array dt_ [7]; array Tier_ [7]; do index=dim(dt_) to 1 by -1 until (not missing(applied_tier)); if .Z < dt_[index] <= start_date then applied_tier = tier_[index]; end; run; Note that you current macro logic is just going to generate 7 assignment statement as part of the data step: MPRINT(TEST): applied_tier = tier_7; MPRINT(TEST): applied_tier = tier_6; MPRINT(TEST): applied_tier = tier_5; MPRINT(TEST): applied_tier = tier_4; MPRINT(TEST): applied_tier = tier_3; MPRINT(TEST): applied_tier = tier_2; MPRINT(TEST): applied_tier = tier_1; That is because the condition in your %IF statements is TRUE for all values of the macro variable I.   dt_&i ne . A string starting with the letters dt is never never going to match a period. start_date ge dt_&i And a string starting with s is always greater than a string starting with d.     ... View more

Those values also work. data have; input dob date_01 wrong ; cards; -1 23386 . -1 23386 . 0 22551 . 0 22551 . 0 22551 . 0 22551 . 0 22551 . 0 22551 . 0 22551 . 0 22551 . 0 22551 . 0 22551 . 0 22551 . 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 9 19345 52 9 19345 52 9 19345 52 9 19345 52 9 19345 52 9 19345 52 ; proc sort nodupkey; by _all_; run; data want; set have; format dob date_01 mmddyy10.; right = int(yrdif(dob, date_01, 'AGE')); run; proc print; run; Results Obs dob date_01 wrong right 1 12/31/1959 01/11/2024 . 64 2 01/01/1960 09/28/2021 . 61 3 01/09/1960 06/05/2007 47 47 4 01/10/1960 12/18/2012 52 52 Check all of the steps in your log.  Make sure they completed and updated the output dataset. Make sure you are actually looking at the updated dataset.   ... View more

Something else is the problem.  Those dates work fine. data have; input dob :mmddyy. date_01 :mmddyy. wrong ; right = int(yrdif(dob, date_01, 'AGE')); format dob date_01 yymmdd10.; cards; 1/1/1960 4/28/1987 . 1/1/1960 9/28/2021 . 1/10/1960 12/18/2012 52 1/9/1960 6/5/2007 47 12/31/1959 1/11/2024 . ; Results Obs dob date_01 wrong right 1 1960-01-01 1987-04-28 . 27 2 1960-01-01 2021-09-28 . 61 3 1960-01-10 2012-12-18 52 52 4 1960-01-09 2007-06-05 47 47 5 1959-12-31 2024-01-11 . 64 Most likely the values in the listing you posted are CHARACTER strings that you tried to CONVERT into dates.  Check that step for the source of the errors. Most likely it either read the strings using the wrong informat (or wrongly placed so that it read only some of the string or parts of the strings next to the date).  Or perhaps there were tabs or other invisible characters in the strings that cause the conversion to fail.   Note that if they were DATE values printed with the MMDDYY10. format they would look like this instead: Obs dob date_01 wrong right 1 01/01/1960 04/28/1987 . 27 2 01/01/1960 09/28/2021 . 61 3 01/10/1960 12/18/2012 52 52 4 01/09/1960 06/05/2007 47 47 5 12/31/1959 01/11/2024 . 64 Notice how this listing is showing the leading zeros in single digit days and months that was missing in the text strings you posted.   ... View more

@Hakob wrote: How do you find the 3rd highest salary using a self-join? Can you provide more context please? Why SQL?  There are better tools for this type of question. ... View more

It didn't.  It created it from the actual dataset where the observations have values for both variables so you can count how many observations had GENDER=F and JOBCODE=MECH02.   Note that it created the first two REPORT the same way.   I try to avoid using the term "table" which has become overloaded with meanings.  Some people use it to refer to datasets instead of the original meaning of a tabular report like the ones you showed.     ... View more