Desktop productivity for business analysts and programmers

Running a Loop in Query Builder

Reply
Contributor
Posts: 56

Running a Loop in Query Builder

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.

Occasional Contributor
Posts: 9

Re: Running a Loop in Query Builder

Posted in reply to paulrockliffe

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

 

Contributor
Posts: 56

Re: Running a Loop in Query Builder

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?

Super User
Super User
Posts: 9,211

Re: Running a Loop in Query Builder

Posted in reply to paulrockliffe

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.  

Super User
Posts: 22,845

Re: Running a Loop in Query Builder

Posted in reply to paulrockliffe

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.

Super User
Super User
Posts: 9,211

Re: Running a Loop in Query Builder

Posted in reply to paulrockliffe

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).  

Ask a Question
Discussion stats
  • 5 replies
  • 161 views
  • 1 like
  • 4 in conversation