Date: Fri, 13 May 2011 09:59:03 -0500
Reply-To: Joe Matise <snoopy369@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Joe Matise <snoopy369@GMAIL.COM>
Subject: Re: Macro Help Needed
In-Reply-To: <201105131451.p4DAlWQk022509@waikiki.cc.uga.edu>
Content-Type: text/plain; charset=ISO-8859-1
declare hash h(dataset:"DAVE");
would be the way to go.
Basic HASH primer:
start with declare hash h(dataset:"NAMEOFDATASET");
then
h.defineKey("KEYVARIABLE"); as many times as you have key variables
then
h.defineData("DATAVARIABLE"); as many times as you have data variables
(variable to return - we have none so no defineData lines)
then
h.defineDone();
then
rc = h.find(); to just find using the variable names as listed in dataset
(so if your hash dataset and your set dataset have same variable names for
the hash keys)
or
rc = h.find(key:key1, key:key2, ...) to use different variables (use key
order as defined).
rc will be 0 if it finds a match, or some -16.... number if not matched.
Also, you need to define &sitelist. Do you know how to use PROC SQL/SELECT
INTO?
PROC SQL;
select distinct site into :sitelist separated by ' ' from DAVE where not
missing(site);
select distinct lab into :arrlist separated by ' ' from DAVE where not
missing(lab);
quit;
will generate a macro variable &sitelist with all of the different sites in
it, and &arrlist with the different labs in it.
-Joe
On Fri, May 13, 2011 at 9:51 AM, Dave Brewer <david.brewer@uc.edu> wrote:
> Hi Joe,
>
> I will have a different list of labs for each site, so I took your
> suggestion and placed that into an Excel spreadsheet and read that into
> SAS...this dataset is called DAVE.
>
> I am not at all familiar with the hash commands, so I know I have to read
> up on that.
>
> I changed
> declare hash h(dataset:"labsite");
>
> to:
> declare hash h(DAVE:"labsite");
>
> and get the following errors in the log:
> 38 data misslabs;
> 39 if _n_ = 1 then do;
> 40 declare hash h(dave:"labsite");
> ERROR: Unknown argument tag dave.
> 41 h.defineKey("site");
> 42 h.defineKey("lab");
> 43 h.defineDone();
> 44 end;
> 45 set out.ipdata;
> 46 where site in (&sitelist); *generate this from the labsite dataset,
> using PROC SQL/SELECT INTO;
> -
> 22
> 76
> WARNING: Apparent symbolic reference SITELIST not resolved.
> ERROR: Syntax error while parsing WHERE clause.
> 47 array misslab &arrlist; *this looks like: wbc_miss cr_miss
> glucose_miss hct_miss, defined above
> -
> 387
> 200
> 76
> 47 ! ;
> WARNING: Apparent symbolic reference ARRLIST not resolved.
> ERROR 22-322: Syntax error, expecting one of the following: a quoted
> string, a numeric constant,
> a datetime constant, a missing value, -.
>
> ERROR 76-322: Syntax error, statement will be ignored.
>
> ERROR 387-185: Expecting a list of variables.
>
> ERROR 200-322: The symbol is not recognized and will be ignored.
>
> 48 do __t = 1 to dim(misslab);
> 49 if misslab[__t]=1 then do;
> ERROR: Too many array subscripts specified for array misslab.
> 50 tempname = scan(vname(misslab[__t]),1,"_");
> ERROR: Too many array subscripts specified for array misslab.
> 51 rc = h.find(key:site, key:tempname);
> ERROR: DATA STEP Component Object failure. Aborted during the COMPILATION
> phase.
> NOTE: The SAS System stopped processing this step because of errors.
>
>
> What am I doing wrong?
>
> Thanks so much for staying with me.
> Dave
>
> On Fri, 13 May 2011 08:23:10 -0500, Joe Matise <snoopy369@GMAIL.COM>
> wrote:
>
> >If you're using the same set of labs for all of the sites in that run, you
> >need to do nothing but add a where site in (...) statement to that code.
> >You certainly don't need multiple datasets; as I said, using BY SITE; and
> >the appropriate tagset options will generate a separate sheet per site.
> >
> >Now, if you have different lists of labs for each site, it's slightly (but
> >only slightly!) more complex. There are a few (dozen...) different ways
> to
> >go about it, but I'd load the site/lab combinations in a spreadsheet; load
> >that into sas, and end up with a vertical table like so:
> >402 cr
> >402 wbc
> >402 hct
> >539 bun
> >539 wbc
> >539 glucose
> >
> >Then:
> >
> >data misslabs;
> >if _n_ = 1 then do;
> > declare hash h(dataset:"labsite");
> > h.defineKey("site");
> > h.defineKey("lab");
> > h.defineDone();
> >end;
> >set out.ipdata;
> >where site in (&sitelist); *generate this from the labsite dataset, using
> >PROC SQL/SELECT INTO;
> >array misslab &arrlist; *this looks like: wbc_miss cr_miss glucose_miss
> >hct_miss, defined above;
> >do __t = 1 to dim(misslab);
> > if misslab[__t]=1 then do;
> > tempname = scan(vname(misslab[__t]),1,"_");
> > rc = h.find(key:site, key:tempname);
> > if rc = 0 then keeprec=1;
> > end;
> >end;
> >if keeprec=1;
> >keep &arrlist site visn admission_date discharge_date unitentry unitexit
> >reporting_period;
> >run;
> >
> >That looks up the name of the lab in the hash table and the site, and
> >verifies that it's one of the ones you want for that site. Then you can
> go
> >forward with this dataset and do the reporting as before. You could do
> this
> >step a lot of other ways - for example, with just a list of site and a
> text
> >list of labs you could use SCAN and a second do loop to iterate over the
> >labs); slower than a hash by a lot, but still not terribly inefficient,
> >especially if you usually don't have more than a few. This is completely
> >expansible (you can have 2 labs or 150, this wouldn't care, and as many
> >sites as you want).
> >
> >The idea is to use a single dataset, because it's not only more efficient
> in
> >processing (if you have millions of records or something like that, this
> may
> >matter), but it's also a lot easier to manage and QA (QAing your QA!).
> >Macro looping repeatedly is much, much slower, and messy.
> >
> >-Joe
> >
> >On Fri, May 13, 2011 at 8:01 AM, Dave Brewer <david.brewer@uc.edu> wrote:
> >
> >> Hi Joe,
> >>
> >> Thanks for the suggestion, but I think I need a macro.
> >>
> >> I probably didn't make myself clear. Not all sites will need to be
> >> reported. Only certain sites for specific labs will be reported for a
> >> specific reporting period.
> >>
> >> Each set of results for that site will then be written to a specific tab
> >> named for that site, so I think I need to create multiple datasets as
> >> input to the PROC REPORT and EXCELXP tagsets.
> >>
> >> Does this make my needs clearer?
> >>
> >> Thanks again.
> >> Dave
> >>
> >>
> >> On Fri, 13 May 2011 07:48:56 -0500, Joe Matise <snoopy369@GMAIL.COM>
> >> wrote:
> >>
> >> >In general, you don't need to use a macro to accomplish what you're
> doing
> >> >here.
> >> >
> >> >The first part (generating the missing data by lab) can be generated
> in a
> >> >single data step, like so (untested so may have some typos):
> >> >
> >> >
> >> >data misslabs;
> >> >set out.ipdata;
> >> >array misslab &arrlist; *this looks like: wbc_miss cr_miss glucose_miss
> >> >hct_miss, defined above;
> >> >do __t = 1 to dim(misslab);
> >> > if misslab[__t]=1 then keeprec=1;
> >> >end;
> >> >if keeprec=1;
> >> >keep &arrlist site visn admission_date discharge_date unitentry
> unitexit
> >> >reporting_period;
> >> >run;
> >> >
> >> >You would want to put "cr" "wbc" "hct" into a macro variable at the
> top of
> >> >your program for ease of modification, and if this is something you may
> >> want
> >> >to have lesser skilled users use, or if you have a ton of these, you
> might
> >> >want to just make a spreadsheet with all of the possible labs in it and
> >> >place a '1' next to each one that you want to run, and bring it in to
> your
> >> >program that way (generating each list you need to use that way).
> >> >
> >> >Then to do your PROC REPORT, I believe that if you are using the latest
> >> >version of ODS TAGSETS.EXCELXP instead of MSOFFICE_2K that you can use
> a
> >> BY
> >> >Statement (BY SITE) to generate individual tabs for each site, as long
> as
> >> >you have it set up properly in the ods options. Google around for the
> >> right
> >> >setting for that as I don't recall it off the top of my head, but I've
> >> >definitely done that before. Possible MSOFFICE_2K also allows that,
> but
> >> in
> >> >general EXCELXP is superior in my opinion.
> >> >
> >> >-Joe
> >> >
> >> >On Fri, May 13, 2011 at 7:33 AM, Dave <david.brewer@uc.edu> wrote:
> >> >
> >> >> Hi SAS-Lers
> >> >>
> >> >> I am need of some macro guidance.
> >> >>
> >> >> The situation:
> >> >> I am QAing hospital lab results and want to report on patients who
> are
> >> >> missing certain lab results in a specific hospital and time period.
> >> There
> >> >> can be anywhere from one to 20 labs that I need to examine for each
> >> >> patient. The report will be written to an Excel spreadsheet with each
> >> tab
> >> >> being the different site in the macro call.
> >> >>
> >> >> Here is what I have so far:
> >> >>
> >> >> OPTIONS SYMBOLGEN MPRINT MLOGIC;
> >> >>
> >> >> libname out "D:\Missing Labs";
> >> >>
> >> >> %Macro MissLabs(lab1=, lab2=, lab3=, site=, period=);
> >> >> data &lab1.miss ;
> >> >> set out.ipdata(keep=id site visn &lab1._miss admission_date
> >> >> discharge_date unitentry unitexit reporting_period) ;
> >> >> where &lab1._miss = 1 and site = "&site" and reporting_period
> >> >> = "&period" ;
> >> >> drop reporting_period ;
> >> >> run;
> >> >>
> >> >> data &lab2.miss;
> >> >> set out.ipdata(keep=id site visn &lab2._miss admission_date
> >> >> discharge_date unitentry unitexit reporting_period) ;
> >> >> where &lab2._miss = 1 and site = "&site" and reporting_period
> >> >> = "&period" ;
> >> >> drop reporting_period ;
> >> >> run;
> >> >>
> >> >> data &lab3.miss;
> >> >> set out.ipdata(keep=id site visn &lab3._miss admission_date
> >> >> discharge_date unitentry unitexit reporting_period) ;
> >> >> where &lab3._miss = 1 and site = "&site" and reporting_period
> >> >> = "&period" ;
> >> >> drop reporting_period ;
> >> >> run;
> >> >>
> >> >> data all;
> >> >> merge &lab1.miss &lab2.miss &lab3.miss ;
> >> >> by id;
> >> >> run;
> >> >>
> >> >>
> >> >>
> >> >> ODS ESCAPECHAR="^";
> >> >> ODS LISTING CLOSE;
> >> >>
> >> >> ODS TAGSETS.MSOFFICE2K_X FILE="d:\missing
> >> >> labs\MissingLabs_&sysdate..XLS"
> >> >> OPTIONS(ORIENTATION="LANDSCAPE" FROZEN_HEADERS="4"
> >> >> FROZEN_ROWHEADERS="2"
> >> >> SHEET_NAME="SITE&SITE" fit2page="yes")
> >> >> STYLE=STYLES.SUGI31 Headtext="<style> h1 {margin:0}
> </style>";
> >> >> PROC REPORT DATA=ALL NOWD ;
> >> >> COLUMN ID VISN SITE ADMISSION_DATE DISCHARGE_DATE UNITENTRY
> >> >> UNITEXIT &lab1._miss &lab2._miss &lab3._miss ;
> >> >> WHERE SITE="&SITE" ;
> >> >> DEFINE ID / DISPLAY "ID" style(column)={htmlstyle="mso-number-
> >> >> format:\@"}; * This is a character $20 but displays in scientific
> >> notation
> >> >> */
> >> >> DEFINE VISN / DISPLAY "VISN" ;
> >> >> DEFINE SITE / DISPLAY "Site" ;
> >> >> DEFINE ADMISSION_DATE / DISPLAY "Admission Date" ;
> >> >> DEFINE DISCHARGE_DATE / DISPLAY "Discharge Date" ;
> >> >> DEFINE UNITENTRY / DISPLAY "Unit Entry Date" ;
> >> >> DEFINE UNITEXIT / DISPLAY "Unit Exit Date" ;
> >> >> DEFINE &lab1._miss / DISPLAY ;
> >> >> DEFINE &lab2._miss / DISPLAY ;
> >> >> DEFINE &lab3._miss / DISPLAY;;
> >> >>
> >> >> COMPUTE ID;
> >> >> BG + 1;
> >> >> IF mod(bg, 2) = 1 then call define(_row_, "style", "style=
> >> >> {background=white}");
> >> >> ELSE call define(_row_, "style", "style=
> >> {background=CXE8EDD5}");
> >> >> ENDCOMP;
> >> >>
> >> >> TITLE justify=left "^S={font=('Times Roman',13PT,Bold Italic)}
> >> >> Missing Labs Report" ;
> >> >> TITLE2 justify=left "^S={font_face=Arial font_size=11pt}
> Reporting
> >> >> Period: &PERIOD" ;
> >> >> FOOTNOTE justify=left "^S={font_face=Arial font_size=9pt}
> Report
> >> >> Date: %LEFT( %sysfunc(today(),worddatx.) )";
> >> >> RUN;
> >> >> ODS TAGSETS.MSOFFICE2K_X CLOSE;
> >> >> %Mend MissLabs ;
> >> >>
> >> >>
> >> >> %MissLabs(lab1=wbc, lab2=cr, lab3=hct, site=402, period=2011Q2)
> >> >> %MissLabs(lab1=bun, lab2=cr, lab3=glucose, site=539, period=2011Q2)
> >> >>
> >> >> ODS LISTING;
> >> >>
> >> >> The first macro call works but is sloppy and is good for only those
> >> cases
> >> >> where I am looking for three lab tests.
> >> >>
> >> >> What I need help in is modifying this macro to accept any number of
> labs
> >> >> from 1 to 20. In some cases, I might need to only select one lab and
> the
> >> >> next time will be 12 labs.
> >> >>
> >> >> I also am having trouble creating the spreadsheet with multiple
> >> sites...I
> >> >> only get one site in the spreadsheet. I imagine I have to somehow
> loop
> >> >> through the PROC REPORT for each site in order to write each site to
> its
> >> >> own tab, but this has me stumped.
> >> >>
> >> >> Any and all suggestions will be greatly appreciated.
> >> >>
> >> >> Thank you.
> >> >> Dave
> >> >>
> >>
>
| 
