DATA Step, Macro, Functions and more

Looping through two sets of values in Proc SQL

Reply
Contributor JS
Contributor
Posts: 38

Looping through two sets of values in Proc SQL

[ Edited ]

 

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;

 

Super User
Posts: 11,343

Re: Looping through two sets of values in Proc SQL

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;
Contributor JS
Contributor
Posts: 38

Re: Looping through two sets of values in Proc SQL

[ Edited ]

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.

Super User
Posts: 5,424

Re: Looping through two sets of values in Proc SQL

If you wish do avoid writing similar lines of code, an in line macro might be the answer.
Data never sleeps
Contributor JS
Contributor
Posts: 38

Re: Looping through two sets of values in Proc SQL

[ Edited ]

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

Super User
Super User
Posts: 7,942

Re: Looping through two sets of values in Proc SQL

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.

 

Super User
Posts: 5,424

Re: Looping through two sets of values in Proc SQL

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.
Data never sleeps
Ask a Question
Discussion stats
  • 6 replies
  • 319 views
  • 1 like
  • 4 in conversation