Date: Mon, 22 Nov 2004 13:37:14 -0500
Reply-To: "Ross, Michael D" <michael.ross@ASTRAZENECA.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Ross, Michael D" <michael.ross@ASTRAZENECA.COM>
Subject: Re: Macro Problem?
Content-Type: text/plain; charset="iso-8859-1"
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/
