About tmm

tmm · ‎08-16-2012

I have some data and proc report code that I want to record a pop-up macro for. I have a list of 1000 possible numbers that can be entered and when a number is entered such as 00460, the code created pulls the data for that number only. An example of the code is this: data one; set all13; if code = '00460'; run; proc sql; create table temp as (select distinct clm from one where rv_cd in ('589','270','462','012')); quit; and the code really goes on and on to pull various information in regards to that code 00460. When I get to the end of the code the final piece is an html output of the report that was derived from all the proc sql, data statements of this code. What I want is to create a macro that will start with a pop-up window asking the user to enter the code they want to run the report on. So say the pop says please enter code and you enter 00100 and then the code between the macro runs all the way to the output and the user never has to do anything but input that code into the popup window. Therefore there is no need to go in and change anything in regards to the actual data. I am just trying to make this so they don't have to re-enter a new code in the actual code everytime if that is possible.

tmm · ‎07-28-2012

I get the following error when running this code: (FYI, the code runs fine on 2 other machines that have the same OS and same SAS version as me) ERROR: The POINT= data set option is not valid for the data set CARDIAC.FINAL9K, the data set must be accessible by observation number for POINT= processing. NOTE: The SAS System stopped processing this step because of errors. NOTE: There were 1 observations read from the data set CARDIAC.FINAL9K. The code: options compress=NO reuse=yes; data cardiac.final9K2; set cardiac.final9K nobs=last_obs; by mbr_sys_id; if prov_mpin_pci=1 and fst_srvc_dt<=&enddt3-30 then do; wb = fst_srvc_dt + 1; we = fst_srvc_dt + 30; do pt = _N_ + 1 to last_obs; set cardiac.final9K(Keep= mbr_sys_id fst_srvc_dt nat_stemi nat_nstemi nat_cabg nat_pci rename=(mbr_sys_id=mem fst_srvc_dt=dt nat_stemi=stemi nat_nstemi=nstemi nat_cabg=ncabg nat_pci=npci)) point=pt; if mbr_sys_id ne mem or dt > we then leave; if dt >= wb and dt<=we then fu_30 = sum(fu_30, ncabg, npci); if dt >= wb and dt<=we and nat_stemi=1 then Stemi_FU_30 = sum(Stemi_FU_30, ncabg, npci); if dt >= wb and dt<=we and nat_nstemi=1 then nStemi_FU_30 = sum(nStemi_FU_30, ncabg, npci); end; output; end; if we =. then we=fst_srvc_dt; if wb=' ' then wb=fst_srvc_dt; if we=wb then output; drop wb we mem dt ncabg npci pt nstemi stemi; run;

tmm · ‎07-22-2012

I noticed in the table there are allowed_amt's that are not = but there are 2 ID's Can I do this: data want; set have; by id; if not first.id then allowed = 0 where allowed_amt = allowed_amt; run;

tmm · ‎07-20-2012

I am doing an update query and trying to update amounts that are duped by ID, by date. The raw data looks like this taxid id dos allowed 111 001 29nov10 100.00 123 001 29nov10 100.00 894 002 15nov10 50.00 908 002 15nov10 50.00 I want to do the update query so the results look like this taxid id dos allowed 111 001 29nov10 100.00 123 001 29nov10 0 894 002 15nov10 50.00 908 002 15nov10 0 Because 2 different taxids saw the patient it pulls the claim allowed amt 2 times. I need to keep the 2 taxid's in the table to show the patient was serviced by 2 different providers but i only need 1 of the allowed charges so the charges are not being over counted. I thought something like: proc sql; update sum2 set last allowed = 0 where count(id) > 1 and dos=dos and allowed = allowed; run;

tmm · ‎07-17-2012

I am trying so hard to explain this correctly. This table just has multiple scenarios and I am doing my best. Ok Maybe this will help. I have a table of 125k+ rows all with the ID, dos, disch date and place of service. There are various options within this table. I went thru all 125k+ rows to find all the options. I need to categorize all hospitals in one hospital table but if they are associated with a hospice, rehab, nursing, skilled admission then they are to go to an other table, only if they are within a 30day period. Then if there is only a hospice, rehab, nursing, skilled admission...they need to be in the other table. I am using 999 as an example. 999 has a hospital with disch 15jan2010 but then another hospital admission 20feb2010 which is less than the 30days but because the hospital is followed by another hospital, the first hospital would fall into the hospital table. The second hospital is associated with the rehab and skilled so all 3 need to be in the other table. I hope this is enough information. This is my third time trying to explain this correctly. data have; informat dos disch date8.; format dos disch date8.; input id plc $ dos disch ; cards; 111 hospital 15jun2010 18jun2010 111 hospital 11dec2010 15dec2010 111 hospice 15dec2010 17dec2010 111 rehab 15jan2011 31jan2011 111 hospital 12nov2011 15nov2011 222 hospital 01jan2010 10jan2010 333 hospital 01feb2011 07feb2011 444 hospital 01jun2010 07jun2010 444 rehab 07jun2010 15jun2010 444 nursing 15jun2010 17jun2010 444 hospital 17aug2011 30aug2011 555 rehab 15aug2011 29aug2011 666 hospital 01jan2010 02jan2010 777 skilled 06aug2011 15aug2011 888 hospice 10aug2011 30aug2011 999 hospital 01jan2010 15jan2010 999 hospital 20feb2010 25feb2010 999 rehab 27feb2010 28feb2010 999 skilled 03mar2010 07mar2010 001 hospital 20aug2010 25aug2010 001 hospital 15sep2011 15sep2011 002 rehab 05sep2011 07sep2011 002 hospital 08sep2011 15sep2011 002 hospice 15sep2011 17sep2011 003 hospital 01jan2010 15jan2010 004 rehab 15jan2010 17jan2010 005 hospital 01jan2011 15jan2011 005 rehab 27jan2011 30jan2011 ; run; I need to have hospital only items that are never associated with rehab, hospice, skilled put into one table and the rest in the other table. So the outcome of this above should be like this: using up to +30 between the disch and the adm. So in the case of ID 111 if they are discharged from the hospital on 15dec2010 and then admitted to rehab on 15jan2011, the entire record fo 111 during the date periods would be in the other table. HOSPITAL Table: 111 hospital 15jun2010 18jun2010 111 hospital 12nov2011 15nov2011 222 hospital 01jan2010 10jan2010 333 hospital 01feb2011 07feb2011 444 hospital 17aug2011 30aug2011 666 hospital 01jan2010 02jan2010 999 hospital 01jan2010 15jan2010 001 hospital 20aug2010 25aug2010 001 hospital 15sep2011 15sep2011 003 hospital 01jan2010 15jan2010 OTHER Table: data have; informat dos disch date8.; format dos disch date8.; input id plc $ dos disch ; cards; 111 hospital 11dec2010 15dec2010 111 hospice 15dec2010 17dec2010 111 rehab 15jan2011 31jan2011 444 hospital 01jun2010 07jun2010 444 rehab 07jun2010 15jun2010 444 nursing 15jun2010 17jun2010 555 rehab 15aug2011 29aug2011 777 skilled 06aug2011 15aug2011 888 hospice 10aug2011 30aug2011 999 hospital 20feb2010 25feb2010 999 rehab 27feb2010 28feb2010 999 skilled 03mar2010 07mar2010 002 rehab 05sep2011 07sep2011 002 hospital 08sep2011 15sep2011 002 hospice 15sep2011 17sep2011 004 rehab 15jan2010 17jan2010 005 hospital 01jan2011 15jan2011 005 rehab 27jan2011 30jan2011

tmm · ‎07-17-2012

Ok Maybe this will help. I have a table of 125k+ rows all with the ID, dos, disch date and place of service. There are various options within this table. I went thru all 125k+ rows to find all the options. data have; informat dos disch date8.; format dos disch date8.; input id plc $ dos disch ; cards; 111 hospital 15jun2010 18jun2010 111 hospital 11dec2010 15dec2010 111 hospice 15dec2010 17dec2010 111 rehab 15jan2011 31jan2011 111 hospital 12nov2011 15nov2011 222 hospital 01jan2010 10jan2010 333 hospital 01feb2011 07feb2011 444 hospital 01jun2010 07jun2010 444 rehab 07jun2010 15jun2010 444 nursing 15jun2010 17jun2010 444 hospital 17aug2011 30aug2011 555 rehab 15aug2011 29aug2011 666 hospital 01jan2010 02jan2010 777 skilled 06aug2011 15aug2011 888 hospice 10aug2011 30aug2011 999 hospital 01jan2010 15jan2010 999 hospital 20feb2010 25feb2010 999 rehab 27feb2010 28feb2010 999 skilled 03mar2010 07mar2010 001 hospital 20aug2010 25aug2010 001 hospital 15sep2011 15sep2011 002 rehab 05sep2011 07sep2011 002 hospital 08sep2011 15sep2011 002 hospice 15sep2011 17sep2011 003 hospital 01jan2010 15jan2010 004 rehab 15jan2010 17jan2010 005 hospital 01jan2011 15jan2011 005 rehab 27jan2011 30jan2011 ; run; I need to have hospital only items that are never associated with rehab, hospice, skilled put into one table and the rest in the other table. So the outcome of this above should be like this: using up to +30 between the disch and the adm. So in the case of ID 111 if they are discharged from the hospital on 15dec2010 and then admitted to rehab on 15jan2011, the entire record fo 111 during the date periods would be in the other table. HOSPITAL Table: 111 hospital 15jun2010 18jun2010 111 hospital 12nov2011 15nov2011 222 hospital 01jan2010 10jan2010 333 hospital 01feb2011 07feb2011 444 hospital 17aug2011 30aug2011 666 hospital 01jan2010 02jan2010 999 hospital 01jan2010 15jan2010 001 hospital 20aug2010 25aug2010 001 hospital 15sep2011 15sep2011 003 hospital 01jan2010 15jan2010 OTHER Table: data have; informat dos disch date8.; format dos disch date8.; input id plc $ dos disch ; cards; 111 hospital 11dec2010 15dec2010 111 hospice 15dec2010 17dec2010 111 rehab 15jan2011 31jan2011 444 hospital 01jun2010 07jun2010 444 rehab 07jun2010 15jun2010 444 nursing 15jun2010 17jun2010 555 rehab 15aug2011 29aug2011 777 skilled 06aug2011 15aug2011 888 hospice 10aug2011 30aug2011 999 hospital 20feb2010 25feb2010 999 rehab 27feb2010 28feb2010 999 skilled 03mar2010 07mar2010 002 rehab 05sep2011 07sep2011 002 hospital 08sep2011 15sep2011 002 hospice 15sep2011 17sep2011 004 rehab 15jan2010 17jan2010 005 hospital 01jan2011 15jan2011 005 rehab 27jan2011 30jan2011

tmm · ‎07-16-2012

I have an inpatient table and in that table are various dates of admission and discharges. A little over 125k rows in all. Member can have more than 1 admission to everything from hospital, nursing, rehab, hospice. Any hospital only admission where the member clearly only has 1 admission in the entire table goes into the HOSPITAL TABLE. If the member had more than 1 admission but they were never sent to nursing, rehab or hospice, that goes in the HOSPITAL TABLE. The tricky part for the other table. If a member has an admission to a hospital and then is discharged and then admitted to a nursing, rehab or hospice the same date of the discharge, that goes into the OTHER TABLE. If the member has a hospital admission and then is discharged and then admitted to a nursing, rehab or hospice within 30 days that goes into the OTHER TABLE. (I am making the call on 30days to make this easier). If the member was only admitted to rehab, nursing or hospice and never had a hospital stay, that goes into the OTHER TABLE. I hope this makes sense. The problem is I am doing this readmission project and it was all fine and done and then they threw in place of service. They said separate hospital from all others. I did that and the odds ratio was so off. The readmits were right but the admissions were too high because those that were admitted to hospital and readmitted to hospice, nursing or rehab their original admissions are counted in the first query I did because they have place of service as hospital. I think I have confused everyone haven't I?

tmm · ‎07-16-2012

*Isolate into episodes; data episodes; set have; by id; retain episode; *keep track of previous discharge date; format prev_disch date8.; prev_disch=lag(disch); *Calculate the time from previous stay, if this is not a new id; if first.id then episode =1; if not first.id then time_from_previous_stay=dos-prev_disch; if time_from_previous_stay =>1 or time_from_previous_stay <=30 then episode+1; *Create id to group episode for the query later on; new_id=put(id, z3.)||put(episode, z2.); run; *Only Hospitals; proc sql; create table hospital as select * from episodes group by new_id having min(plc)='hospital' and max(plc)='hospital'; quit; *All the others; proc sql; create table others as select * from episodes group by new_id having min(plc)ne 'hospital' or max(plc)ne 'hospital'; quit; Maybe the time issue of between does not work so I changed it to the above. The query runs but still not right output and I am lost. This project is more than I can handle.

tmm · ‎07-16-2012

Sorry this has so many twists and turns. Nature of the data we store. I just wished I was well versed in SAS so I did not have to ask for help and frustrate everyone. If I had someone internally to go to I would this way I could share the REAL information but cannot with outside world as it would be a violation of PHI and HIPAA and I could lose my job and get fined by the govt. So I am doing the best I can with providing the items I am finding. I am fairly to SAS and do mostly proc sql stuff. and then case stuff. data have; informat dos disch date8.; format dos disch date8.; input id plc $ dos disch ; cards; 111 hospital 15jun2010 18jun2010 111 hospital 11dec2010 15dec2010 111 hospice 15dec2010 17dec2010 111 rehab 15jan2011 31jan2011 111 hospital 12nov2011 15nov2011 222 hospital 01jan2010 10jan2010 333 hospital 01feb2011 07feb2011 444 hospital 01jun2010 07jun2010 444 rehab 07jun2010 15jun2010 444 nursing 15jun2010 17jun2010 444 hospital 17aug2011 30aug2011 555 rehab 15aug2011 29aug2011 666 hospital 01jan2010 02jan2010 777 skilled 06aug2011 15aug2011 888 hospice 10aug2011 30aug2011 999 hospital 01jan2010 15jan2010 999 hospital 20feb2010 25feb2010 999 rehab 27feb2010 28feb2010 999 skilled 03mar2010 07mar2010 ; run;

tmm · ‎07-16-2012

Sorry. The problem is this is not my table. It is a table on our claims system. I have to go thru each row of over 125K records to find the varying scenarios I am running across and the problem now is that now that I am having to look at each row in this huge table I might as well click edit in SAS and manually put in skillled nursing or rehab. If I am going thru each record I might as well do that anyway. I was trying to avoid that by coding this but it seems I cannot because I have to view each ID to get all the scenarios logged to get help on the SAS forum. The PM's have no criteria other than they want hospital separate from others. The problem is when summing it all up the numbers do not match and I told my boss and she is like well you will have to figure out what hospital goes with rehab, skilled nursing, hospice, etc. You will have to review the dates of the records for each member ID and I am like there has to be an easier way than this. This is just plain ridiculous..

tmm · ‎07-16-2012

I found this scenario in the table and searching for more. 111 hospital 11dec2010 15dec2010 111 hospital 15jun2010 18jun2010 111 hospice 15dec2010 17dec2010 111 rehab 15jan2011 31jan2011 111 hospital 12nov2011 15nov2011 222 hospital 01jan2010 10jan2010 333 hospital 01feb2011 07feb2011 444 hospital 01jun2010 07jun2010 444 rehab 07jun2010 15jun2010 444 nursing 15jun2010 17jun2010 444 hospital 17aug2011 30aug2011 555 rehab 15aug2011 29aug2011 ;

tmm · ‎07-16-2012

I think I did something wrong. I have this: data have; informat dos disch date8.; format dos disch date8.; input id plc $ dos disch ; cards; 111 hospital 11dec2010 15dec2010 111 hospital 15jun2010 18jun2010 111 hospice 15dec2010 17dec2010 111 rehab 15jan2011 31jan2011 111 hospital 12nov2011 15nov2011 222 hospital 01jan2010 10jan2010 333 hospital 01feb2011 07feb2011 444 hospital 01jun2010 07jun2010 444 rehab 07jun2010 15jun2010 444 nursing 15jun2010 17jun2010 444 hospital 17aug2011 30aug2011 ; run; *Isolate into episodes; data episodes; set have; by id; retain episode; *keep track of previous discharge date; format prev_disch date8.; prev_disch=lag(disch); *Calculate the time from previous stay, if this is not a new id; if not first.id then time_from_previous_stay=dos-prev_disch; *If first id then set episode to 1, else if dos ne disch then increase by 1; if first.id then episode=1; else if time_from_previous_stay between >=0 and <=30 then episode+1; *Create id to group episode for the query later on; new_id=put(id, z3.)||put(episode, z2.); run; *Only Hospitals; proc sql; create table hospital as select * from episodes group by new_id having min(plc)='hospital' and max(plc)='hospital'; quit; *All the others; proc sql; create table others as select * from episodes group by new_id having min(plc)ne 'hospital' or max(plc)ne 'hospital'; quit; I get this back on each table: 4 back in the hospital and 7 in the other but there should be 5 based on the 15jun2010 I put in. I am finding more and more oddities in this table of 125k. It is not strictly first service is hospital and then other things follow. I have some where the first service is nursing or rehab and they never even go to the hospital. It is just getting more and more complex the more I dig into this table.

tmm · ‎07-16-2012

I will try the above scenarios when my other SAS query is done which is actually extracting some huge data. Will let you know how this works out. Thanks.

tmm · ‎07-16-2012

Not sure. The table is auto constructed to have what it has. Not my table it is on a server. It has 125k records and unless I go thru line by line I really do not know all the scenarios. I am up to row 1015 and am writing down all the scenarios as I see them where it is not a 100% hospital. I have to log all of them because I explained to my manager that what I have seen so far is not a simple case of what I described above. I hate projects like these. I am thinking instead of manually going thru all these 125k records that maybe I can create a query that says give me distinct ID and their admit disch date without place of service so I can see all the possible scenarios. I alerted my boss to the scenarios so far and she seems to think if we isolate all 100% hospital and then the others are within a 30day that should get us all that we want. Again, did I say I hate projects like these? Nothing in any of our claims systems is ever cut and dry. There are always a set of scenarios but then you find oops, now I have 12 monkey wrenches thrown into it all.

tmm · ‎07-16-2012

Ok. Now if there is a situation of the below which I just found in fishing thru the 125k plus records 111 hospital 11dec2010 15dec2010 111 hospice 15dec2010 17dec2010 111 rehab 20dec2010 30dec2010 changed this date 111 hospital 12nov2011 15nov2011 222 hospital 01jan2010 10jan2010 333 hospital 01feb2011 07feb2011 444 hospital 01jun2010 07jun2010 444 rehab 07jun2010 15jun2010 444 nursing 19jun2010 25jun2010 changed this date 444 hospital 17aug2011 30aug2011 would I put: *Isolate into episodes; data episodes; set have; by id; retain episode; *keep track of previous discharge date; format prev_disch date9.; prev_disch=lag(disch); *If first id then set episode to 1, else if dos ne disch then increase by 1; if first.id then episode=1; else if dos ne prev_disch then episode+30; a +30 on this??? *Create id to group episodese for the query later on; new_id=put(id, z3.)||put(episode, z2.); run;

Online Status	Offline
Date Last Visited	‎09-01-2015 07:11 AM

Place quotes around column header and data

Re: Point = error

Point = error

Re: Insert into confusion

Re: Insert into confusion

Insert into confusion

Re: create a proc sql without the need for multiple proc sqls

create a proc sql without the need for multiple proc sqls

Re: how to combine 2 inner joins to proc sql

how to combine 2 inner joins to proc sql

format max date

concatenating number columns

macro pop-up

Data Point Error

Re: Update Query

Update Query

trying to explain correctly

Re: Find Records

Re: Find Records

Re: Find Records

Re: Find Records

Re: Find Records

Re: Find Records

Re: Find Records

Re: Find Records

Re: Find Records

Re: Find Records