BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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;
9 REPLIES 9
Flip
Fluorite | Level 6
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.
deleted_user
Not applicable
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
Peter_C
Rhodochrosite | Level 12
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
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Peter_C
Rhodochrosite | Level 12
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.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Peter_C
Rhodochrosite | Level 12
Scott
would you like to demonstrate?
I can't achieve the required effect with less than the 3 tramwrd() calls.
PeterC
data_null__
Jade | Level 19
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]
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 908 views
  • 0 likes
  • 5 in conversation