About ballardw

ballardw

<Pedantic mode:on> Sun in your subject line and Count in your question are quite different mathematical concepts. <Pedantice mode:off> It is a good idea to describe, better provide some example, the starting data and the desired result from the given data. You should also include the LOG when you get errors such as you code will throw. Copy from the log, open a text box using the </> icon that appears above the main message window and paste the copied text. Example: 24 proc sql nosqlremerge; ------------ 1 WARNING 1-322: Assuming the symbol NOREMERGE was misspelled as nosqlremerge. 25 create table junk as 26 select *, 27 sum(age) as agesum 28 from sashelp.class 29 group by sex 30 ; ERROR: The query requires remerging summary statistics back with the original data. This is disallowed due to the NOREMERGE proc option or NOSQLREMERGE system option. 31 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds My version of SAS also doesn't like the option you specified as NOSQLREMERGE but does use the NOREMERGE option and attempts the code with that option and then errors because of the required remerege. Removing the noremerge to run the code: 48 proc sql ; 49 create table junk as 50 select *, 51 sum(age) as agesum 52 from sashelp.class 53 group by sex 54 ; NOTE: The query requires remerging summary statistics back with the original data. NOTE: Table WORK.JUNK created, with 19 rows and 6 columns. 55 quit; You can copy the code from the above and run it on your system as you should have the SASHELP.CLASS data set available. Does your desired output data want to have the same "sum" value repeated for each value of the groupid variable? If you look at the above created data set JUNK you will see 9 values of 119 for the Agesum variable and 10 of 134 .

ballardw

If you want to display a dash in the date look at the YYMMDDXw format. The X position lets you use a list of characters in that position, B C D N P and S which will then use a Blank, Colon, Hyphen (dash), nothing, Period or Slash as the separator between the elements. Note that custom rolled date, time and datetime formats created with Proc Format are also not going to display properly in graph axis.

ballardw

@adalisaw wrote: I am sure you are wrong. I have been using SAS for at least 8 years. I can use SAS to do what R, Python, SQL, and etc allow me to do. And I love it. BTW, you have not given us your reason (s) why you seem to not like SAS. I think you replied to the wrong thread @adalisaw . If this comment relates to the original post by @ShaneClark you should have initiated the reply by clicking the REPLY button at the base of his post. This is also a fairly old thread to respond to in general. I, ballardw, whose post you replied to, have a very positive history with using SAS staring in 1986.

ballardw

Some minor things that when dealing with conversion from Excel to SAS sometimes aren't minor. 4.when i exported to sas it shows as mm/dd/yy format as numeric 8. EXCEL and SAS both use numeric values for date, time or datetime values. The way tell that such values are intended to be used as such is shown by applying a date, time or datetime format. The underlying numeric values do not change but the format may. Depending on the particular method used to convert data between Excel and SAS, SAS may attempt to duplicate the display appearance of the dates. If you right click on a cell populated with one of these date values in EXCEL you should see a cell format box pop up that will indicate what role the cell plays such as General, Number, Currancy, Date and a few others. If the cell is a date then that role will be selected and another part of the window should show the current display format in effect though Excel will provide an example appearance (generally) to select. SAS format descriptions use letters such as DD to indicate the day of month position, MM the month position and YY the year position in a date. Some of the behavior of the order of those fields can depend on your national language so it is not impossible to see a value in Excel appear differently in SAS because the numeric value in Excel could be displayed in day moth year order and get transposed to a month day year order if that is the national preference set in SAS (or vice versa). On not uncommon behavior is for an Excel sheet to have columns that mix numeric values with date display formats and character values of similar appearance if people are entering or modifying data manually. At which point you have have missing values or character date values, again depending on how you convert the data. Using SAS you do want to get some terminology a bit clearer when using the word "format" as that is a very specific meaning. A variable with have a format and the name is important and would be reported by clicking on displayed tabled headings or running Proc Contents on a data set or even using one of the information functions. Your SAS data set displays a value like mm/dd/yy but that is not a format as the format name is not allowed to have special characters. The FORMAT might be MMDDYY10. to show 10 characters, default using a / between the elements which would be displayed with 2 digits for month and day and 4 digits for the year. It is a good idea to specify the exact format SAS uses for a variable as some "problems" may well be addressed by changing formats such as displaying more or fewer characters, different or no delimiter between date or time elements and even which elements to display. SAS supplies over 50 date related formats and you can create custom appearances for dates using Proc Format if one of the existing formats doesn't fit your need.

ballardw

Will you provide the reason that you want these variable names in single quotes? I can see lots of potential problems with a long string and many single quotes for some types of use and perhaps it would be easier to approach the how this is to be used in the long run. As an aside to basic SAS coding, when you have variables with common names that only vary by a sequence of digits such as your balance_MESHU_LLL_PIG_MTI_1_AMT balance_MESHU_LLL_PIG_MTI_6_AMT you are often much better off providing the sequence number at the end of the variable as then you can use lists such as Common_part_: to get all variables that start with a common name portion or Common_part_2 - Common_part_6 to process just the 2 through 6 named variables. Note that the : list builder immediately follows the common part of the name. These lists can be used in keep/drop statements, array definitions using existing variables and many function calls that accept multiple variables using OF <list descriptor> such as Max(of Common_part_:

ballardw

@Ronein wrote: The issue is that I wrote code that other people should run. It might happend that in the source data set some of fields will not exist and then I want that the code still run (no error). If you are writing the code for other people to run the I would say that it is also your job to verify if the variables exist in the data sets used and to modify the code so that there are no such issues. Easy enough to check existence of variables for SAS data sets, I suspect there are at least 20 or 30 threads just on this forum related to such. If your data is external then that may be a bit more complicated. Modifying the code to use a verified list of variables may be much more complicated depending on how you are using the variables. There are many places that missing variables are critical failures and just turning off warnings will not allow the code to run at all much less "with no error". You may have to provide some examples of other examples of where these possibly missing variables may occur if other than in a Keep option or statement.

ballardw

@ChiSAS25 wrote: If it helps, yesterday I managed to implement the algorithm. proc sort data=input_ds out=input_ds_sort; by SubjectId diff_days; run; Did you verify before this sort that the SubjectId did not involve more than one sequence of values? Note that if your data starts in appropriate grouped /ordered values within SubjectId then the Proc Sort is not needed. You should be able to use: data input_ds_pairs_sel; set input; by SubjectId NOTSORTED; retain prev_day prev_flag1 prev_is_prior best_dt1 best_dt2 best_gap pa_dt1 pa_dt2; The NOTSORTED option on the BY statement allows you to process values grouped together as a by variable but without sorting as the BY statement expects and may through an error when not sorted. This is a very useful option.

ballardw

It is a good idea to paste your code by copy from your editor and paste into a text box opened on the forum using the </> icon above the message window. The forum software will reformat text when pasted into the main message window and may hide the actual problem because of the conversion. From ancient behaviors with some keyboards I don't trust >= and =< and use the SAS GE and LE comparisons in code. Sometimes a >= or <= may just happen to be a single character in an extended character set that SAS won't use for comparisons. It is also a good idea to provide data in the form of a working data step as when I create data set such as with this data step: data have; input var1 $ var2 $; datalines; ROOMA DESK01A ROOMA DESK01B ROOMA DESK05B ROOMA DESK06A ROOMB DESK09A ROOMB DESK09B ROOMC DESK01B ROOMC DESK05A ROOMD DESK10B ROOME DESK15A ROOME DESK15B ROOME DESK16 ; And get this log for YOUR code: 101 %let range_1 = 13; 102 %let range_2 = 24; 103 data test; 104 set have; 105 if input(compress(var2,'', 'A'), 8.) >= &range_1. and 105! input(compress(var2,'', 'A'), 8.) <= &range_2. then test = 'YEAH'; 106 run; NOTE: There were 12 observations read from the data set WORK.HAVE. NOTE: The data set WORK.TEST has 12 observations and 3 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds which has no error. So one suspects something in your data may be involved. Resulting data set: var1 var2 test ROOMA DESK01A ROOMA DESK01B ROOMA DESK05B ROOMA DESK06A ROOMB DESK09A ROOMB DESK09B ROOMC DESK01B ROOMC DESK05A ROOMD DESK10B ROOME DESK15A YEAH ROOME DESK15B YEAH ROOME DESK16 YEAH The variables Var1 and Var2 are length 8. If your variables white space values like leading spaces, tabs or NULL characters (ASCII 255) then we can't see those and may be reformatted by the forum software to blanks. I would try using : compress(var2,'', 'dk') to keep only digits. This would protect against non-displayed characters like Null. With those thoughts in mind, when I create a data set with this code: data have; input var1 $ var2 $; datalines; ROOMA DESK01A ROOMA DESK01B ROOMA DESK05B ROOMA DESK06A ROOMB DESK09A ROOMB DESK09B ROOMC DESK01B ROOMC DESK05A ROOMD DESK10B ROOME DESK15A ROOME DESK15B ROOME DESK16 ; Also any error message should be copied from the log with the code of the procedure or data step that generates the error along with the error and all other notes or warnings for that step. Copy the text from the log, open a text box using the </> and paste. Many of those errors will have diagnostics that indicate where the error is found. As an aside, I strongly recommend using numeric results in stead of 'Yes' or similar for comparisons especially if the result is binary Yes/No, True/False, Present/Not present or similar. You could use Test = value1 LE TestValue LE value; Test would have value 1 when true and 0 when false. It is often much easier to get reportable values from the numeric values a Sum of Test would have the count of true values, mean would be the percentage true values as as decimal. Questions like all true, all false, more true than false are also often easier to determine using sum of the variable compared to count of values, N statistic, in proc summary, means, report or tabulate than dealing with character values.

ballardw

@ChiSAS25 wrote: Can you help me program this algorithm that is giving me trouble? I have a dataset (as the one I've attached) with patient ID and the difference in days between two dates, with a range of -21 to +7. I need to identify patients who have two consecutive values of flag_KPI = 1 within the range [-21, 0). If they have only one in the range [-21, 0), then I consider the case where they have two consecutive values of flag_KPI = 1 , one in the window [-21, 0) and the other in the window [0, 7]. Somethings that will help get a good result: What is the output data set supposed to look like after this process? What is the result if a patient only has one observation in the data? What is the result if no consecutive results meet either criteria? How does the result indicate which criteria was met? Do any of the patients involved have more than one sequence of values? If so, how might different starts and intervals modify the result? A general note about about any data set involving date or time intervals. It is usually a very good idea to include variables that contain actual date, time or datetime values with the "base" value included. There are procedures that are intended to work with such for some of these interval types of questions. A data set as shown without actual dates might get sorted and if you have the same patient with 2 or more sequences then it might be quite awkward getting observations back into the correct order.

ballardw

It may depend on the SAS environment you are using. I am pretty familiar with the ancient Display Manager window. In that the SYSTEM Color is when you want to use one of the system colors for a specific area. That means select the Window Element in the first list, such as Background or Border. You can either select the shown colors (dating back to VGA basically) OR set one of the System colors. If the SYSTEM color definition of Background, Foreground or Secondary Background are changed then all of the windows defined with the system colors would have the changed setting. Which window element and what it means is documented in the section of online help "Using Base SAS Software in the Windowing Environment" in the topic "Changing SAS Windowing Environment Options" into links. Window Elements and Their Descriptions Window Element Description Background Specifies the background color for all primary windows that are displayed in a SAS session. Secondary Background Specifies the background color for all secondary windows that are displayed in a SAS session. Border Specifies the color and attribute for the border of a primary window. Secondary Border Specifies the color and attribute for the border of a secondary window. Banner Specifies the banner color and attribute within a window. The banner is the text that is used to signify the command line prompt 'Command==>'. Command Specifies the color and attribute of the command data entry field that is displayed when the PMENU bar is disabled. Message Specifies the color and attribute of the message line. Error Specifies the color and attribute of Error lines that are written to the SAS Log window or Output window. Warning Specifies the color and attribute of WARNING lines that are written to the SAS log. Note Specifies the color and attribute of Note lines that are written to the SAS log. Foreground Specifies the color and attribute for all text and text fields that you can edit that are defined within SAS windowing environment windows. Label Specifies the color and attribute for text that typically precedes a widget. The default label that precedes a field in the FSEDIT procedure is an example of a label. Row Label Specifies the color and attribute for ROW information. Informational Text Specifies the color and attribute for text that is displayed in a primary or secondary window as an aid to the user. 'Press Enter to continue' is an example of informational text. Column Label Specifies the color and attribute for the text labels that are used for COLUMNAR information. Primarily, this element is used to label the spreadsheet columns or column labels (for tabular-formed fields) of spreadsheet-oriented windows. Help Main Topic Specifies the color and attribute of the header that describes the general subject matter of a Help window. Help Subtopic and Syntax Specifies the color and attribute for the text that is displayed below the Help Main Topic header (when applicable) and the syntax. The Help subtopic describes the specific subject matter of the Help window. Help Link Specifies the color and attribute that are displayed for a hyperlink word or phrase. The hyperlink word or phrase that is selected displays detailed information about the topic that is referenced in the hyperlink. Selected Area Specifies the color and attribute of the area that is marked or selected for additional operations, such as find, cut, and copy. Source Specifies the color and attribute of SAS SOURCE lines that are written to the SAS log. Data Specifies the color and attribute of general lines that are written to the SAS log or Output window. Footnote Specifies the color and attribute information for FOOTNOTE lines that are written to the SAS Output window. Title Specifies the color and attribute information for TITLE lines that are written to the SAS Output window. Header Specifies the color and attribute information for HEADER lines that are written to the SAS Output window. Byline Specifies the color and attribute for BY lines that are written to the SAS Output window. Not all windows have all the options. So the list that appears for a specific window so is often shorter than the above list. I would suggest looking in you SAS install for the source of the macros Dm608clr.sas and Dmcolors. In my Windows install the folder is C:\Program Files\SASHome\SASFoundation\9.4\core\sasmacro These two macro show Display Manager syntax for setting some of the colors for some of the windows. Note that these files date back to about 1994 and you are likely not to have all of the windows in a recent install. The syntax is basically issue a DM command to open a window followed by several COLOR commands with the window element and the desired color and ends with a WSAVE command to keep the colors set persistent. You may be able to have the window open and in the command box enter commands such as: Color Background Pink and press enter to execute the command and change the displayed color. However the color names available will be quite restricted.

ballardw

There is a macro %TABLEN that is located and documented at https://communities.sas.com/t5/SAS-Communities-Library/Demographic-Table-and-Subgroup-Summary-Macro-TABLEN/ta-p/634030 that does what many of the demographic and similar tables ask for.

ballardw

If the purpose is to look for commonality between the variables you might try a report such as this: Proc tabulate data=have; class offerid degem amnt; table offerid, degem amnt /misstxt=' ' ; keylabel n=' '; run; This will have one row for each offerid. The columns for degem and amnt will have the count of the number of times each value appears for that offerid and a blank space where there are none (the option MISSTEXT does the blank). The keylabel supresses the N that would otherwise appear in each column for the statistic. If you don't want a count but just an indicator then a custom format to display all the values 1 or greater as a single character could be created and used. Note: this doesn't care what variable type any of the three variables are.

ballardw

@ChrisWoo wrote: Any ways to color my cell (background=yellow) when MSRP / Invoice > 100k AND, change font color (color=red) when MSRP / Invoice > 1 Mil ? Did anyone else reading this ask themselves what is to be highlighted because of that division calculation? ChrisWoo, for questions related to programming you may want to use words like "or" or "and" as appropriate instead of symbols like / * + - that may be interpreted as arithmetic operators.

ballardw

In addition to @PaigeMiller's very good suggestion, if you find yourself needing a date, time or datetime value to appear in a specific way and none of the very many SAS supplied formats do not quite get you there you can use Proc Format to create your own. One caveat with custom date/time/datetime formats is that they may not work as axis labels in graphs.

ballardw

This is one of the cases where it may be a good idea to include all of the code involved. For example, do you close the connection completely after after reading the data? If not then there might be a locking issue. Do you or your organization control the website? It is quite likely that displayed html is generated from another source for display as html and that source may well be what needs to be changed. If your organization controls the site then you may be able to work with the site developers/maintenance staff to determine what access and files are needed.

Online Status	Offline
Date Last Visited	Friday