LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (October 2009, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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.
Comments: To: Gerhard Hellriegel <gerhard.hellriegel@t-online.de>
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. >


Back to: Top of message | Previous page | Main SAS-L page