topic Re: Running a Loop in Query Builder in SAS Enterprise Guide

Running a Loop in Query Builder

paulrockliffe — Tue, 20 Feb 2018 11:57:21 GMT

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.

Re: Running a Loop in Query Builder

Selvaraj — Tue, 20 Feb 2018 12:22:33 GMT

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/40486

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

Re: Running a Loop in Query Builder

paulrockliffe — Tue, 20 Feb 2018 12:27:23 GMT

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?

Re: Running a Loop in Query Builder

RW9 — Tue, 20 Feb 2018 12:56:50 GMT

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

Re: Running a Loop in Query Builder

RW9 — Tue, 20 Feb 2018 12:58:20 GMT

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.

Re: Running a Loop in Query Builder

Reeza — Tue, 20 Feb 2018 16:53:46 GMT

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