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

Extending the CLP approach by @Ksharp, here's a way to do it with a groupBy parameter in the runOptmodel action in SAS Optimization: %let numVars = 6; data mycas.have; input id target var1-var&numVars; cards; 1 10000 -2000 10000 3000 7000 12000 30 2 7000 5000 1500 500 6500 750 . ; proc cas; source pgm; num target; set VARS init 1..&numVars; num a {VARS}; read data have into target {j in VARS} <a[j]=col('var'||j)>; VARS = {j in VARS: a[j] ne .}; var Select {VARS} binary; con SumToTarget: sum {j in VARS} a[j] * Select[j] = target; solve with clp / findallsolns; create data want(drop=j) from [solution j]={s in 1.._NSOL_, j in VARS} var=('var'||j) value=a[j] sol=Select[j].sol[s]; endsource; action optimization.runOptmodel / code=pgm groupBy='id'; quit; proc print data=mycas.want; run;   ... View more

As others have explained, when you use a multi-valued prompt, the values are stored in multiple macro variables.   This paper by Joe Flynn has a section explaining an approach: http://support.sas.com/resources/papers/proceedings11/050-2011.pdf   Based on that approach, I created a function-style macro which returns a delimited list of the selected items:   %macro ConcatenateSelectionList (prompt ,dlm=%str( ) ); /*============================================================================== Macro: ConcatenateSelectionList Abstract: Macro function that returns a delimited list of items chosen in a check all that apply stored process prompt. Description: Stored processes pass CHECK All That Apply parameters in an odd fashion. If parameter is named PROMPT then &PROMPT_Count always exists, holds number of options selected (0-n) &PROMPT1 - &PROMPTN are created if count>=2, if count=1 or 0, they not created &PROMPT is created if count >=1, if count=0 it is not created. Usage: %let sitelist=%ConcatenateSelectionList(site) ================================================================================*/ %local i return ; %if &&&prompt._Count >= 2 %then %do i = 1 %to &&&prompt._Count ; %if &i=1 %then %let return=&&&prompt&i ; %else %let return=&return&dlm&&&prompt&i ; %end ; %else %if &&&prompt._Count = 1 %then %do ; %let return=&&&prompt ; %end ; %else %if &&&prompt._Count = 0 %then %do ; %let return= ; %*null ; %end ; %else %put ER%str()ROR: USER &prompt._Count=&&&prompt._Count ; &return %mend ConcatenateSelectionList;   ... View more

Hello, thank you for your answer. ... View more

thanks for the solution tom. i didn't know that comparison operators can also be used on characters to group the records while characters can also be treated as a range. this is the most simplified solution could come across. ... View more

Hello @Mahis  Please check if the user has access to the location defined for work library. ... View more

You can use Proc Format to tell you about the custom formats/informats you have available.  Example that creates one format and one informat in the Work library and default format catalog. Then  gets the descriptions with the FMTLIB option. Run the below code and see in the description the the $ABC has the words FORMAT NAME, indicating it is an output format and $PDQ has INFORMAT NAME indicating it is used for reading.   You would want to use Library=LIBRARY.cerform in the Proc format statement to examine you custom formats.    /* create a custom format to use later*/ proc format ; value $abc 'abc'='some formatted result' ; invalue $pdq 'pdq'='input result value' ; run; /* get descriptions of the current custom format/informats */ proc format library=work fmtlib; run;  I would quite often have an informat/format pair defined because the INVALUE has different options available such as <some value description> = _error_ to report in the log that the value encountered may not be valid and reports as such in the log, or to create special missing values that a FORMAT could then provide details about when requested. Plus for reading text the INVALUE option UPCASE is very helpful to standardize values so that Abc, ABc, aBc, abC (etc) are all read into the value ABC. ... View more

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

Sorry, I'm not good enough to clearly state the problem and/or solution. That data step performed other functions and when I moved the age determination outside of the problematic data step it worked just fine and that's the most important thing. The data step performed other functions so I'm assuming one conflicted or one of the variables in my function was dependent on that and I was attempting to this age calculation. Moving it outside of the data step eliminated that conflict and it runs just fine. The key concept from Cynthia and Paige was that perhaps my function wasn't to blame (since I did not change it, I just moved it). So it wasn't the particular function at fault, which is what I had focused on. A good reminder to me to check other possibilities.  ... 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

Array that has been suggested is a good alternative. The reason why your macro is not working is because you are using DATA step variables on a %IF condition which is not correct. If you change your code to use an IF statement, you should get the expected results. %macro test; data test; merge v (in=a) trans (in=b); by site_number; if a; %let i = 7; %do %while (&i > 0); if (dt_&i ne . and start_date ge dt_&i) then do; applied_tier = tier_&i; end; %let i = %eval(&i - 1); %end; run; %mend test; options mprint mlogic symbolgen; %test; ... View more

Thanks Tom, dumb me, I saw only the partial data in the mechanics dataset. TYSM!!🎯 ... View more

Hi @Mscarboncopy , maybe you already figured out the answer base on others' reply, but here is my version of code (mainly base on @Tom 's reply), and I think these are effective and efficient solution according to your request. data dtfmt1; input ID DATE datetime16.; datedt16=date; format datedt16 datetime16.; datalines; 1 27JUL25:09:00:00 2 05AUG25:10:00:00 3 15AUG25:08:00:00 4 20AUG25:11:30:00 ; run; proc print data=dtfmt;run; data dtfmt2(drop=date datedt16); set dtfmt1; dated=datepart(date); time=timepart(date); day_of_week=weekday(dated); month=month(dated); day=day(dated); year=year(dated); format dated date9. time timeampm8. day_of_week downame. month monname.; run; proc print data=dtfmt2;run; proc format; picture mydt(default=40) low-high='%F,%c %d,%Y %H:%0M %p' (datatype=datetime); run; data dtfmt3; set dtfmt1; format date mydt.; datechar=put(date,mydt.); run; proc print data=dtfmt3;run;   And @Mscarboncopy , several techniques perhaps you would like to know: 1)if one wants to display a month's number in character month name, monname. format works, similarly, weekday() function returns a number, if one want to display the character weekday, downame. format works (and you do not need to use proc format 1='Monday' etc.), and I just learnt it from @Tom 's reply, 2) use timeampm8. format to reduce the 00:00:00 time format to 00:00, 3)if one would like to display date/time in a specific format, use picture format (and I copied this from @Tom 's reply), and if one would like to further convert it to character string, put(date/time, picfmt.) is the efficient way, and catx() is not a good way to do it because nested functions such as catx(catx()) is a bit too complicated and cost a lot of eyesight. ... View more