topic Re: Creating Macro Variables using PROC SQL in SAS Procedures

Creating Macro Variables using PROC SQL

deleted_user — Thu, 12 Nov 2009 20:45:11 GMT

DATA DEMOG;
INPUT NAME $ GENDER $;
DATALINES;
TOM M
JILL F
PETE M
KAT F
SAM M
KELY F
;
RUN;

PROC SQL NOPRINT;
SELECT CASE WHEN GENDER = 'F' THEN NAME END,
CASE WHEN GENDER = 'M' THEN NAME END
INTO :GIRLS SEPARATED BY ',' ,
:BOYS SEPARATED BY ','
FROM DEMOG ;
QUIT;
%PUT &GIRLS. ;
%PUT &BOYS. ;

Hello,
I'm creating 2 macro variables GIRLS, BOYS as stated in the above sql. I'm getting the desired ouput. But i'm getting some unwanted comma's in the output a sshown below.
%PUT &GIRLS. ;
,JILL,,KAT,,KELY
%PUT &BOYS. ;
TOM,,PETE,,SAM,.

I dont want to use 2 select statements when creating the macro variables . I can get the desired output if I use the below sql statements.But I want to use either a case or if statement only.can anybody pls help me with this.I really appreciate your help..Thanks
PROC SQL NOPRINT;
SELECT NAME INTO: BOYS SEPARATED BY ',' FROM DEMOG WHERE GENDER = 'M';
SELECT NAME INTO: GIRLS SEPARATED BY ',' FROM DEMOG WHERE GENDER = 'F';
QUIT;

Re: Creating Macro Variables using PROC SQL

Flip — Thu, 12 Nov 2009 21:01:25 GMT

What is happening is that in each Case group the values that do not match are being set to Missing and placed in the macro variable.
If you code an ELSE condition for each case setting a default value you will see that value between your ','s.
The reason you have them all doubled is that you alternate gender in you data set.

Re: Creating Macro Variables using PROC SQL

deleted_user — Thu, 12 Nov 2009 22:56:09 GMT

Thanks for your reply.
If I use an ELSE condition like
CASE WHEN GENDER = 'F' THEN NAME ELSE 0 END
then values 0 will also be created in the macro variable &GIRLS. shown below.
0,JILL,0,KAT,0,KELY
which I don't want in my results.
I just want my results like this - JILL,KAT,KELY

Thanks

Re: Creating Macro Variables using PROC SQL

Peter_C — Fri, 13 Nov 2009 08:30:16 GMT

understand what is happening:
"separated by ',' " places comma between selected rows.
When you want a females-only list, select where the input is female.
"Case", is not "where".
To create two separate lists, you need two separate "select" statements, like[pre] select name into :males separated by "," from demog where gender = "M";
select name into :females separated by "," from demog where gender = "F";
[/pre]hope that does it.
Alternatively, removing the comma separation from your original hides the empty names of the "wrong" gender.

PeterC

Re: Creating Macro Variables using PROC SQL

sbb — Fri, 13 Nov 2009 15:24:11 GMT

One remedy to the current behavior: the resulting macro variables could be post-processed with a couple of %LET statements using %SYSFUNC with the TRANWRD and COMPRESS functions.

Scott Barry
SBBWorks, Inc.

Re: Creating Macro Variables using PROC SQL

Peter_C — Fri, 13 Nov 2009 15:45:37 GMT

something like
%let males =%sysfunc( tranwrd(%superq(males),%str(,,), %str(,) ));
with superQ to protect the commas in the value of &males, from being treated as syntax, rather than data
Another issue to manage: occasions where more than two commas occur.
Here is a small demo
%let males = a,b,,c,,,d,,,,e,,,,,,f,,,,,,,g,,,,,,,,h;
%let males =%sysfunc( tranwrd(%superq(males),%str(,,), %str(,) ));%put &males ;
%let males =%sysfunc( tranwrd(%superq(males),%str(,,), %str(,) ));%put &males ;
%let males =%sysfunc( tranwrd(%superq(males),%str(,,), %str(,) ));%put &males ;
[pre]1 %let males = a,b,,c,,,d,,,,e,,,,,,f,,,,,,,g,,,,,,,,h;
2 %let males =%sysfunc( tranwrd(%superq(males),%str(,,), %str(,) ));%put &males ;
a,b,c,,d,,e,,,f,,,,g,,,,h
3 %let males =%sysfunc( tranwrd(%superq(males),%str(,,), %str(,) ));%put &males ;
a,b,c,d,e,,f,,g,,h
4 %let males =%sysfunc( tranwrd(%superq(males),%str(,,), %str(,) ));%put &males ;
a,b,c,d,e,f,g,h[/pre]

looks like it needs three compbl() invocations to deal with all.

Re: Creating Macro Variables using PROC SQL

sbb — Fri, 13 Nov 2009 16:10:06 GMT

With the TRANWRD function, use a replacement character that does not appear in the original string and then use COMPRESS to squeeze it out. I do it all the time in this manner. Unfortunately, TRANWRD does not understand what to do if you want a null replacement value -- a single blank is a minimum replacement, unfortunately -- so use COMPRESS does the clean-up.

Scott Barry
SBBWorks, Inc.

Re: Creating Macro Variables using PROC SQL

Peter_C — Fri, 13 Nov 2009 20:07:52 GMT

Scott
would you like to demonstrate?
I can't achieve the required effect with less than the 3 tramwrd() calls.
PeterC

Re: Creating Macro Variables using PROC SQL

data_null__ — Fri, 13 Nov 2009 21:13:03 GMT

Not to change the subject completely but I though RegEX would be usefull. However, I could not get this to work via SYSFUNC. I always got an error regarding the , in the change expression. Tried all sorts of quoting but never found the solution.

[pre]
731 data _null_;
732 males = 'a,b,,c,,,d,,,,e,,,,,,f,,,,,,,g,,,,,,,,h';
733 males =prxchange('s/,+/,/',-1,males);
734 put males;
735 run;

a,b,c,d,e,f,g,h
[/pre]

I count not get past the comma.

[pre]
739 %let males = 'a,b,,c,,,d,,,,e,,,,,,f,,,,,,,g,,,,,,,,h';
740 %let males =%qsysfunc(prxchange(%qsysfunc(rxparse(%str(s/,+/,/))),-1,%superQ(males)));
ERROR: The following pattern expression passed to the function RXPARSE contains a syntax error.
s/,+/,/
^
ERROR: Misplaced list separator or missing change expression.
WARNING: Argument 1 to function RXPARSE referenced by the %SYSFUNC or %QSYSFUNC macro function is out of range.
NOTE: Mathematical operations could not be performed during %SYSFUNC function execution. The result of the operations have
been set to a missing value.
NOTE: Argument 1 to the function PRXCHANGE is missing.
WARNING: Argument 1 to function PRXCHANGE referenced by the %SYSFUNC or %QSYSFUNC macro function is out of range.
741 %put NOTE: MALES=***&Males***;
NOTE: MALES=******
[/pre]

Re: Creating Macro Variables using PROC SQL

sbb — Fri, 13 Nov 2009 21:20:37 GMT

Based on this forum thread example, the code works where the PROC SQL populates a zero on a no-match condition - and you have to run it through twice to catch the front-end and back-end no-match:

%let boys = 0,0,Fred,Joe,0,Ted,0;
%let boys = %sysfunc(tranwrd(%nrbquote(&boys),%str(0,),%str(\)));
%let boys = %sysfunc(tranwrd(%nrbquote(&boys),%str(,0),%str(\)));
%let boys = %sysfunc(compress(%nrbquote(&boys),\));
%put &boys;

Scott Barry
SBBWorks, Inc.