Transcript Quote Me On This : How To Make SAS Talk Database

Article & Slides:
www.idiom.com / ~sherman / paul / pubs / qcomma
Quote Me On This:
How To Make
SAS
Talk Database
Paul Sherman
Overview
The %qcomma macro function helps us go
from SAS variable names which are
space delimited into Oracle, DB2 and
SAS lists-of-values which are quoted and
comma delimited.
Topics to be Covered
•
•
•
•
Those darn quotes
The old way we resolve them
A cheesy solution
Macro function %qcomma
• Examples & Applications
• How it works
Commas & Quotes
• WHERE … IN() needs values
• Dataset variable names are not values
• How to convert names to string values
%macro getdata(trtgrp); %mend;
%getdata(A B C);
Good
%getdata('A','B','C'); BAD
Proc SQL; WHERE trtgrp IN ('A','B','C');
Proc SQL; WHERE trtgrp IN ( A
B
C );
The Old Way
•
•
•
•
•
1
2
3
Copy / Paste values into Excel
Replicate commas into adjacent cells
Copy / Paste block back into SAS
Remove that last comma
Exceedingly tedious
A
‘
‘
‘
B
treat-a
treat-b
treat-c
C
‘
‘
‘
D
,
,
,
IN (
E 'treat-a',
'treat-b',
'treat-c',
)
A Cheesy Solution
•
•
•
•
Use Proc SQL to append quotes & comma
Build macro variable
Can’t use with other SAS statements
Have to deal with that last comma (again)
%local trtgrp;
Single-quote mark
Proc SQL;
SELECT chr(39)||trtgrp||chr(39)||',' :into trtgrp
FROM work.mytable;
quit;
Might also want to ltrim()
%put &trtgrp.;
and rtrim(), too
'A','B','C',
Macro Function %qcomma
• Translates names to character strings
• Use with your Proc SQL WHERE … IN clause
When Delimiter is a Space
%put %qcomma( one two three four );
'one','two','three','four'
Embedded Space as part of the
Value
%put %qcomma( PT Rate
INR Ratio );
'PT','Rate','INR','Ratio'
%put %qcomma( PT Rate Z INR Ratio
,delim=Z );
'PT Rate','INR Ratio'
Tip: Don’t use a-z or 0-9 for your delimiter
Typical SAS to SQL Application
%macro analyze(parms);
WBC RBC
Proc SQL;
connect to odbc(dsn= uid= pwd=);
select * from connection to odbc (
SELECT parm, value
FROM labs
WHERE parm IN (%qcomma(&parms.))
);
disconnect from odbc;
'
WBC','RBC'
quit;
%mend;
%analyze( WBC RBC );
How %qcomma Works
%macro qcomma(list, delim=%str( ));
%local varlist i var;
%let varlist=&list.;
%let i=0;
 Scan
%do %while(%qscan(&varlist.,%eval(&i.+1),
%quote(&delim.)) ne );
%if &i. gt 0 %then %do;
%let var=%str(%trim(%quote(&var.)),);
%end;
 Concatenate
%let i = %eval(&i.+1); Extract
%let var=&var.%str(%')%qscan(&varlist,&i.,
 Mark-up
%quote(&delim.))%str(%');
%end;
%* MUST be first and only thing on a line by itself *;
%unquote(&var.) Finalize
%mend;
Conclusion
• Variable names are most challenging to
translate into lists of values
• %qcomma macro is an easy solution to add
that quote & comma
Acknowledgments
• Michael Metts and Paul Dorfman
Original idea and discussion
• SAS Technical Support
Review of macro variable quoting
• Na Li
Examples, technical review and critique
About the Speaker
Speaker Paul D Sherman
Electrical Engineer
Location 335 Elan Village Lane #424
San Jose, CA 95193
Telephone (408) 383 – 0471
E-Mail sherman @ idiom.com
Web Site www.idiom.com / ~sherman / paul / pubs / qcomma