Date: Fri, 2 Oct 2009 20:14:01 +0200
Reply-To: Daniel Fernández <fdezdan@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Daniel Fernández <fdezdan@GMAIL.COM>
Subject: Re: Structured language SAS program.
In-Reply-To: <200910021704.n92AsDlW030492@malibu.cc.uga.edu>
Content-Type: text/plain; charset=ISO-8859-1
hi Gerhard,
perhaps are you thinking me to post here so others do my job meanwhile a take a
swimming at Barcelona beach ??
well, as I said, I will post my solution, I only hope others bring
with others ideas, nothing
more nothing less.
I have been taking a look (as I changed some varnames to english
traslation I made
a bit of erratic steps and the solution I posted when I said 'Need: ' was not
correct.
Well, here comes my code with the right solution:
* first at all, have a good weekend ;
** my code is a mix of SQL and some useful Procs and data steps;
- Mostrar texto citado -
data one;
input MG $1. SCH_NUM $ SAS_SchDate Registry_Date $16. TIPUS_VISIT;
cards;
3 OFT7 18190 08/28/2009
5
3 OFT9 18182 08/31/2009
1
8 uro2 18209 .
1
7 traum55 18232 .
0
7 traum100 18208 08/28/2009
1
7 traum88 18208 .
0
7 traum55 18203 .
0
7 traum100 18198 08/31/2009
1
7 traum88 18208 08/31/2009
1
7 traum55 18202 .
0
7 traum100 18198 .
1
7 traum88 18208 .
1
7 traum55 18199 08/29/2009
0
3 OFT9 18198 .
1
3 OFT9 18198 .
1
3 OFT9 18198 08/23/2009
1
7 traum87 18194 08/28/2009
1
7 traum55 18202 .
1
7 traum100 18200 .
1
7 traum88 18211 .
1
7 traum55 18199 08/28/2009
0
7 traum55 18194 08/31/2009
0
7 traum88 18208 .
0
7 traum88 18208 .
0
7 traum55 18197 08/31/2009
0
7 traum100 18198 08/31/2009
1
7 traum88 18216
1
7 traum55 18197 .
1
7 traum100 18198 .
1
7 traum88 18219 .
0
7 traum88 18216 .
1
;;;
run;
data two;
input group $1. group_name $18. ETC $12.;
cards;
3 Oftalmology BLAHBLAH
1 Nefrology PIKPIK
7 Traumatology PIKPIK
8 Urology PIKPIK
9 Anesthesiology PIKPIK
2 Phycology BLAHBLAH
;;;;
run;
/* SIMPLE calculations to identify occupied or not, total per day and
their disposal rate */
proc sql;
create table ONE2 as
select MG, SAS_SCHDATE,SCH_NUM, count(1) as total,
sum(case when (Registry_Date='' and TIPUS_VISIT in (0)) then 1 else 0
end) as NO_PREFER_VACANT,
sum(case when (Registry_Date='' and TIPUS_VISIT in (1)) then 1 else 0
end) as PREFER_VACANT,
sum(case when Registry_Date='' then 1 else 0 end) as TOTAL_VACANT,
sum(case when Registry_Date NE '' AND TIPUS_VISIT in (0) then 1 else
0 end) as NO_PREFER_OCCUPIED,
sum(case when Registry_Date NE '' AND TIPUS_VISIT in (1) then 1 else
0 end) as PREFER_OCCUPIED,
calculated TOTAL_VACANT/ calculated total as pct_VACANT format = percent6.2
from ONE
where TIPUS_VISIT IN (0,1) /* we eliminate those not equal 1 or 0 */
group by MG, SAS_SCHDATE,SCH_NUM;
run;
/* we identify those with 20% or higher disposal */
data ONE2;
set ONE2;
if pct_VACANT >= 0.20;
run;
proc sort data=ONE2;
by SCH_NUM SAS_SCHDATE;
run;
/* calculates time interval from current day to schedule day and we cut for the
first day that satisfaces 20% already */
data ONE2;
set ONE2;
by SCH_NUM SAS_SCHDATE;
TIME_INTERVAL=SAS_SCHDATE - date();
if FIRST.SCH_NUM then output ONE2;
run;
/* we need to calculate the accumulative occupied visits for all
future schedule days */
proc summary data=ONE2 nway;
class SCH_NUM SAS_SCHDATE ;
var NO_PREFER_OCCUPIED PREFER_OCCUPIED;
output out= ONE2_ocupats (drop= _:) sum()= ;
run;
data ONE2_ocupats;
set ONE2_ocupats end=fin;
by SCH_NUM SAS_SCHDATE;
if first.sch_num and first.SAS_SCHDATE then do;
CUMULATIV_NO_PREFER_TIL_DATE = NO_PREFER_OCCUPIED;
CUMULATIV_PREFER_TIL_DATE = PREFER_OCCUPIED;
end;
else do;
CUMULATIV_NO_PREFER_TIL_DATE ++ NO_PREFER_OCCUPIED;
CUMULATIV_PREFER_TIL_DATE ++ PREFER_OCCUPIED;
end;
run;
/* here we merge for the schedulenumber and sasdate that satisfaces the
table with the very first day already calculated */
proc sort data=ONE2; by SCH_NUM SAS_SCHDATE; run;
data list_20pct_condition;
merge ONE2(in=a)
ONE2_ocupats;
by SCH_NUM SAS_SCHDATE;
if a; * <--- IF ONE3 TABLE that contains the cut day for each
schedule number;
keep MG SAS_SCHDATE SCH_NUM total NO_PREFER_VACANT PREFER_VACANT
TOTAL_VACANT pct_VACANT TIME_INTERVAL CUMULATIV_NO_PREFER_TIL_DATE
CUMULATIV_PREFER_TIL_DATE;
retain MG SAS_SCHDATE SCH_NUM total NO_PREFER_VACANT PREFER_VACANT
TOTAL_VACANT pct_VACANT TIME_INTERVAL CUMULATIV_NO_PREFER_TIL_DATE
CUMULATIV_PREFER_TIL_DATE;
run;
/* we add the literal name for the speciality group */
proc sort data=list_20pct_condition;; by MG ; run;
proc sort data=TWO
out=TWO_literal
(keep= grOup GROUP_NAME
rename=(GROUP=MG GROUP_NAME=SPECIALIST));
by GROUP ; run;
data list_20pct_condition;
SPECIALIST=' ';
retain MG SAS_SCHDATE SPECIALIST SCH_NUM total NO_PREFER_VACANT PREFER_VACANT
TOTAL_VACANT pct_VACANT TIME_INTERVAL CUMULATIV_NO_PREFER_TIL_DATE
CUMULATIV_PREFER_TIL_DATE;
merge list_20pct_condition (in=a)
TWO_LITERAL (IN=B);
by MG;
if a AND B;
DROP MG SAS_SCHDATE;
run;
proc delete data= one2 one2_ocupats two_literal ; run;
Daniel Fernandez.
Barcelona.
2009/10/2 Gerhard Hellriegel <gerhard.hellriegel@t-online.de>:
> I think it's a complete missunderstanding of the purpose of the list! What
> you write is something like "I have a piece of work to do with SAS at my
> site and I don't want to do it. I simply send it to the list and copy the
> result of the post in my editor window. Then my work is done and I go
> home...". All of us (ok, I think the most) have that problem. Everybody is
> responsable for his work and hopefully is payed for it. There are some
> people here which have good SAS skills and many are very clever. That is a
> very good combination if you are stuck in a detail problem and don't find
> a good solution. I also had sometimes a solution and was not lucky (bad
> performance, not elegant, not exactly what's needed) and I posted that
> anyway. Most of the posts contain things that I cannot get to run.
> Sometimes there is a sophisticated thing, for example a special graphic
> with things in it, which are not standard. I'd never post such a thing
> without trying it to do it myself!
> If you look at postings, there is always something like: "I tried to do
> this and that, but I was not successful. My current code is...". Sometimes
> also: "I have to use 4 axis for a plot, but I can only have 2. Is that
> possible?"
> Questions like "I have to produce a report until the end of the week, but
> I don't have time to do it" might (hopefully) not be answered. Hopefully,
> because many of the people which are very active parts of that list, are
> SAS consultants. It is great, that these people share their knowledge
> without getting any money for that. Note that this is not self-evident!
> But I think each of them will have a problem to do your WORK! You can get
> and pay those experts, if you want them to do work for you, but don't
> expect to get it for free! Nobody has a problem with a thing which costs 2
> minutes to type, many people have a problem to do other's work!
> Only my opinion, maybe others are willing to do your work for nothing...
>
> Gerhard
>
>
>
>
> On Fri, 2 Oct 2009 10:28:51 -0400, Daniel Fern=?ISO-8859-1?Q?=C3=A1ndez?=
> <fdezdan@GMAIL.COM> wrote:
>
>>What I like from SAS-L are the simple one or two code steps to resolve
>>concrete problems users post.
>>I admit I am learning a lot from all of you, a lot more than I thought.
> But
>>what I miss
>>in this forum is the different methods to resolve real problems at our
>>daily/weekly
>>job tasks. I know it is hard to explain some specific real job problems
>>situations with a morass of details, the time it takes to illustrate the
>>problem with fake data and varnames could exhaust some SAS-L-users's
>>patience but I will make a try for first time ( I think).
>>
>>I am rethinking about a problem situation at my job in a public Hospital
> in
>>Barcelona ( I am in charge of extraccting information about Hospital
>>functionality (some type of so called
>>Business Analytics , perhaps a bit incongruent inside public enterprises
>>that focus more into saving lives and human rights than money as final
>>target) as patients flow, waiting times, surgery occupancy, bed and floor
>>units occupancy, etc etc...
>>
>>Imagine fake dataset ONE talks about:
>>medical_group (MG)
>>SCHEDULETIME_NUMBER (SCH_NUM) <-- one o more sch_num for each
> medical_group
>>depends on different Doctors teams.
>>schedule_day_visits (SAS_SCHDATE) <--- future calendar for medical
>>attendance,
>>if is completed then it is occupied, else it is vacant (datasas format)
>>confirmed_visit_date (REGISTRY_DATE) <-- if registry date exists, then a
>>visit hole
>>is occupied for this day (notice more than a visit/day exist), mmddyyyy
> SAS
>>date type
>>tipus_visit (TIPUS_VISIT) <--- depends on the Doctors priority criteria
>>(preference=1, no_preference=0, else it is not first_visit (perhaps second
>>visit,etc...)
>>and we want only to account for first visit (tipus 0 or 1)).
>>
>>data one;
>>input MG $1. SCH_NUM $ SAS_SchDate Registry_Date $16. TIPUS_VISIT;
>>cards;
>>3 OFT7 18190 08/28/2009
>> 5
>>3 OFT9 18182 08/31/2009
>> 1
>>8 uro2 18209 .
>> 1
>>7 traum55 18232 .
>> 0
>>7 traum100 18208 08/28/2009
>> 1
>>7 traum88 18208 .
>> 0
>>7 traum55 18203 .
>> 0
>>7 traum100 18198 08/31/2009
>> 1
>>7 traum88 18208 08/31/2009
>> 1
>>7 traum55 18202 .
>> 0
>>7 traum100 18198 .
>> 1
>>7 traum88 18208 .
>> 1
>>7 traum55 18199 08/29/2009
>> 0
>>3 OFT9 18198 .
>> 1
>>3 OFT9 18198 .
>> 1
>>3 OFT9 18198 08/23/2009
>> 1
>>7 traum87 18194 08/28/2009
>> 1
>>7 traum55 18202 .
>> 1
>>7 traum100 18200 .
>> 1
>>7 traum88 18211 .
>> 1
>>7 traum55 18199 08/28/2009
>> 0
>>7 traum55 18194 08/31/2009
>> 0
>>7 traum88 18208 .
>> 0
>>7 traum88 18208 .
>> 0
>>7 traum55 18197 08/31/2009
>> 0
>>7 traum100 18198 08/31/2009
>> 1
>>7 traum88 18216 .
>> 1
>>7 traum55 18197 .
>> 1
>>7 traum100 18198 .
>> 1
>>7 traum88 18219 .
>> 0
>>7 traum88 18216 .
>> 1
>>;;;
>>run;
>>
>>
>>Next, imagine dataset TWO with unique names for each medical specialist
>>group ('group' varname
>> is linked to 'mg' varname in dataset ONE):
>>
>>data two;
>>input group $1. group_name $18. ETC $12.;
>>cards;
>>3 Oftalmology BLAHBLAH
>>1 Nefrology PIKPIK
>>7 Traumatology PIKPIK
>>8 Urology PIKPIK
>>9 Anesthesiology PIKPIK
>>2 Phycology BLAHBLAH
>>;;;;
>>run;
>>
>>
>>Here, it comes that hardest part of the problem. Explained patients flow
>>for this situation:
>>
>>When a person feels bad he/she goes to the physician in his/her
>>neighbourhood, who is not
>>a Doctor specialist, then this physician send him/her to the Hospital, the
>>Doctors at the
>>Hospital determine if it is a priority visit or not, and later Hospital's
>>Patients Management Dept.
>>determines the tipus of visit and schedule time in concurrence of Doctor's
>>opinion.
>>
>>What Hospital's Patients Management Dept. wants to know is the interval
>>time
>>for the very first_visit for each scheduletime_number (calculated like
> that
>>day with more 20% disposal first_visits type,
>>for example if a scheduletime_number as 'traum88' have 10 visits/day and
>>only 1 vacant it means 10% disposal, and as
>>long as some surgeons and Doctors re-arrange their schedule, this 10%
> could
>>be a low significative to know the waiting days
>>for a patient to visit his/her Doctor specialist at Hospital.)
>>Second, Hospital's Patients Management Dept. want to know too how many
>>occupied visits exists until this cutting day
>> with more than 20% disposal (accumulative/cumulative number for each
>>preference/not_preference).
>>
>>Definitely, what I would like to see is a nice efficient, clever and clean
>>SAS code, in a sequential structured language
>>that runs steps for each fase until get the final target (I really doubt
>>one data step made all work at once).
>>
>>Need:
>>
>>SPECIALIST SCH_NUM total NO_PREFER_VACANT PREFER_VACANT TOTAL_VACANT
>>pct_VACANT TIME_INTERVAL CUMUL_NO_PREFER CUMUL_PREFER
>>Oftalmology OFT9 3 0 2 2 67%
>> 26 0 2
>>Traumatology traum55 2 0 1 1 50%
>> 25 2 1
>> traum100 4 0 2 2 50%
>> 26 2 3
>> traum100 5 3 1 4 80%
>> 36 4 4
>> traum88 5 3 1 4 80%
>> 36 4 5
>>Urology uro2 1 0 1 1 100%
>> 37 0 0
>>
>>
>>Finally, I invite others to post real job problem, how they have to
>>struggle to get to the target table
>>through differents steps (what I call structured language programming).
>>
>>
>>I will post my particular solution.
>>
>>thank you very much.
>>
>>Daniel Fernández.
>>Barcelona.
>
| 
