Hi Everyone,   Running into an issue and I'm not sure what I'm missing here. I have 8 numeric variables, that I sum together, and then subtract a 9th numeric variable from the summation. When I simply sum the first 8 I get a valid output, however, when I try subtracting the 9th I get a period (.) There are some instances of missing values in all 9 columns, that's why I thought sum() was the best approach.     Example code below:   DATA WORK.WANT; SET WORK.HAVE; TotalCount1 = sum(of Total1 -- Total8); TotalCount = sum(TotalCount1 -Total9); DROP TotalCount1; RUN;   I'd prefer, this step below, but neither have worked DATA WORK.WANT; SET WORK.HAVE; TotalCount = sum(sum(Total1 -- Total8) - Total9); RUN;   ... View more

Hi All,   I'm trying to remove middle initials at the end of a name IF it has a middle initial (some names don't have middle initial in the dataset).   What I have: Name Doe, John M. Doe, Jane   What I'd like: Name Doe, John Doe, Jane   I've seen some different approaches, and it seems as though a substr(scan()) may be the best approach, I'm just not sure how to apply it.   I tried the below code, but it removed everything after the first space. Name = substr(Name,1,length(Name) - length(scan(Name,-1,' ')));   ... View more

Hi Everyone,   I have a table (example of current & what i'm looking for) and am not sure which data or proc statement is best to accomplish this and if I need to list out every column name that needs summed.   Current Table: Date ID Weight Height Length Miles 11/10/2020 A123D 4.5 3 5 15 11/10/2020 A123D 5.5 5 7 10 11/10/2020 B456E 7.0 4 3 20 11/12/2020 B456E 6.0 6 8 12 11/12/2020 A123D 8.0 2 4 17   Look to sum by Date and ID, to hopefully get something like this: Date ID Weight Height Length Miles 11/10/2020 A123D 10 8 12 25 11/10/2020 B456E 7.0 4 3 20 11/12/2020 B456E 6.0 6 8 12 11/12/2020 A123D 8.0 2 4 17 ... View more

Hi All,   Looking to see the best way to delete a row if all numeric variables are 0.   I've seen some examples of how to delete rows with all missing values and could all do IF missing() for each variable, however, I was hoping someone may know of an easier way (because I have 30 number columns) I have a table example below, the first 3 columns are formatted as a date, character, and character, the remaining are numbers.   Date Name City Count A Count B Count C Count D Count E 01/01/2021 John NYC 1 0 0 4 0 01/01/2021 John NYC 0 0 0 0 0 01/01/2021 Jane NYC 0 3 2 0 0   I saw some code like this, which didn't delete any records (I assume maybe because Date may be considered numeric?) if nmiss(of _numeric_) then delete; I also tried the below code, which didn't work and threw an error (error shown below code). if sum(of 'Count A'n-'Count E'n)=0 then delete; ERROR: Alphabetic prefixes for enumerated variables (Count A-Count E) are different. ERROR 71-185: The SUM function call does not have enough arguments.   ... View more

Hi Everyone,   I've seen some posts about reading in txt files and excel files from a folder based on a date, so this may seem like a repeat, however, I'm not familiar enough with it to adapt to my current requirements, so I'm hoping to get a solid example that's applicable.   I'm looking for a data step that can scan a specific folder and pull in the csv with the newest date in the file name.   I've adapted my file names to read TestFile_20201230 as I've read on other threads that YYYYMMDD is the best approach (I can change this if someone has a better suggestion).   Example file path could be 'stage/documents/testreports/TestFile_20201230.csv'   Any and all help would be greatly appreciated.         ... View more

I'm not sure of the best approach for this, so I'll provide a current table example and the desired output table. The first table is a series of records. The activity types are known, so I'm thinking we could use WHERE ActivityType = "App_New". But am open to any and all suggestions/help.   ID Date ActivityType B123D 11/01/2020 APP_New B123D 11/01/2020 APP_New A932F 11/01/2020 APP_Edit C456E 11/01/2020 APP_New B123D 11/02/2020 APP_Edit A932F 11/02/2020 APP_New C456E 11/02/2020 APP_Edit C456E 11/02/2020 APP_New   What I'm looking to accomplish, is to count the number of specific activity types by ID and by Date. So the "new" table would look something like this (see below), based off the above table. Date ID Total_App_New Total_App_Edit 11/01/2020 B123D 2 0 11/01/2020 A932F 0 1 11/01/2020 C456E 0 1 11/02/2020 B123D 0 1 11/02/2020 A932F 1 0 11/02/2020 C456E 1 1 ... View more

I have a .xlsx that has 37 different columns. After import, I want to rename them based on column position. For example, reference below mockup table, I'd like to change "Start Time" to "StartDate" by referencing the column number (Column 1) if possible, as there are 37 columns all with varying names and lengths of names and was looking for an easier way to rename. Some other examples I've found have limitations after 26 columns. If there is a way to do this on import, referencing column position, I can do that as well. I'm just not sure of how to approach this.   Start Time Machine Name Total Hours Worked             ... View more