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.
Hi Paul,
Original solution to pad zeros to a character variable is here https://communities.sas.com/t5/SAS-Procedures/how-to-pad-character-variable-with-leading-zeroes/td-p...
In EG query builder follow below steps
>> Query Builder
>> Add your input variables
>> Add a new computed column
>> Advanced Expression
>> (repeat('0',8-length(COLUMNTOPAD0)-1)||COLUMNTOPAD0)
and follow through to run the query
Hope this helps.
Selva
Thanks that looks like it'll sort me out.
I'd still like to know if it's possible to use the equivalent of a Do Loop within Query Builder as it might be useful in the future, if anyone knows the answer to that?
No, do loops do not work in Query builder - this is an SQL builder software, and SQL does not have the concept of a do loop, it is a selection of data based on logical criteria.
@paulrockliffe wrote:
Thanks that looks like it'll sort me out.
I'd still like to know if it's possible to use the equivalent of a Do Loop within Query Builder as it might be useful in the future, if anyone knows the answer to that?
Not within the GUI tools for sure. You could with a macro language, but it's not an appropriate for this question anyways. An example would be if you had say 100 cols that you need to loop a calculation over. It's usually easier to switch to a data step at that point.
If the value is also a number you can convert it to a numeric and then back to a character using Z8 format to ensure the leading zeroes.
put(input(my_var, 8.), z8.) as padded_variable
GUI tools get you about 75% of the way. You can get another 15% via some workarounds and multiple steps in a GUI that would be a single data step. And the last 10% requires pure code. In my experience that's true of most GUI analytical tools in the market today.
You can use the z format to pad numbers out with zeros.
data have; a=3456; b=put(a,z8.); run;
You will see b is:
00003456
You can use this technique in a merge (join in SQL) as well, so - and I don't use builders so you would need to adapt to your software:
proc sql; create table want as select * from have where id not in (select distinct(put(id,z8.)) from codelist); quit;
(Note no test data or anything to work with so just run up a simple example).
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.