THis might be a daft question, but I've not needed to do this before and I'm struggling to find a guide or syntax anywhere.   Can I run a loop in query builder?   I'm validating a column of reference numbers, I have one source where each reference has 8 characters, often with leading zeros.  I have a second data source wit the same references where the leading zeros have been stripped.  As a result I can't match the data properly, and I'd like to add the leading zeros back to the strings.   I basically want a loop that looks at the length of the string, if it's less than 8 then it concatenates a zero with the string, then loops through again.  Once the string is 8 characters long, end the loop.   Is this possible, is there a better way to do it?   Thanks   Paul. ... View more

I'm building some data tables that include data across several years.  The last step is to transpose the rows so that I have a column for each year.  I'm not using the transpose function for this, I'm using a program to create 5 tables then joining these back together in Query Builder.   Each row has a column with the year in it, an entity identifier and a total score.   The years that are included are the previous five years, this is controlled automatically by filters in the process flow that take account of the current date, so on the first of January the data will drop a year of the end and add the previous year to the analysis.   I have got it setup with column names YM1 to YM5, ie current Year Minus 1 to 5 to identify the years.  But this isn't perfect because I then have to add an explanation of what that means when the results are sent out and people generally don't like it.     Is it possible to use a program to change column names to add the year?  I've added a table below that should explain what I mean properly I hope.   Thanks!   Initial Table:   Name       || Year  || Total -------------------------------- Company || 2016 || 567 Company || 2015 || 684 Company || 2014 || 123 Company || 2013 || 678 Company || 2012 || 348   Is transposed into:   Name       ||  2016 Total  ||  2015 Total  ||  2014 Total  ||  2013 Total  ||  2012 Total ----------------------------------------------------------------------------------------------------------- Company ||      567         ||      684         ||      123        ||      678        ||      348   ... View more

I've got a project that clashes a couple of data tables together.  There's a problem with duplicates within one of the tables, but I'm able to correct some of the duplicates by bringing in some other data and doing some messing around to find the pairs of duplicates where I know enough about one or the other to work out which one is the error.   I've created a table of errors to filter out and joined this back to my process flow to correct the data.  I want to be able to turn the feature on or off via a Prompt as there are some circumstances where it's better to have the duplicates.   When I thought about this in the car I figured it would be easy to do as I just need to work out the filter to remove duplicates and then add a second filter "&Prompt = Yes" to the filter, so if the Prompt is set to No there will be no cases that match all the criteria.   Of course on trying to build this I've remembered that Prompts don't work this way, so now I'm stuck!  I know I can use conditional processing but this filter is at the start of a very long and complicated flow and I don't want to have to duplicate everything from this point.  With it being a simple filter that should be unnecessary.   But how do I do it?  This is EG5.1.   Thanks! ... View more

I've got a data table which consists of sequental returns from various sources.  I want to add a label to the table that identifies where each row sits in the sequence of returns from it's source.  For example, I want to label the latest return for each source as "1", the one before that as "2", etc.     I'm using Enterprise Guide, so something that works in the Query Builder would be great.   I'm running a test on the data that compares the latest figure with the previous figures, so ned to be able to know where in the sequence each sits.   Thanks ... View more

I'm using a Prompt with a Code Node to let a user select a table to run the code on.  I'd like the user to be able to select from a list of all of the tables in the Project, with the Prompt automatically picking up the values to offer to the user.  My code is splitting a table up into a number of smaller tables and will be used across a wide range of projects.   Is this possible?   I was thinking that there may be a table within SASHELP that contains that information, but I can't find it.  Alternative methods gratefully received.   As a follow-on question, if the above is possible I would like to be able to set a second prompt that lets you select one of the columns from the selected table.  I think I can do this using cascading prompts, but again any pointers gratefully received.   Thanks! ... View more