Date: Sat, 19 Feb 2011 03:13:22 +0000
Reply-To: "Keintz, H. Mark" <mkeintz@WHARTON.UPENN.EDU>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Keintz, H. Mark" <mkeintz@WHARTON.UPENN.EDU>
Subject: Re: More on Selecting Variables by Suffix
In-Reply-To: <B90B817A9BBB904AAAD7EC321C01596710F529@USFCH-MAIL1.lewin.com>
Content-Type: text/plain; charset="us-ascii"
Ted:
I think you're better off building a "KEEP=" dataset name parameter than a "DROP=". Consider data set have:
data have;
input meanxxx medianxxx stdxxx meanyyy medianyyy stdyyy other1 other2;
datalines;
100 110 21 200 220 22 11111 22222
run;
You want something like:
data xxx (keep=other1 other2 meanxxx medianxxx stdxxx)
yyy (keep=other1 other2 meanxxx medianxxx stdxxx);
set have;
run;
To do so you really need to
1. capture "xxx" and "yyy".
2. capture the names of the other variables (other1 and other2)
3. Use 1 and 3 to build the expressions above.
This should do:
proc sql noprint;
create table dsnames as
select substr(name,5,8) as dsn
from dictionary.columns where memname='HAVE' and libname='WORK'
and name between 'mean' and 'meanzzzzzz';
select distinct name into :othervars separated by ' '
from dictionary.columns where
memname='HAVE' and libname='WORK'
and not (substr(name,1,3)='std'
or substr(name,1,4)='mean'
or substr(name,1,6)='median');
select
catx(' ',dsn,"(keep=&othervars"
,cats('mean',dsn)
,cats('median',dsn)
,cats('std',dsn)
,')')
Into :dslist separated by ' '
from dsnames;
quit;
Finally:
data &dslist;
set have;
run;
Note this assumes you know the names of the stats (e.g. 'mean','median','std') in advance.
Regards,
Mark
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Kirby, Ted
Sent: Friday, February 18, 2011 6:46 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: More on Selecting Variables by Suffix
I have a dataset with variables that contain statistics along the lines of MeanXXX, MedianXXX, MinXXX, MaxXXX, StdXXX where the prefix indicates the statistic and the suffix (XXX) refers to the variable whose statistic it is. I want to separate these variables into datasets based on the suffix (base variable).
To do this, I would like to use PROC SQL to select variables based on the suffix into a macro variable list and then use that list to create the datasets by dropping the variables I do not want. (There are other variables in the dataset that I do want to keep, so I thought it would be better to drop what I do not want rather than save what I do want.)
I got a good start from an old SAS-L post
(http://www.listserv.uga.edu/cgi-bin/wa?A2=ind0304A&L=sas-l&P=R13849)
but this code deals with a single suffix and I have 5 suffixes (suffices?).
Here is the single-suffix-at-a-time method:
proc sql noprint;
select name
into :GainVars separated by ','
from dictionary.columns
where libname='WORK' and memname='FIMSUMM' and name like '%Gain' ;
select name
into :AdmitVars separated by ','
from dictionary.columns
where libname='WORK' and memname='FIMSUMM' and name like '%Admit' ;
<repeat 3 more times> ;
quit;
I would like to code this logic into macro that looks something like:
%macro SeparateVars(sffx);
proc sql noprint;
select name
into :&sffx.Vars separated by ','
from dictionary.columns
where libname='WORK' and memname='FIMSUMM' and name like '%&sffx.' ;
quit;
%mend /* SeparateVars */;
I would then run do this 5 times:
%SeparateVars(Gain);
%SeparateVars(Admit);
<repeat three more times>
So I could do something like this:
proc sql;
create table GainData as
select * from FIMSumm;
alter table GainData
drop &AdmitVars.,&DischgVars.,&EffVars.,&LOSVars.;
create table AdmitData as
select * from FIMSumm;
alter table AdmitData
drop &GainVars.,&DischgVars.,&EffVars.,&LOSVars.;
<repeat three more times>
quit;
The problem, of course is in the
. . . and name like '%&sffx.'"
part of the WHERE statement in the macro. Enclosing
%&sffx.
in single quotes will not allow the macro processor to evaluate the variable, and enclosing it in double quotes results in the following log
entry:
3621 %macro SeparateVars(sffx);
3622 proc sql noprint;
3623 select name
3624 into :&sffx.Vars separated by ','
3625 from dictionary.columns
3626 where libname='WORK' and memname='FIMSUMM' and name like
"%&sffx." ;
3627 quit;
3628 %mend /* SeparateVars */;
3629 %SeparateVars(Gain);
MLOGIC(SEPARATEVARS): Beginning execution.
MLOGIC(SEPARATEVARS): Parameter SFFX has value Gain
MPRINT(SEPARATEVARS): proc sql noprint;
SYMBOLGEN: Macro variable SFFX resolves to Gain
SYMBOLGEN: Macro variable SFFX resolves to Gain
WARNING: Apparent invocation of macro GAIN not resolved.
MPRINT(SEPARATEVARS): select name into :GainVars separated by ',' from
dictionary.columns where libname='WORK' and
memname='FIMSUMM' and name like "%&sffx." ;
NOTE: No rows were selected.
MPRINT(SEPARATEVARS): quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
MLOGIC(SEPARATEVARS): Ending execution.
Notice the unresolved macro invocation and the "No rows were selected"
note. I have tried to quote
%&sffx.
with various macro quoting functions (%str, %nrstr and %dquote). I have tried quoting it in a %LET statement in the macro (before the PROC SQL
code) and in the WHERE clause of the PROC SQL code, but without success.
(That is not really surprising since, even after reading the documentation and some other sources I still do not understand exactly how macro quoting functions work.)
It has taken me nearly an hour to write this message. Probably one you brilliant SAS-L'ers will solve the problem in 30 seconds.
Thanks for any assistance you can render.
I have SAS 9.1.3 on Windows XP Professional.
kwmacro kwvariable kwlist kwsuffix kwsql kwquoting kwdictionary
************* IMPORTANT - PLEASE READ ********************
This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately.
[Mark Keintz]
