Date: Mon, 22 Nov 2004 13:37:27 -0600
Reply-To: "Dunn, Toby" <Toby.Dunn@TEA.STATE.TX.US>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Dunn, Toby" <Toby.Dunn@TEA.STATE.TX.US>
Subject: Re: Macro Problem?
Content-Type: text/plain; charset="us-ascii"
Micheal,
Consider the doing the following:
%do i=1 %to &obs;
Proc sql noprint;
select name into: varlist separated by " "
from dictionary.columns
where libname = "A" and memname = "&&memname&i";
Quit;
proc print
data = all_data label ;
by centre patient pageno module ;
var visit varlist ;
run;
%end;
HTH
Toby Dunn
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Ross, Michael D
Sent: Monday, November 22, 2004 12:37 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Macro Problem?
Thanks for the great advice!
I used the Sql solution - it worked great.
I ran into another problem - I appended all the datasets and variables
I need into one dataset. Now I'm trying to give a patient profile by
module and I can't seem to eliminate the extraneous variables (ie. if
datset "A" has 10 vars - the code will print those 10 vars in the output
for dataset "B" as well).
I'm trying to research a solution with a "File" statement in the data
step. I'm hoping that it will give me the control I need to generate
this report. Here's my code:
proc sql noprint ;
select memname into : memname1-:memname99
from trans_data;
select name into : name1-:name99
from trans_data;
select vars into : vars1-:vars99
from trans_data;
%let obs = &sqlobs;
quit ;
%macro gener_report;
%do i=1 %to &obs;
Data &&memname&i;
set a.&&memname&i (keep=&&name&i);
module ="&&memname&i";
run;
%end;
Data all_data;/*(index=(aaa=(centre patient pageno)));*/
set %do i=1 %to &obs;
&&memname&i
%end;
;
run;
proc sort data=all_data /*out=all_data (keep=centre patient visit pageno
module)*/;
by centre patient pageno module ;
run;
options firstobs=1 obs=20;
%do i=1 %to &obs;
proc print data=all_data label ;
by centre patient pageno module ;
var visit &&vars&i;
* where module eq "&&memname&i";
run;
%end;
%mend gener_report;
%gener_report;
-----Original Message-----
From: Dunn, Toby [mailto:Toby.Dunn@TEA.STATE.TX.US]
Sent: Saturday, November 20, 2004 8:30 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: FW: Macro Problem?
Micheal,
Good idea wanting to use macros and some meta data to do the hard
repetative work in concatenating alot of data sets, however there was
some problems with the implementation as I see you have recieved some
excellent advice I wont offer a full blown break down of what went wrong
but rather some ideas about how to make your job easier:
1.) For what you want don't use a data step with call symput, I am
slowly realizing that this little function should be reserved for when
there is no better way to get the macro var(s) you want populated with
the text you want from the dataset that you want. What I think should
have been used is Proc SQL with the into statement, (I bet this will
make Sig happy he just loves SQL solutions and is a master at SQL).
2.) the macro you have designed while may very well do what you want it
too, it is too complex and therefore way too hard to read and maintain
for such a simple task.
As it isn't clear as to the intent of what exactly you want the data
step to full do where you want this mass concatenation to happen is, I
will offer only some advice on the macro variables.
try the following:
proc sql noprint;
select memname into : vlst separated by " "
from dictionary.tables
where libname = "A";
quit;
Now you can process this list in your macro by using a %do-loop and a
%let statement, which should if done right allow for a cleaner macro and
a easier read.
This method also allows for creating macro vars in just about anway
fashion you wish from the meta data, and if you are wondering if I
forgot about that var and rec macro you created i haven't.
all you need is to add two more macro vars and build those with the same
SQL procedure.
now lets talk about the macro you are trying to build:
Why i dont see a problem with the where statements inside of the data
set options, I don't think that the retain statemment will do what you
want even if 'mobs' did exist. Why? Well when i = 1 and j = 2 which
will happen as sure as the cows come home you will get this written out
and sent to the SAS
compiler:
data work.revw_lst(drop=<some var name>);
set a.<some memname>(in=inb keep=centre patient <some var name>
where=(<some var name> ^= .));
retain visit ' ';
set a.<some memname>(in=inb keep=centre patient visit
where=(<some var name> ^=
.)); here you are not appending anything, what is going to happen i
believe is that you will effectively merge the two and any overlapping
vars in the second set statement will overwrite those of the first for
as many observations as is in the data set in the second set statement.
I will let you explain what you want to do in this macro more before
offering a solution.
Next,
if inb then do;
dataset = "&&mem&i";
variable = "&&var&i._&j";
value = &&var&i._&j;
end;
Here not a bad idea but you don't need the if then do part, all of the
datasets in the macro have the same data set in operator equal to inb,
so don't use conditional logic here it is very inefficient.
In short write back to us telling us what exactly you want to done and
perhaps a guru or master SAS programmer (which I aint one of yet but
working hard to get there) could come up with a super dupper neato
mesquito solution that will make your life super easy.
Toby Dunn
________________________________
From: SAS(r) Discussion on behalf of Ross, Michael D
Sent: Sat 11/20/2004 9:15 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Macro Problem?
Anyone around on a Saturday? I'm trying to append all dasasets into
one. Any suggestions?
THIS IS THE ERROR I'M GETTING FOR THE CODE BELOW: Any suggestions?
WARNING: Apparent symbolic reference MOBS not resolved.
ERROR: A character operand was found in the %EVAL function or %IF
condition where a numeric
operand is required. The condition was: &MOBS
ERROR: The %TO value of the %DO I loop is invalid.
ERROR: The macro LOOP will stop executing.
MLOGIC(LOOP): Ending execution.
proc contents data=a._all_ out=contents (keep=memname name); run;
options symbolgen mlogic mprint;
data _null_;
set work.contents;
by memname name notsorted;
if first.memname then do;
n+1;
m=1;
call symput("mem"||TRIM(LEFT(PUT(n, 3.))), TRIM(memname));
end;
else m+1;
call symput("var"||TRIM(LEFT(PUT(n, 3.)))||'_'||TRIM(LEFT(PUT(m,
3.))), TRIM(name));
if last.memname then do;
call symput("mobs"||TRIM(LEFT(PUT(n, 3.))), TRIM(LEFT(PUT(m,
3.))));
end;
call symput("recs", TRIM(LEFT(PUT(n, 3.))));
run;
%macro loop;
%do i=1 %to &mobs;
%do j=1 %to &&recs&i;
data work.revw_lst(drop=&&var&i._&j);
%if &i = 1 AND &j = 1 %then
%do;
%if &&mem&i = DDEMOG %then
%do;
set a.&&mem&i(in=inb keep=centre patient
&&var&i._&j
where=(&&var&i._&j ^= .));
retain visit ' ';
%end;
%else
%do;
set a.&&mem&i(in=inb keep=centre patient visit
&&var&i._&j
where=(&&var&i._&j ^= .));
%end;
length dataset variable $8.;
%end;
%else
%do;
%if &&mem&i = DDEMOG %then
%do;
set work.revw_lst
a.&&mem&i(in=inb keep=centre patient
&&var&i._&j
where=(&&var&i._&j ^= .));
%end;
%else
%do;
set work.revw_lst
a.&&mem&i(in=inb keep=centre patient visit
&&var&i._&j
where=(&&var&i._&j ^= .));
%end;
%end;
/*%if "&where" ^= "" %then %STR(if
%TRIM(%QUOTE(&where)););*/
if inb then
do;
dataset = "&&mem&i";
variable = "&&var&i._&j";
value = &&var&i._&j;
end;
format value date9.;
run;
%end;
%end;
%mend loop;
%loop;
-----Original Message-----
From: Richard A. DeVenezia [mailto:radevenz@IX.NETCOM.COM]
Sent: Saturday, November 20, 2004 7:00 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Use of Variable With 21 Characters
Michael F. Murphy wrote:
> Dear SAS-L'ers
>
> I'm reviewing a membership system that includes a variable called
> "Membship", created in the days when variable names could not be more
> than 8 characters long.
>
> Membship is a concatination of four variables:
> Group number converted to a 5-character string
> Subgroup number converted to a 3-character string
> Family Account number converted to an 8-character string Effective
> date of health care coverage converted to a 5-character string.
>
> Hence Membship is a 21 character string.
>
> We are trying to decide whether to keep this variable or replace it
> with the following four numeric variables: Group, Subgroup,
> Family_Acct, and Eff_Date. I am having trouble coming up with reasons
> for keeping Membship, but there may be reasons for keeping it. Can
> anyone think of reasons for keeping this 21-character variable instead
> of the four numeric variables?
>
> Thanks.
A different kind of redesign; instead of storing the numbers with the
member, store them in their own tables. Table 1 : Persons - PersonId,
person Satellites Table 2a : Groups - GroupId, group satellites Table 2b
: PersonsOfGroups - PersonId GroupId, relationship satellites Table 3a :
SubGroups - SubGroupId, subgroup satellites Table 3b :
PersonsOfSubGroups - SubGroupId, PersonId, relationship satellites Table
4a : Familys - FamilyId, subgroup satellites Table 4b : PersonsOfFamilys
- FamilyId, PersonId, relationship satellites etc...
This is an extreme design for simple situations, but it generally scales
well when new data needs to be introduced at different levels or in
respect to data relationships. The (b) type tables relating an aspect
to an object is helpful if the aspect can be or is transactional in
nature.
In this design, the 'data set' a person would most readily call helpful
is really a join of many tables.
--
Richard A. DeVenezia
http://www.devenezia.com/
