When running a group by, one typically includes any variables in the GROUP BY that aren't summarized in the SELECT clause. However, if one field is unique (or a primary key), it can be costly to add min/max/other summary to ensure the query is only grouped on that unique variable.   any_value exists as a solution to this in other SQL variants: MySQL, Snowflake SQL, Google BigQuery, etc. ... View more

Currently proc gproject issues a warning if it doesn't clip anything. Please add the option to suppress this warning message.   Right now to avoid this message I have to add a single coordinate based somewhere I know I don't want mapped.   WARNING: The map data are completely contained within the specified clipping region. WARNING: Data NOT clipped due to reasons noted above. ... View more

Hi team,   We have a AWS IAM Compliance while uploading a file into S3 bucket of adding tag. Would like you to add this as feature for PROC S3 below is example from AWS CLI aws s3api put-object --bucket <bucket_name> --server-side-encryption aws:kms --ssekms-key-id <arn_key_id> --key <filename> --region us-east-1 --tagging <value>   Thanks Harry ... View more

https://communities.sas.com/t5/SAS-Data-Management/How-do-I-get-regular-expressions-functions-in-PROC-FEDSQL/m-p/472453#M14809   PROC DS2; package regexp /overwrite=yes; method match(varchar(1024) pattern, varchar(1024) string) returns varchar(1024); return prxmatch(pattern, string); end; endpackage; run; QUIT; PROC FEDSQL; CREATE TABLE foo AS SELECT regexp.match('/[0-9]+/', id) AS test1, regexp.match('/[A-Z]+/', id) AS test FROM someDatabase.someSchema.someTable; QUIT;   With an error message of:   ERROR: Access Violation occurred during PREPARE! Access Violation? is the error that needs to be better explained. ... View more

If I export a dataset with more than about one million records to Excel, it won't work because that is too much for Excel 2010 to handle. Can proc export determine this before doing the export, rather than processing the export and after some time breaking, with the error ERROR: Too many records for the output file?   This would save time if a dataset that is too large is exported, and save space - for the current process I'm building, I now see a dataset_name.$$1 file that's 120 MB.   Another suggestion is to make this a warning, or have an option to change this from an error to a warning. ... View more

We currently have the REPEAT function, which repeats a given string n+1 times, where n >= 0.  Thus, the return value will always contain the given string at least 1 time.   It would be convenient to have a version of REPEAT that repeats the string just n times, where n is allowed to be nonpositive.  When n is nonpositive, the return value would be a zero-length string -- i.e., very analogous to how SUBSTRN treats a nonpositive length argument.   I would propose calling this new function REPEATN, which seems to be in line with SAS's naming convention for functions related to zero-length strings, such as SUBSTRN, TRIMN, and LENGTHN.   My reasoning for this proposed new function is that there are often situations where a given string needs to be repeated 0 or more times, not 1 or more times.  Due to REPEAT always repeating the string at least once, a special check needs to be done for the case of 0 repeats.   I'm proposing that REPEATN repeats the given string n times instead of n+1, but others may find it preferable to stick with n+1 since that is how REPEAT works.  Either way, the primary benefit of REPEATN would be the ability to get back a zero-length string, made possible by expanding the allowable range of values for n.   Alternatively, instead of a new function, the current REPEAT function could be modified to accept negative values for n in order to return a zero-length string, although I was hesitant to propose that since it could break existing code.   Here is an example showing how REPEATN could lead to simpler, easier-to-read code:     * Goal: Left-pad strings with 0 to length 9. * Note: This is of course not the only way to solve the given task -- * it's just meant to show an example usage of REPEATN. ; * Create some test data. ; data demo; length id $9; input id 1-9; datalines; 1 12 A23 1B34 12C45 123D56 1 34E67 12 45F78 123 56G89 run; * REPEAT requires a special check to make sure the length is not already 9. ; data demo2; set demo; if lengthn(id) < 9 then id2 = repeat('0', 9 - lengthn(id) - 1) || strip(id); else id2 = id; run; * REPEATN works for any starting length. ; data demo3; set demo; id2 = repeatn('0', 9 - lengthn(id)) || strip(id); run;     ... View more

It would be wonderful if the color of the text was retained when you saved the SAS log as an RTF file using "Save As" or PROC PRINTTO. It looks like there might be some ways around this by editing configuration files but I think it should be the default behavior or at least an option the user can change when in the program.   There has been some discussion about this in the past:   https://communities.sas.com/t5/Base-SAS-Programming/Preserving-Color-Coding-in-Log-Window-when-saving-log-to-a-file/m-p/7914/highlight/true#M182 https://groups.google.com/forum/#!topic/comp.soft-sys.sas/SkTR6bmBSEk ... View more

It would be nice to have a feature in EG that allows you to skip the current data step/procedure while SAS is running entire projects/programs.   Reason being, a certain step might be taking long to run and the results of which don't affect the data steps that follow, so this feature would allow you to skip the current step/procedure and move on with the project/program.   Currently you would have to stop the entire run process, comment out the irrelevant data step(s) and start the run from the beginning again - especially in cases when datasets are being overwritten constantly, as in these cases you have no choice but to run the entire project again from the start if you decide to stop the run mid-way. ... View more

As discussed in this SAS whitepaper, it would be neat if a user could specify a keyword to appear in a specified color in the log. Like how NOTE:, WARNING:, and ERROR: do now.   Here's a screenshot from the paper for a visual: ... View more

I'd like to see an option added to PROC PRINT that would print to the SAS log the total number of observations in the SAS dataset before filtering.  This information could easily be queried from DICTIONARY.TABLES and echoed to the SAS log.   For example:     proc print data=younameit (obs=7) totobs; title 'younameit'; run; NOTE: There were 7 of 34417 observations read from the data set YOUNAMEIT. NOTE: PROCEDURE PRINT used (Total process time): real time 0.06 seconds cpu time 0.07 seconds Thanks,   Dave   ... View more

...should work on datetime fields without having to use the datepart function.  day(datetime1) should work.  Also, date formats should work on datetime fields.  Date9, for example, should show the standard ddmmmyyyy display. ... View more

Currently, when SAS exports a file to Stata format, all variable names are converted to lower case. Stata variable names are case sensitive, so often users wish to control the control this. ... View more

I often find myself creating slightly different variables from existing variables. It would be useful to have a facility to enable inheritance of attributes.   For example, I might have a variable named XYZ where missing has been coded as -9, and I need to create a new variable (XYZm) where I am going to convert -9 to . (SAS missing). I'd like to be able to type something like:   XYZm = inherit(XYZ); if XYZ = -9 then XYZm = . ; ...etc.   This would be particularly useful where the attributes have been set in another program, and copy/paste of code is not straightforward.   ... View more

Hey guys,   can we have a function named lead which does the opposite of lag.   data example; input a b; cards; 1 2 3 4  5 6 ; run;   data want; set example; c=lag(b); d=lead(b); run;   Ouput should look something like this   a b c d 1 2 . 4 3 4 2 6 5 6 4 .   Thanks ... View more

It would be nice to have a place where users can find information on how to make their outputs WCAG 2.0 compliant. Currently, I've been forced to cobble together tips from white papers, but I know enough knowledge exists to consolidate and centralize this info.   Cheers ... View more

Hi Everyone,   I am currently developing some basic routines to find multiple copies  (some 'd say duplicates ) between SAS Datasets, not inside. Here is one possible blueprint for solving this issue.   Problem situation : cloning is not universal   We have tens of thousands of SAS tables (V9) with many candidates for master + n? copies (usually 1 master +  1 copy, sometimes 2 or more copies). As far as I know, the tables were copied using Proc Copy - unfortunately - not any system tool (cp, copy) which would have saved us - the admins - precious time. Alas, Proc Copy even with clone option doesn't provide a bit-perfect copy : try it for yourself, a sas table copied with Proc Copy is not like a file cloned at filesystem level :   => md5 hash keys for two *.sas7bdat files are different even if Proc Compare displays exact identity between the 2 tables.   a SAS dataset is cloned with reference to its content 'sas-wise' : descriptor (attributes values) + data, whereas a file is cloned as regards its sequential bits at block level 'storage-wise'. The page bounds defined at SAS level might not be exactly equal between the two copies, I suspect, but that doesn't matter for SAS Engine (V9). Even in the case of having two data portions are exactly equal, if some table attributes differ in any sense (e g a variable name, label or format/informat etc.) then we should declare the two copies unequal, obviously.   New tool : an optional Table Digital Signature   Considering the lack of tools at my disposal, I am suggesting to provide a new feature for SAS dataset (V9, CAS) :   let's call it, for instance, a Table Digital Signature TDS. A question of definition immediately arises :   - is this new key to assess unique Tables comprising Descriptor + Observations ? -  or Observations only ?   The latter looks more useful to me in practice.   How - and When - to compute the TDS   The TDS would be based upon some hashing algorithm ; sequence of n bits { bit, bit ... , bit } => 1 Hash key like MD5 or SH1, or some other non deterministic tool like a Bloom Filter, for instance, in order to scale up appropriately  ('linearly') the extra computation time needed : a TDS computed at O(n^2) or, at any rate - beyond O(log n), for instance, would be utterly unusable in practice on large datasets.   Usually, a SAS engine is used sequentially from to top to bottom against a SAS dataset (a data set OBS option is thus an exception) and random or direct access (SET instruction with Point) is rather the exception than the rule : why not use this complete pass through the data to compute the TDS with an efficient single-pass (multi-threaded) algorithm ?   Another idea to allocate the TDS computation time adequately might be to add it to the Compress/Uncompress time (if the Data set is compressed, of course) : in a perfect world, this extra stage could cost only some marginal fraction of elapsed time.   Major difficulty : how to reconcile TDS with partial modifications ?   Like the cost of maintaining an index, any partial (in-place) modification of the dataset :   - Proc Datasets + Modifiy - Data + If + Output - Proc Append etc.   would require a recalculation of the TDS. Therefore, adding some non marginal extra time to the modification  if the modified part is small compared to the overall table size : is there some associative hashing function already available to take care of this ? By associative, here (the exact technical term might be different), I mean some strong property like :   Hash(Big Part of table A) + Hash(Small Part of table A) = Hash(full table A)     See https://www.quora.com/Are-there-any-strong-associative-hash-mixing-functions   How to store and retrieve the TDS value   Like the Sort flag, the TDS could be stored ('embedded') in the table Descriptor portion and displayed with Proc Contents and Dictionary View (Vtables) if available.   How to enable/disable the optional feature   Of course, there should exist some System Option to Enable/Disable the TDS calculation at session/library/table level.    Afterthought : clone signatures aren't perfect   A minor trouble with this kind of feature would be the legal status of the TDS calculation : if false positive (or negative) errors are in theory > 0 (eg hash collisions probability > 0) then this would restrict the usage of such TDS to some kind of only partial (non conclusive) evidence. Taking this into account, I am quite confident this would definitely improve our SAS usage no matter what ! 🙂   If you've liked the idea, thanks for your support ... View more

According to SAS Documentation, "An analysis variable is a numeric variable that is used to calculate a statistic for all the observations represented by a cell of the report." I would love to have some summary functions for character variables as well, such as coalesce or count. Example Use Case Given a dataset like this: Be able to create a report like this:   data toprint; input groupvar $ acrossvar $ charvar $ numvar; datalines; Group1 Across1 A 1 Group2 Across1 Q 4 Group1 Across2 T 1 Group2 Across2 F 7 ; run; /* The desired report is easily created for a numeric variable */ proc report data=toprint; column groupvar numvar,acrossvar; define groupvar / group; define acrossvar / " " across; define numvar / " " analysis sum; run; /* But for a character variable you can only get a "stairstep" output */ proc report data=toprint; column groupvar charvar,acrossvar; define groupvar / order; define acrossvar / " " across; define cahrvar / " " display; run; /* The desired functionality might look something like the following */ /** This code does not work **/ proc report data=toprint; column groupvar charvar,acrossvar; define groupvar / group; define acrossvar / " " across; define charvar / " " analysis coalesce; run;   In a pharmaceutical setting, groupvar might be usubjid, you might have parcat1 and param as across variables, and charvar might be avalc. This functionality would allow you to create a wide format listing of character data with very little work.   ... View more

We already use HPGLM node in our SAS Enterprise Miner (SAS E.M.) 15.1 to develop our price and fraud models. And they work on SAS E.M. But, to score them in our production environment, we use JAVA (our environments are FICO Blaze). Recently, we had problems in this exported code. We reach your Technical support team (track 7613643392) and they can't help us. Their answer was: SAS doesn't support this export (Java score code) in this Node (HPGLM). It doesn't sound good to me. Usually, if one node contains this option (the export option is there). Further, we have many models in HPGLM using this score exported to Java and they are working o.k. 93% of our models work with this option (only 2 fails on almost 30 models). And, We know how it should be corrected (we send all files through the Support track). It seems that it would be one simple change on the generated java code. The variable initialization. Just one row of code. For us, as customer, it seems that it would be simple to SAS to correct this code and make HPGLM node supportable by Java export code. And it would be great! As we have many models and many of them in HPGLM node, if it wouldn't be corrected (and with support for this node and this score export task) we will leave our SAS E.M. usage (for all of our Modeling tasks) to addopt other tool where we can use multivariate model and export to Java. ... View more