@Wolverine    Don't be shy.  Mark your explanation as the solution, so we all know the problem has been solved. ... View more

Here is what I think you want.   You apparently want to create a new variable CLINIC, such that     If          all the non-blank values for clinic_name1, clinic_name2, clinic_name3 have the same value,         and         all the corresponding prior_span values are "1"     then         the new variable gets the common clinic_name value     otherwise         it is left blank.   If so, then  data want (drop=i); set have; array cn {3} $ clinic_name_1- clinic_name_3; array ps {3} $ prior_span1 - prior_span3; if prior_span1='1' then clinic=clinic_name_1; do i=2 to 3 while (cn{i}^=' '); if cn{i}^=cn{1} or ps{i}^='1' then clinic=' '; end; run; This rule, in the case of your sample data, generates a name for only 1 observation (out of 12).  So I think we may need more clarity from you on the rule to use in assigning a value to CLINIC. ... View more

Why does index_date have values like "V0182"?  That is not a date value, formatted or unformatted.   I misread the index_date variable.  Here is a single step solution.  It assumes data are sorted by ID the index_date=0 record is always the last one for a given ID.   data want;; set db1 (in=firstpass) db1 (in=secondpass); by id; retain death_date; format death_date date9. ; if first.id then death_date=. ; if firstpass then death_date=max(death_date,discharge+1); if secondpass; if index_date=0 then call missing(status,death_date); run;   m ... View more

The problem is that what you want is to double count in the ALL row (actually "up to ten" count), for which tabulate does not provide a mechanism.   I suggest a data set VIEW with a new variable, to be included in the proc tabulate.   data vneed / view=vneed; set have (keep=columnvar rowvar1-rowvar10); row_count=n(of rowvar:); run; proc tabulate data=vneed; class columnvar rowvar1-rowvar2; var row_count; table rowvar1-rowvar2 row_count*sum='Col Total' ,columnvar; run; ... View more

For each permno you want LMV, defined as current daily MV for the first month for all other month, the MV of the last trading date of the prior month. This should work:   DATA LMV (drop=_: nxt_:) ; SET MV (keep=permno); BY PERMNO; merge mv mv (firstobs=2 keep=date rename=(date=nxt_date)) ; RETAIN LMV .; if first.permno then _monthnum=1; if _monthnum=1 then lmv=mv; output; /* if this is the last trading date of the month, prepare for next month */ if intnx('month',date,nxt_date)>0 then do; _monthnum+1; lmv=mv; end; RUN; The initial code (below) would consistently, just for the last date of each month, mistakenly use the current MV for the LMV.  That has to be delayed by one date, as above.   DATA LMV (drop=_: nxt_:) ; SET MV (keep=permno); BY PERMNO; merge mv mv (firstobs=2 keep=date rename=(date=nxt_date)) ; RETAIN LMV 0; if first.permno then _monthnum=1; if _monthnum=1 or intck('month',date,nxt_date)>0 then lmv=mv; /* If first month, or end-of-current month*/ if intnx('month',lag(date),date)>0 then _monthnum+1; /*Starting a new month */ RUN;   ... View more

Here's a way to use proc freq to generate the results you want (for each auditor/sic generate the proportion of the auditor clients' total assets_squared arising from the sic):   I use the sashelp.class, modified, as an example.  I gave auditor    data class; set sashelp.class (rename=(name=gvkey sex=sic)); auditor=mod(_n_,3); assets_squared=age*age; if _n_= 10 then assets_squared=weight*weight; drop age height weight; run; proc sort; by auditor; run; proc freq ; by auditor; table sic / out=want (rename=(count=assets_squared)); weight assets_squared; run; It uses proc freq, because that proc generates percentages of assets_squared for each sic within each auditor.  You can add a "noprint" option to the proc freq statement to get the WANT dataset without the printed table.     You could sort the WANT dataset by     auditor  descending percent and then the first obs for each auditor will be the most "specialized" sic for that auditor.   ... View more

Editted note:  The most important statement in my original email below is "This program is untested".   The two statements following the "IF FIRST.PERMNO" test should have been reversed, and I have done so now.  The "call missing" should precede assignment of the value for _first_date_qualified.     If a permno has a highest (adjusted) PRC of, say, 100 over the first year (the "unqualified" period), but afterwards never exceeds 80, will H be 100? or 80?    In other words, is the unqualified period price data used to track the value of H  (and L)?   This matters, because if it is NOT used (i.e. H=80), then each and every PERMNO will have an H event on the first date after the unqualified period.  The program below assumes the unqualified period price values are included in determining a new max or min during the qualified period:   data want (drop=_:); set have; by permno; retain _first_date_qualified . /* One year after first date */ _H . /* Highest Price since day 1 */ _H_date . /* First date with current value of H */ _L . /* Lowest Price since day 1 */ _L_date . /* First date with current value of L */ ; length portfolio $11 ; /*Each obs will be "Unqualified","Max","Min", or "Comparison"*/ if prc^=. then prc_adjusted = abs(prc/cfacpr) ; if first.permno then do; call missing(of _:); _first_date_qualified=intnx('year',date,1,'same')+1; end; if _H=. then _H=prc_adjusted; if _L=. then _L=prc_adjusted; if prc_adjusted ^=. then do; if prc_adjusted > _H then do; /*If New High ...*/ _H=prc_adjusted; _H_date=date; portfolio='Max'; end; else if prc_adjusted < _L then do; /*If New Low ...*/ _L=prc_adjusted; _L_date=date; portfolio='Min'; end; end; if date >= _first_date_qualified then do; H=_H; H_date=_H_date; L=_L; L_date=_L_date; if portfolio=' ' then portfolio='Comparison'; end; else portfolio='Unqualified'; format H_date L_date yymmdd10. ; run; This program is untested.  It produces, for each observation, six new variables:  PRC_ADJUSTED (adjusted by cfacpr), PORTFOLIO ("Max", "Min", "Comparison", "Unqualified"),  H, H_Date, L, and L_date.  H_date and L_date are the earliest dates in which a new high or low is encountered. If you have a new high on a given date, and a set of later dates with the same PRC, only the first instance has portfolio="Max".    So you can have a set of dates with constant PRC_ADJUSTED=H, but only the first one has H_DATE=DATE.         ... View more

The reason your code produces sd_south_north=8.485 (instead of 18.475) is the same as the reason it produces sum_south_north=116 (instead of 126).  That's because it is treating north+south as a single calculated value for each row.  This means It excludes row 1, because north is missing, making north+sourth missing. It provides statistics on the sum calculated for each row, not the individual components of the sum.   I.e. the standard deviation is   std(40+12,50+14)=  std(52,64) = 8.485.   If you really must use sql to statistically treat two columns as if they were one long column, then you will have messy expressions, like   dm 'clear out'; data have; input id south north west east; cards; 1 10 . 15 9 2 40 12 10 14 3 50 14 13 13 run; proc sql; create table want as select * ,sum(south)+sum(north) as sum_south_north ,(sum(south)+sum(north))/(count(south)+count(north)) as mean_south_north ,sqrt( ((sum(north**2) + sum(south**2)) - (sum(north)+sum(south))**2 / (count(north)+count(south))) / (count(north)+count(south)-1) ) as std_south_north from have; quit; For a couple of column-pairs, this might be tolerable, but if there are lots of pairs, then you are asking for trouble.   You're better off reshaping the data and then generating the STD more directly. ... View more

@Tom wrote: @Irenelee wrote: variable names are PERMNO DATE PRC CFACPR SHROUT CFACSHR H L Ok.   Is this supposed to be stock market data? So what do those names mean?   Clearly the H and L are NOT the same as the H that you talked about before. @Tom   I can answer the easy part.   Yes, it is stock market data distributed by CRSP (Center for Research in Stock Prices). PERMNO                       Unique ID number assigned by CRSP to each stock. DATE                              Trading Date PRC                               The stock closing price on DATE.   If PRC is negative, there was no trading on that date, so PRC is the "negative" of the closing BID/ASK midpoint on that DATE CFACPR                       An adjustment factor, that when applied to PRC allows consistent comparison of prices over the entire stock history, even when that stock has multiple events such as stock splits. SHROUT                       Shares outstanding.    In general, market value= SHROUT*PRC CFACSHR                     An ajustment factor for SHROUT, similar to the function of CFACPR when dealing with PRC H and L                         are not standard CRSP variables - as you surmise probably a user-generated value (maybe a high and low over some time period. Data from CRSP are always provided to users sorted by PERMNO/DATE, and no DATE has more than one observation (edited addition: for a given PERMNO).   The hard part:   @Irenelee -help us help you.  Please show a sample of exactly what the resulting data set should look like.  I too, do not understand how you want highs recorded.  (Once per PERMNO? once for every new historic high?  Do you want one obs per day in your output dataset?    And then you have mentioned 52-week ranges.  Do you want, for every observation, to also have a rolling 52-week range?) ... View more

@Tom    A very nice use case for the undocumented "monotonic" function in PROC SQL. ... View more

It looks to me like you want to create pre-1963 historic adjusted highs and low, and then compare each post-1962 observation to those historic extremes.  You can do that in one pass of the crsp data, which is typically already sorted by PERMNO/DATE.   data want ; set crsp (where=(shrcd IN (10,11) and exchcd in (1,2,3,31,32,33)); by permno date; prc_split_adjusted =abs(prc)/cfacpr; retain ohh ohdate /*Pre-1963 historical adjusted high and date of its latest occurence*/ ohprc ohcfacpr /*Nominal PRC and CFACPR on OHDATE */ oll oldate /*Pre-1963 historical adjusted low and date of its latest occurence*/ olprc ohlfacpr /*Nominal PRC and CFACPR on OLDATE */ ; format ohdate oldate date9.; if first.permno then call missing(of oh:, of ol:); if date<'01jan1963'd then do; if prc_split_adjusted=max(prc_split_adjusted,ohh) then do; ohh=prc_split_adjusted; ohdate=date; end; if prc_split_adjusted=min(prc_split_adjusted,oll) then do; oll=prc_split_adjusted; oldate=date; end; end; if date>='01jan1963'd; /* This is a "subsetting IF" */ ** Do your code for comparing post-1962 to OHH and OLL here **; run;     ... View more

@DrAbhijeetSafai wrote: @chrej5am  Thanks!   Method = relative option in proc compare statement helped me. Thanks once again.   Thank you.   - Dr. Abhijeet Safai For the benefit of the rest of us, please mark the response with the needed answer as a solution.  Thank you. ... View more

Apparently Excel stores time values as fractions of a day,  where 06:00:00 displayed is stored as 0.25, 12:00:00 noon stored as 0.5, etc.  Those fractional values are what is brought by PROC IMPORT.   But SAS records time of day as number of seconds after midnight (06:00:00 is stored as 21600, noon is stored as 43200).  So you have to do two things after the proc import:   Multiply each value by 86400 (number of seconds in a day). Reformat the variable using a TIME format.   data work.svoi2014_formatted; set work.svoi2014; array timvals {*} tobito--retiradafuneraria; do i=1 to dim(timvals); timvals{i}=timvals{i}*86400; end; format tobito--retiradafuneraria TIME8.; drop i; run;   ... View more

@pdick2    You want  all cases for ID's that have sepsislabel always at zero, or that change from zero to one. You earlier said your data has no instance of going from one to zero. If the above are both true, then you simply want all ID's that start with zero,  That's a simple task.   If the data are sorted by PATIENT_ID/Hour, then:   data want (drop=_:); retain _keep ' '; set have; by patient_id ; if first.patient_id=1 then _keep=ifc(sepsislabel=0,'Y','N'); if _keep='Y'; run; If the data within patient_id are sorted by HR, and the patient_id's are grouped, but not sorted, then change the BY statement to: by patient_id notsorted;     Now if the data are sorted by HR, you don't have to re-sort by patient_id/hr.  And it doesn't matter what the order is within each HR value.  So, if sorting by patient_id/hr is expensive, then you can use a hash object:   data want (drop=_:); set have; by patient_id; length _keep $1; if _n_=1 then do; declare hash h(); h.definekey('patient_id'); h.definedata('_keep'); h.definedone(); end; if h.find()^=0 then do; /*If this is first instance for this ID ...*/ if sepsislabel=0 then _keep='Y'; /*Check the initial sepsislabel value */ else _keep='N'; h.add(); /*Put it in the hash object */ end; if _keep='Y'; run; These are untested. ... View more