topic Looping through two sets of values in Proc SQL in SAS Programming

Looping through two sets of values in Proc SQL

JS — Tue, 17 May 2016 22:53:54 GMT

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;

Re: Looping through two sets of values in Proc SQL

ballardw — Tue, 17 May 2016 23:21:05 GMT

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;

Re: Looping through two sets of values in Proc SQL

LinusH — Wed, 18 May 2016 06:13:58 GMT

If you wish do avoid writing similar lines of code, an in line macro might be the answer.

Re: Looping through two sets of values in Proc SQL

JS — Wed, 18 May 2016 06:18:52 GMT

What is an in line macro? What would that look like?

Re: Looping through two sets of values in Proc SQL

JS — Wed, 18 May 2016 06:21:07 GMT

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.

Re: Looping through two sets of values in Proc SQL

RW9 — Wed, 18 May 2016 08:22:05 GMT

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.

Re: Looping through two sets of values in Proc SQL

LinusH — Wed, 18 May 2016 09:44:25 GMT

The call would look like:

%phoneRule(home)

And the definition something like:

%macro phoneRule(type);
case
when &type._phone in ("") then "_"
when &type._phone in ("0") then "0"
else "H" end as &type._present
%mend phoneRule;

Check online doc for Macro programming for more details.