BookmarkSubscribeRSS Feed
paulrockliffe
Obsidian | Level 7

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.

5 REPLIES 5
Selvaraj
Fluorite | Level 6

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

 

paulrockliffe
Obsidian | Level 7

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?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.  

Reeza
Super User

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 2097 views
  • 1 like
  • 4 in conversation