The below generates a dataset, one row per original columns, with the variable name, and a "Yes" or "No " value for DUPE_FOUND.   It makes a hash table for each column, which allows checking for duplicates.  But once a duplicate is found for a given column, three things happen: (1) DUPE_FOUND goes from "No " to "Yes", (2) that column is no longer examined, and (3) its hash object is deleted, freeing up memory.   data want (keep=varname dupe_found); set have end=end_of_have; array cols_to_check {*} _numeric_; declare hash h ; /*Reserve the name H for hash objects, not a variable name*/ length varname $32 dupe_found $3; if _n_=1 then do; declare hash dupecheck (ordered:'A'); dupecheck.definekey('varname'); dupecheck.definedata ('varname','dupe_found','h'); dupecheck.definedone(); declare hiter hd ('dupecheck'); dupe_found='No '; do v=1 to dim(cols_to_check); /*Make a hash for each column*/ varname=vname(cols_to_check{v}); h=_new_ hash (); h.definekey(varname); h.definedata(varname); h.definedone(); dupecheck.add(); /* Add this hash & varname as dataitem to dupecheck*/ end; end; do while(hd.next()=0); if dupe_found='Yes' then continue; if h.check()^=0 then h.add(); else do; dupe_found='Yes'; dupecheck.replace(); h.delete(); end; end; if end_of_have then do while(hd.next()=0); output; end; run; ... View more

Do you need the actual cardinality of each column, or do you just need to determine if there are ANY duplicates for each column?   If it's the latter, then you can program a data step with a hash for each column.  When a duplicate is found, you can stop profiling that column and delete the corresponding hash object, freeing up memory for use on the other columns.   Of course, if there are very few columns with a duplicate, you'll probably end up exhausting memory before processing the entire data set.  Then you'd have to loop over subsets of columns that don't exhaust memory.   ... View more

@Ronein wrote: The Gini coefficient of what? Credit score Model    What are you using to rank the population? PD values    And what is the measure whose concentration over that population you are using? what do you mean?? My questions cited above were posted in 2021.  Upon re-reading this thread, here is what I recall by my question "what is the measure whose concentration over that population you are using?"   The Lorenz curve that I presume would display the underlying distribution for the Gini coefficient needs a horizontal measure the (cumulative % of population) and a vertical (cum % of "measure whose concentration ...").  So I take the latter to be PD values.  And I presume your horizontal is just based on a count of observations in your dataset. ... View more

  @IceFire wrote: Below is the log from SAS system. The total rows for "import_file" is 1855371 How do you know that the total row count is 1,855,371, given that your log below reports 1,856,307 records?  (I presume that"import_file" above refers to the same file as "imported_file" below).   NOTE: The infile 'imported_file' is: Filename=imported_file, Access Permission=-rw-r--r--, Last Modified=27Dec2024:17:59:32, File Size (bytes)=1642040648 NOTE: 1856307 records were read from the infile 'imported_file'.   ... View more

@Astounding    "PROC RANK processes the 0 salary values into percentile 1. "    and "Run the PROC RANK on the salary > 0 observations, then put the groups back together again."   The latter is what my code effectively does using the "where=(salary>0)". filter to the data set submitted to PROC RANK, which of course necessitates the subsequent recovery of the unranked observations.   But instead of a separate WHERE statement it's coded as a data set name parameter.  I suspect you may have missed that.  Welcome to a club I frequently visit.     ... View more

@Ronein wrote: Hello, Yes-  5,000 will go into the same percentile (leaving some other percentile with 0 observations) What is the way to do it? And which percentile will ties be assigned: lower bound?  upper bound? mid-point?   Let's say you want the mid-point, and data is sorted by ID:   proc rank data=have (keep=id salary where=(salary>0)); groups=100 ties=mean out=need; var salary; ranks salary_pctile; run; data want; merge have need; by id; if salary>0 then salary_pctile=salary_pctile+1; else if salary=0 then salary_pctile=0; run; Untested in the absence of sample data. ... View more

Once the data are sorted by custid yyyymm (and no gaps in YYYYMM), then a RETAIN statement provides a nice way to dynamically update the maximum consecutive qualifying balances, and output the result for each custid.   data want (keep=custid max_consec); set have; by custid yyyymm; retain max_consec; if first.custid=1 or balance>0 then call missing(consec,max_consec); if sign(balance)=-1 then consec+1; else consec=0; max_consec=max(max_consec,consec); if last.custid; run;   ... View more

Without getting into a coding example, why not run PROC RANK (with groups=100) on the subset of salaries > 0, which can yield an output dataset with ranks of 0 through 99?   Merge that data with the original data, increasing rank by 1 for the positive salary subset, and assign a rank of zero to cases with salary=0.  You would then have rank values of 00 through 100. ... View more

@Ronein    Nice question.  Where is the sample data, to help us help you? ... View more

You effectively want the most frequent word in a set of words, all of which are in the same observation.  This can also be done using a single character variable containing that list of words:   data want (drop=_: i); set have; length _word_list $100 _word $3; _word_list=catx(' ',of hp_:); do while (_word_list^=' '); _word=scan(_word_list,-1); _freq=0; do i=1 to countw(_word_list); if scan(_word_list,i)=_word then _freq=_freq+1; end; if _freq > mode_freq then do; mode_freq=_freq; mode=_word; end; _word_list=tranwrd(_word_list,trim(_word),' '); end; run;  Note that the assignment of a value to _WORD using _word=scan(_word_list,-1); continually takes the rightmost available value (i.e. the latest) instead of the leftmost.  Then, if there is a tie in the frequency of two words, the subsequent  statement if _freq > mode_freq then do; guarantees that the most recent (rightmost) word with the tied frequency is assigned, per the request. ... View more

Your data are sorted by ID.  You want new variable END_DATE1 to take the value of END_DATE only for ID's than have no instance of missing END_DATE, correct?   If so, then   data want; merge have (where=(end_date=.) in=inblank) have; by id; format end_date1 date9. ; if inblank=0 then end_date1=end_date; run; The temporary dummy variable INBLANK will be zero only for those ID's with no rows with missing END_DATE.   ... View more

@quran    You may have missed the objective of the original poster, or perhaps didn't notice the structure of the data.   The OP wanted "every country" in the two specified regions.  But unfortunately there is no Excel column (thus no imported SAS variable) that identifies the region for every observation.  Instead, there are rows with the region identified in the variable Reporting_Country_Territory_Area.  Luckily the word "Region" appears in this variable for all such rows and only those rows.   Following those rows are a series of countries (also identified in Reporting_Country_Territory_Area) that belong to the identified region.   Your code retrieves only the two rows that identify the desired regions, but none of the rows following them.  That is what @Kurt_Bremser 's code addresses.   If you intend to make the subset for WANT directly from COVID19 imported dataset, then:   data want; set covid19 ; length region $47; retain region; if findw(Reporting_Country_Territory_Area,'Region') then region=reporting_country_territory_area; else if region in ('Western Pacific Region' 'South-East Asia Region') then output; run;   Or you can just create the COVID19_EXPANNDED data set:   data covid19_expanded; set covid19 ; length region $47; retain region; if findw(Reporting_Country_Territory_Area,'Region') then region=reporting_country_territory_area; else output; run; Then you could use the WHERE filter that you propose.       ... View more

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