05-17-2016 06:49 PM - edited 05-17-2016 06:53 PM
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.
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;
05-17-2016 07:21 PM
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;
05-18-2016 02:20 AM - edited 05-18-2016 02:21 AM
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.
05-18-2016 04:22 AM
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.
05-18-2016 05:44 AM