About pcfriendly

pcfriendly · ‎07-25-2013

Thanks to all, especially, Reeza & Bruno. I eventually figured out to do the Rank using only the 2 Fields & got the Rank Number & then ran a query builder to select where the Rank Number = 1 as you said Bruno. This will come in very handy, Cheers Peter

pcfriendly · ‎07-25-2013

OK, thanks Reeza, I'm doing that now to see how I can just get the row that has the latest Start_Date. Thanks for your help.

pcfriendly · ‎07-25-2013

You're right Larry, I was in the wrong task icon, Rank instead of Sort... DUH !!:smileysilly:

pcfriendly · ‎07-25-2013

Sorry Reeza, you're right, I was in a 'Rank' Icon instead of a Sort Icon. My screen looks exactly like yours & does have the Duplicates option area. Under 'Data', I've added the Start_Date filed in the 'Sort By' area descending & under 'Options' I have selected the 'Keep only the first record for each 'Sort By' group' option. My results are showing more than one date for the Job_Role though? Is it correct to just add one field in the Task Roles - Sort By area? Also, what is 'Columns to be dropped for? Thanks

pcfriendly · ‎07-25-2013

Hi Bruno, My options to choose under the Data area in the Rank icon are as follows; Job_Role sort order - which I've set to Descending Columns to Rank - Start_Date Rank By - Job_Role Sort by Variables checkbox - not sure if I should leave it checked? My options to choose under the Options area in the Rank icon are as follows; Ranking method - Smallest to Largest Radio Button If values tie, use; - Default is Median, other options are 'High Rank', 'Low Rank' There's also a checkbox for 'Reverse Ranking from largest to smallest' doesn't this negate the Ranking Method already set to 'Smallest to Largest? The date field I'm using is a Date Time value & I'm getting results like 403.5 in my ranking results??

pcfriendly · ‎07-25-2013

Hi Reeza, I'm using EG 4.3 version, it doesn't have the option of 'Options>Duplicate' - select the option to keep only the first record.

pcfriendly · ‎07-25-2013

Hi Bruno, I wasn't aware of this before, thank you. I'm getting some strange numbers in the Rank result. I'm not getting a 1, 2 3 in the ranking which is what I was expecting. I noticed I have the below options when I add the Rank node to the graph as below; Sort By Variables under the Data area Ranking Method under the Options area If values tie, use; What shoud I choose for these to get a 1,2,3 Ranking? Thanks

pcfriendly · ‎07-24-2013

Thanks Reeza, So I would be entering that code into a new Program? Where is the reference to the source table? Thanks

pcfriendly · ‎07-24-2013

Hi, I'm trying to find the Function in SAS that will perform a sort on my Dataset of Job Roles & Start Dates. I'm using Enterprise Guide 4.3 & I want to do this sorting in an Advanced Expression, that is via The Query Builder >> 'Computed Columns' >> 'New' >> 'Advanced Expression' The Dataset has various Job Roles staff have had & the various Start Dates, there are multiple Job Roles & Start Dates for each Staff member. I want to find the latest Start Date & Job Role for each Staff member. In Teradata i would use the below; ------------------------------------------------------ Select Job_Role, Start_Date, ROW_NUMBER OVER (PARTITION BY 'Job_Role' ORDER BY Start_Date DESC) AS Rank_No From Data_Table ------------------------------------------------------ I could then filter the records for where the 'Rank_No' = 1 which would give the most current Job Role for that Staff member. What can I use in SAS to do a similar task? Thanks

pcfriendly · ‎07-02-2013

Thanks Ksharp, Unfortunately, this doesn't work. to use it, I replaced the 'score' value with my field name & got the results of '0's & '1's instead of the field value + 1. Existing values ending in '0' such as '40' were displayed as '1' & values ending in '9' such as '29' were displayed as '0' ??

pcfriendly · ‎07-02-2013

Thanks, but if you mean the script your posted above, it's obviously not going to work in the Advanced Expression area of SAS EG 4.3. data have; input Score; if mod(score,10)=9 then score=score+1; cards; 119 110 100 114 120 115 106 109 ; run;

pcfriendly · ‎07-01-2013

Hi Barry Stevens, No I haven't tried KSharps answer as I need it as an advanced expression - such as the one I posted as what Excel would accept. I should have explained that, so I'm looking for someting I can add into the Advanced Expression area of SAS EG 4.3. I didn't mean to be rude KSharp, I also answer Forum questions & if someting is obvious, I reuse what I've already written. I think I did explain the bit about strings & numbers clearly enough, I don't know what function to use & these (String) ones didn't work, so I'm asking what function is best here. Any suggests?

pcfriendly · ‎07-01-2013

As advised in the question; I'd like to know how I can firstly locate the last integer from a number value & if it is a '9' add 1 to it, otherwise leave the value as it is. '29' becomes '30'

pcfriendly · ‎07-01-2013

Hi Ksharp, The Field contains only Integers such as 0, 29, 30, 59, 150, 99 etc, so I want the ones that end in '0' to be left alone.

pcfriendly · ‎07-01-2013

Hi, I have a number field & some values end in a '9' & I want to round those to the next number - that is '29' becomes '30'. I know I could use a 'Round' type function but I'd like to know how I can firstly locate the last integer from a number value & if it is a '9' add 1 to it, otherwise leave the value as it is. In excel I would add this - =IF(RIGHT(E2,1)="9",E2+1,E2). I looked at the RIGHT, SUBSTR & SUBSTRN functions but I suppose they are for text?? Can anyone suggest the best answer? Thanks

Online Status	Offline
Date Last Visited	‎09-01-2015 07:11 AM

Re: Change date/time DD/MM/YY to date MMYY SAS EG 4.2

Re: Change date/time DD/MM/YY to date MMYY SAS EG 4.2

Change date/time DD/MM/YY to date MMYY SAS EG 4.2

Re: "Access to path C:\... Denied" error

Re: "Access to path C:\... Denied" error

"Access to path C:\... Denied" error

Re: User manual for SAS Enterprise Guide 4.3

Re: User manual for SAS Enterprise Guide 4.3

Re: User manual for SAS Enterprise Guide 4.3

Re: User manual for SAS Enterprise Guide 4.3

Re: Finding Unmatched Values between 2 tables

Re: EPOCH Time conversion

Re: Find Latest value using ROW_NUMBER OVER (PARTITION BY 'X' ORDER BY...

Re: Find Latest value using ROW_NUMBER OVER (PARTITION BY 'X' ORDER BY...

Re: Find Latest value using ROW_NUMBER OVER (PARTITION BY 'X' ORDER BY...

Re: Find Latest value using ROW_NUMBER OVER (PARTITION BY 'X' ORDER BY...

Re: Find Latest value using ROW_NUMBER OVER (PARTITION BY 'X' ORDER BY...

Re: Find Latest value using ROW_NUMBER OVER (PARTITION BY 'X' ORDER BY...

Re: Find Latest value using ROW_NUMBER OVER (PARTITION BY 'X' ORDER BY...

Re: Find Latest value using ROW_NUMBER OVER (PARTITION BY 'X' ORDER BY...

Find Latest value using ROW_NUMBER OVER (PARTITION BY 'X' ORDER BY DAT...

Re: Function to locate the last character in a string

Re: Function to locate the last character in a string

Re: Function to locate the last character in a string

Re: Function to locate the last character in a string

Re: Function to locate the last character in a string

Function to locate the last character in a string