I'm not sure I understand exactly what you want.  I guess you want the average of count for each level of RACE, of AGE, and of SEX.  If so, then this would generate a report of those averages:   data a; input date :monyy. age sex race count; format date monyy.; datalines; JAN18 24 0 0 1 JAN18 22 1 1 3 FEB18 19 1 2 1 MAR18 20 0 0 4 MAR18 20 1 4 2 RUN; proc means data=a; class age sex race ; var count; types age sex race; run; ... View more

First, I agree that this problem is best be solved in a PROC REPORT.   But if you must somehow only use SQL, you would have to place a constraint on subranges of the column.  For instance, if you choose to only use values of -1 through +1 to represent -100% through 100%, and use only other values to be presented in comma format, you could do this.   Take the sashelp.demographics dataset, with variables POP (actual population count) and POPURBAN (percentage represented as zero through one), put into the same column.     proc format; value pc -1 - 1=[percentn10.2] other=[comma10.0]; run; proc sql; create table want as select name, 1 as row, pop as column_var format=pc10.2 from sashelp.demographics union select name, 2 as row, popurban as column_var format=pc10.2 from sashelp.demographics order by name, calculated row ; quit;   ... View more

Please show some sample data, in the form of a working DATA step.  Then show us what you want the result of that sample to look like.   Help us help you.     ... View more

You may want more than a flag variable.  Here is code to count the number of earlier admission dates within 2 years (nprior), and the number of later admission dates within two years (nafter):   data counts (drop=_:); set have (in=firstpass) have (in=secondpass); by patient_id; array dates{0:50} _temporary_; if first.patient_id then call missing(of dates{*},_n1,_n2); _n1+firstpass; if firstpass then dates{_n1}=admit_dt; if secondpass; _n2+1; _cutoff=intnx('year',admit_dt,-2,'s'); do nprior=0 by 1 until (dates{_n2-1-nprior}<_cutoff); end; _cutoff=intnx('year',admit_dt,+2,'s'); do nafter=0 by 1 until (dates{_n2+1+nafter}>_cutoff); if dates{_n2+1+nafter}=. then leave; end; run; This assumes data are sorted by admit_dt within patient_id.  Also make sure that the lower bound of the DATES array is zero, and the upper bound is at least 1 more than the maximum number of single-patient admissions in dataset HAVE. ... View more

Do you want to compare each ADMIT_DT to all other admission dates, or only to earlier admission dates?   Here is code that creates two flags, one to indicate whether the prior admission is within two years, and one to indicate whether the next admission is within two years.   This assumes data are sorted by admit_dt within patient_id.   data have; input patient_id admit_dt : mmddyy10.; format admit_dt mmddyy10.; cards; 1 1/3/2012 1 11/21/2017 1 10/2/2018 1 10/3/2018 1 11/12/2018 1 8/21/2019 1 3/13/2021 1 5/2/2021 ; data want (drop=nxt_:); set have; by patient_id; nxt_admit=.; if eod2=0 then set have (firstobs=2 keep=admit_dt rename=(admit_dt=nxt_admit)) end=eod2; prior_admit_flag=first.patient_id=0 and intnx('year',admit_dt,-2,'s')<=lag(admit_dt); later_admit_flag=last.patient_id=0 and intnx('year',admit_dt,2,'s')>=nxt_admit; run;   ... View more

You want a single table that reports a subgroup, and all groups.   Make a new variable called group, which will become an additional CLASS variable in the proc tabulate.   Give GROUP the value 'All Providers' for every observation to be counted (including Hospitalists).  Then, for each Hospitalist, output a second observation with Group='Hospitalists'.    data combined; set ....; *... same code as before in generating all providers; group='All Providers'; output; if attendingspecialty='Hospitalist' then do; group='Hospitalists'; output; end; run; Then you can revise the proc tabulate to something like this: proc tabulate data=combined; class group; class CompanyCode /preloadfmt order=data; format CompanyCode; class DischargeFullDate; format DischargeFullDate dtmonyy7.; table CompanyCode='Aurora Hospital' *group='Hosp. vs All' , DischargeFullDate*rowpctn /printmiss; title '% Inpt Discharges Before Noon'; run;     ... View more

@sasuser_8 wrote: It's elegant but I have another variable in my DB wich is less than 0 without being a percentage 😞  "Less than 0 without being a percentage"?  Why is that a problem?   Did you see that my revised solution did not format negatives as percentages?  You could be even more specific, using:   proc format; value ff 0-1=[percent8.] other=[comma8.0] ; This depends on values of zero through one (and only zero through one) always being intended as percentages.  So far you have not said your data violate that rule. ... View more

Is SAS running on your own PC, or are you accessing SAS on a server, (for instance via sas/connect, or sas enterprise guide connected to a server)?  If you are accessing a remote server, then you may have a communications issue, not specific to SAS. ... View more

The topic title is "multi-format" column.  And the correct answer is a column can't have more than one format.   But it CAN have a multi-component format (as in sometimes %, sometimes integer).  You can't base those components on the value of the VARIABLE column, but it appears that for the odd values of VARIABLE, the M variables all range from zero-through-one, and for all others look like integers greater than one.  So define a different format for each of those ranges, as in:   data sale; input variable m1-m5; datalines; 1 0.52 0.36 0.98 0.74 0.99 2 36 52 2 6 42 3 0.96 0.55 0.44 0.7 0.32 4 65 8 24 6 21 5 0.63 0.36 0.54 0.8 0.7 run; proc format; value ff 0-1=[percent8.0] 1<-high=[comma8.0]; run; proc print data=sale noobs; format m: ff.; run;   yielding:   variable m1 m2 m3 m4 m5 1 52% 36% 98% 74% 99% 2 36 52 2 6 42 3 96% 55% 44% 70% 32% 4 65 8 24 6 21 5 63% 36% 54% 80% 70%     Edit note: the format above is the tweaked version.  It was: proc format; value ff 1<-high=[comma8.0] other=[percent8.0]; run;   And yes, as @PaigeMiller observes, you must know that the rows that should have the percentage format have a range that does not intersect the range of the other rows. ... View more

@bhr-q wrote: I am sorry, that is weird; now it works; sorry again, I was in a rush. Thanks a lot for your answer, That's ok.  But mark your comment as the "solution", so that folks looking for unsolved topics don't needlessly review this one. ... View more

If your data are always structured such that a formula observation is immediately followed by all of its components, then the solution offered by @PeterClemmensen seems to be the most straightforward.   But if the components can be anywhere in the dataset, then you need to have access to all the known score values in the dataset before you can reliably interpret every formula.  @Ksharp 's solution does that.  The only downside I see to the latter is that, for each formula, it loops through all known scores in the dataset ("do i=1 to dim(_x)").  It could be a big loop.  You said you have 250,000 obs, so maybe you have 50,000 formulas and 200,000 known scores to loop through for each formula, yes?.   Here's a way to have every known score available for each formula, but loop only through those scores actually in the formula:   data have; length ID $10. score 8. formula $200.; infile datalines dsd missover ; input ID $ score formula $; datalines; KRI1,,KRI2/KRI3 KRI2,1, KRI3,10, KRI4,,(KRI5-KRI6)*100 KRI5,13, KRI6,16, KRI7,,(KRI8+KRI9)/(KRI10-KRI11) KRI8,13, KRI9,16, KRI10,1, KRI11,10, run; data want (drop=_: i); set have (where=(formula is not missing)); if _n_=1 then do; declare hash sc (dataset:'have (keep=id score where=(score is not missing))'); sc.definekey('id'); sc.definedata('score'); sc.definedone(); end; resolve_text=compress(formula); do i=1 to countw(formula,'-+*/() '); /* For every term in the formula */ _term=scan(formula,i,'-+*/() '); /* Get the term identity, then insert its score */ if sc.find(key:_term)=0 then resolve_text=tranwrd(resolve_text,trim(_term),trim(cats(score))); end; score=input(resolve(cats('%sysevalf(',resolve_text,')')),32.); run; Note that the sc.find(key:_term) method will retrieve the score from the SC hash, but not the ID.  That's because the ID is only in the .definekey method, but not in the .definedata method. ... View more

@Jim_Cooper_hmsa  If you end up finding the solution independent of responses to your SAS community topic, I for one would appreciate seeing the solution.  No harm in marking one's own response as a solution.   ... View more

You didn't show your code using the LAG function, but I bet you used it in the THEN clause of an IF ... THEN statement, which would mean that the lagged values would come from the last observation when the IF condition was true, not necessarily from the immediately previous observation (that's because the LAG function is a FIFO queue updater, not a "lookback").   Here is code that uses FIRST.  and LAG functions suitable for your purpose, which allows you to have a single DATA step solution: data have; infile cards expandtabs ; input empid startdt :date11. rolecode rolegroup $ status $; format startdt date11.; cards; 101 01-Jan-20 231 AD1 A 101 01-May-20 231 AD1 A 101 01-Aug-20 232 AD1 A 101 01-Sep-20 301 . A 101 01-Nov-20 301 . A 102 01-Apr-21 231 AD1 A 102 01-Jun-21 231 AD1 D 102 01-Aug-22 231 AD1 A ; data want; set have; by empid ; retain effective; format effective date9.; if first.empid then effective=startdt; if first.empid=0 and rolecode^=lag(rolecode) and rolegroup^=lag(rolegroup) then effective=startdt; if status='A' and lag(status)='D' then effective=startdt; run;   Notice the second IF statement does not begin as ELSE if.  Otherwise the values produced by the LAG functions would not be synchronized with the immediately preceding observations.   And if you want to have more extensive use of FIRST. and less use of LAG:   data want; set have; by empid rolecode notsorted; set have (keep=empid rolegroup); by empid rolegroup notsorted; retain effective; format effective date9.; if first.empid then effective=startdt; else if first.rolecode and first.rolegroup then effective=startdt; if status='A' and lag(status)='D' then effective=startdt; run; Note that there are two pairs of SET + BY statements.  The second SET rereads the observation read by the first.  I do it that way because the program needs independently constructed FIRST.ROLECODE and FIRST.ROLEGROUP dummies.  If both of those variables had been in a single BY statement, then they would not be independent.  Detection of a FIRST. condition on any given BY variable would force a FIRST. condition on all subsequent vars in the BY statement.   ... View more

@Hoibai :   Please note that @FreelanceReinh's explanation is not an exclusively SAS issue.  It is an issue for all  software on digital computers based on binary arithmetic. ... View more

Given the 19 obs in sashelp.class have only sex='M' or 'F', you want to add two more obs, with SEX='Trans' or 'Other', and all other variables missing. I.e you want 21 obs.   Along the lines of @yabwon 's suggestion but without the proc sort:   data obs1 (where=(sex='F')) obs2 (where=(sex='M')) obs3 (where=(sex='Trans')) obs4 (where=(sex='Other')); length sex $5; set sashelp.class; run; data want (drop=i); set obs1-obs4 end=end_of_data; array genders {4} $5 _temporary_ ('M','F','Trans','Other'); i=whichc(sex,of genders{*}); if i>0 then call missing(genders{i}); output; if end_of_data; call missing(of _all_); do i=1 to dim(genders); sex=genders{i}; if sex^=' ' then output; end; run; This uses a temporary array with all the expected values of SEX.  Any values found in the incoming data are deleted from the array, leaving only those analogous to your zero-obs datasets.  Those are added after the "if end_of_data;" statement. ... View more