Is anyone familiar with how to conduct a loop in a SQL step?
I'm trying to figure out how to loop through two sets of values in SQL. I realize that the way I'm currently doing the query (just manually writing the code multiple times) also works, but I was wondering if there's a better way to do it.
Have:
proc sql;
Select
ID,
home_phone,
work_phone,
cell_phone
case
when home_phone in ("") then "_"
when home_phone in ("0") then "0"
else "H" end as home_present
case
when work_phone in ("") then "_"
when work_phone in ("0") then "0"
else "W" end as work_present,
case
when cell_phone in ("") then "_"
when cell_phone in ("0") then "0"
else "C" end as cell_present
from TABLE
;quit;
And I want something like this (but actually works lol )
[Letter loop] = H,W,C;
[Number loop] = home,work,cell;
proc sql;
Select
ID,
[Number Loop]_phone
case
when [Number Loop]_phone in ("") then "_"
when [Number Loop]_phone in ("0") then "0"
else [Letter Loop] end as [Number Loop]_present
from TABLE
;quit;
The closest thing I can think of what it looks like you are doing is using Arrays. But Arrays are used in data steps and not available in Proc SQL. The code below creates a small data set and demonstrates ONE way to do something similar.
data have;
input home_phone $ 1-10 Work_phone $ 12-21 Cell_phone $ 23-32;
datalines;
1111111111 2222222222
0 2222222222
3333333333 0 2222222222
3333333333 1111111111
3333333333 1111111111 0
;
run;
data want;
set have;
array phones home_phone work_phone cell_phone;
array codes (3) $ 1 _temporary_ ('H' 'W' 'C'); /* 3 is number of items , $ 1 says the items are character and will
be one character long */
array present (3) $ 1 home_present work_present cell_present;
do i = 1 to dim(phones);
If phones[i] in ("") then Present[i] = "_";
else If phones[i] in ("0") then Present[i] = "0";
Else Present[i] = Codes[i];
end;
drop i;
run;
This is definitely an interesting approach I had not thought of.
I'm still looking for a way to enable the loop in an SQL statement if possible, but this might be my other option if that's not possible.
What is an in line macro? What would that look like?
Can I sugest you start by posting test data - in the form of a datastep - so that we can see what the dataset looks like. There is no such thing as "looping" in terms of SQL - there is a cursor which starts at the first observation and moves down. You can however group things, join them and other methods of doing such process, but normally you wouldn't need to worry about it. Also post and example of what you want the output to look like.
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.