About devsas

devsas · ‎10-09-2016

Thanks. But, I'm still strugging with my original problem, although i made quite a progress getting to the point where i have 4 variables, id, start_dt, end_dt and total months of membership. I have to now combine total membership months for each id but subtracting by 1 those cases where the first end_dt of a member was the same as start_dt of next membership period (to avoid counting that month twice). So, what im left with is first extracting mmYYYY from start_dt and end_dt and then using combination of retain statement and first. function perhaps to get what i want. Here is what my code looks so far. ata hh_2014_2; set hh_ ; if end_dt >= '01jul2013'd and start_dt < '01aug2013'd then july_2013 = 1; if end_dt >= '01aug2013'd and start_dt < '01sep2013'd then aug_2013 = 1; if end_dt >= '01sep2013'd and start_dt <'01oct2013'd then sep_2013 = 1; if end_dt >= '01oct2013'd and start_dt < '01nov2013'd then oct_2013 = 1; if end_dt >= '01nov2013'd and start_dt <'01dec2013'd then nov_2013 = 1; if end_dt >= '01dec2013'd and start_dt <'01jan2014'd then dec_2013 = 1; if end_dt >= '01jan2014'd and start_dt < '01feb2014'd then jan_2014 = 1; if end_dt >= '01feb2014'd and start_dt <'01mar2014'd then feb_2014 = 1; if end_dt >= '01mar2014'd and start_dt < '01apr2014'd then mar_2014 = 1; if end_dt >= '01apr2014'd and start_dt < '01may2014'd then apr_2014 = 1; if end_dt >= '01may2014'd and start_dt < '01jun2014'd then may_2014 = 1; if end_dt >= '01jun2014'd and start_dt < '01jul2014'd then jun_2014 = 1; run; proc sort data = hh_2014_2; by medicaid_id start_dt; run; data hh_2014_3; set hh_2014_2 ; total_months = sum(july_2013, aug_2013, sep_2013, oct_2013, nov_2013, dec_2013, jan_2014, feb_2014, mar_2014, apr_2014, may_2014,jun_2014); run; proc sql; create table hh_2014_4 as select medicaid_id, start_dt, end_dt, total_months from hh_2014_3 where total_months > 0; quit; run;

devsas · ‎10-07-2016

This is the code I run and below is the log. But, again, as I said earlier, the date formats u guys suggested earlier did work for my real data. Also, can you help in finding the id's with time conditions mentioned in my first post? Thanks so much! data have1; infile cards; input id start_dt mmddyy10. end_dt mmddyy10. ; cards; 1 01/01/2013 01/03/2014 2 08/01/2013 01/01/2015 2 03/01/2015 04/03/2015 3 12/12/2014 04/01/2016 ; run; proc sql; create table a as select* from have1 where start_dt between '01jul2013'd and '30jun2015'd; quit; run; data have1; 147 infile cards; 148 input id start_dt mmddyy10. end_dt mmddyy10. ; 149 cards; NOTE: Invalid data for end_dt in line 150 13-22. RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+-- 150 1 01/01/2013 01/03/2014 id=1 start_dt=21915 end_dt=. _ERROR_=1 _N_=1 NOTE: Invalid data for end_dt in line 151 13-22. 151 2 08/01/2013 01/01/2015 id=2 start_dt=22128 end_dt=. _ERROR_=1 _N_=2 NOTE: Invalid data for end_dt in line 152 13-22. 152 2 03/01/2015 04/03/2015 id=2 start_dt=21975 end_dt=. _ERROR_=1 _N_=3 NOTE: Invalid data for end_dt in line 153 13-22. 153 3 12/12/2014 04/01/2016 id=3 start_dt=22261 end_dt=. _ERROR_=1 _N_=4 NOTE: The data set WORK.HAVE1 has 4 observations and 3 variables. NOTE: DATA statement used (Total process time): real time 0.06 seconds cpu time 0.04 seconds 154 ; 155 run; 156 proc sql; 157 create table a as select* from have1 where start_dt between '01jul2013'd and '30jun2015'd; NOTE: Table WORK.A created, with 0 rows and 3 columns. 158 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.04 seconds cpu time 0.03 seconds 159 run;

devsas · ‎10-07-2016

EDIT: Actually it worked on my original file but dont work on the test data i provided to you guys. Not sure why. Thanks! But it still gives me 0 rows. Also, this part is just the first step, im not sure how to correctly solve the main problem as described above.

devsas · ‎10-07-2016

Hi All, I have the data in following format data have; infile cards; input id start_dt mmddyy10. end_dt mmddyy10.; cards; 1 01/01/2013 01/03/2014 2 08/01/2013 01/01/2015 2 03/01/2015 04/03/2015 3 12/12/2014 04/01/2016 ; run; Start_dt is always more than 01/01/2013. My dates in original dataset are stored and displayed in MMDDYY10. format (numeric). Here is what I need-it seemed easy at first but i got stuck in first step itself! I need to get all those id's which were members for atleast 9 months during both fiscal periods. Ist fiscal period is between july 2013 and June 2014 and second fiscal period is between july2014 and june 2015. Even if a member was member for 1 day in a month, it counts as 1 month. Many members have more than one record where he/she could end the membership and then restart at later date. Besides, in some rare cases, the end month date of first time membership coincides with start month date of second time membership (for ex, a member could have end_dt on 04/13/2014 and then could restart membership with start_dt on 04/20/2014). This is important to keep in mind so that months of membership are not over estimated. My first instinct was to extract two tables with each table satisfying 2014 or 2015 fiscal period membership requirement and then join the tables with id to get members who were present in both tables. Now the first problem i'm having is that i could not get members satisfying the start_dt requirement . When I put the condition below, its returning 0 rows but not an error. My guess is this has to do with date format or something. proc sql; create table test from source where start_dt between 07/01/2013 and 06/31/2015; quit; run; In any case, i believe to accurately extract the informaton im looking for needs more than simple joins and where conditions. Thanks in advance for your help.

devsas · ‎10-06-2016

Amazing! Astounding can you please explain the the logic behind your first step-especially do i=1 by 1, i have never seen something like this before. Thanks. @Astounding wrote: It will take two steps, not a single DATA step. Here's one way: data b; set a; rownum = _n_; length parse $ 5; do i=1 by 1 until (parse=' '); parse = scan(ColumnName, i, ','); if parse > ' ' then output; end; keep parse rownum; run;

devsas · ‎09-28-2016

Thanks so much! I knew of CATX function but was not sure how will it take care of blanks. But it seems your code took care of that.

devsas · ‎09-28-2016

Ok, here is the data you can read in sas. I already mentioned the desired output as above. Thanks so much! data WORK.have; infile datalines dsd truncover; input Procedure_Code:$5. section_3:$1. section_5:$1. section_7:$1. section_12:$2. section_13:$1. section_15:$1. section_17:$2. section_18:$2. section_19:$2. section_20:$2. section_21:$2. section_22:$2. section_23:$1. section_25:$1. section_26:$2. section_2 8:$2. section_29:$2. section_30:$2. section_31:$2. section_32:$2. section_40:$2. section_65:$2. section_68:$1. section_85:$1. section_96:$2. section_97:$1. section_102:$1. section_103:$3.; datalines4; A9279,,,,,,,,18,19,20,21,,,,,,29,,,,,,,,,,, G0108,3,,,,,,,,,,,,,,,,,,31,,,,,,,,,103 G0109,3,,,,,,,,,,,,,,,,,,31,,,,,,,,,103 G0151,,,,,,,,,19,,,,,,,,,,,,40,,,,,,, G0152,,,,,,,,,19,,,,,,,,,,,,40,,,,,,, ;;;;

devsas · ‎09-28-2016

Hi all, I'm attaching an example extract file. Basically all I have is procedure codes which have a certain section or not as shown by section number variables. If a section is present for that procedure code, there is a number similar to section number variable below the variable, otherwise its blank. For instance, for the first procedure code, A9279, I have section 18, 19, 20, 21 and 29 and other sections are blank. Similarly, for procedure code G0108, sections present are 3,31 and 103 and so on. What i'm looking is following output- procedure_code section_numbers A9279 18,19,20,21,29 G0108 3,31,103 G0109 3,31,103 G0151 19, 40 Thanks for your help!

devsas · ‎09-15-2016

Haikyo, This was very helpful from my learning perspective. Thanks. I'm little unsure about array a_ a_:; array mob_ mob_:; am=a_*mob_(_i_+tob-1); What does these 3 line codes mean and during what kind of situations u use this?

devsas · ‎09-12-2016

lol..you are a rockstar! Thanks so much! It worked, and sorry for being casual in my approach (still learning :)) I just tested it, but have to spend some time learning what exactly it is doing. I will have more questions.

devsas · ‎09-09-2016

Sorry Scott for confusing you! Ok, let me try to rephrase and make it simple for demonstration purpose. Lets say I have written four different codes using macro variables and I want to write one single macro code which recalls each one of those codes. So rather than me running each code seperately, I run one code by changing variable names (file names and date only) and rest all is taken care of. Lets say the first code uses input file A, second code uses input file B and so on. Macro should do If file =A then run code 1, if file =B, then run code B and so on. Hope this is clearer. I ran your code to test on my SAS, but it gave an error for loop. Thanks again!

devsas · ‎09-09-2016

Thanks everyone for reading! So, I have written a SAS code using Macro variables (%let statement for file location, date, output location) where the code reads input csv files using infile statement, uses first. and other manipulation before joining datasets and producing three different files which are exported using the proc export three times. There are four such codes used which are similar in nature except that the input files are different and manipulation varies a bit. I'm hoping to write a macro which encompasses all these 4 seperate coded into one. Meaning depending upon the input file, macro could direct SAS to run the relevant code. Below is the code(s) i have written. The other codes are similar except that files have different/fewer variables and sometimes 2 and not 3 final output files are created. %LET outroot = C:\SAS\input_files\Readmission_MaineCare2015q2.csv; %LET MUSKIE = C:\SAS\input_files\BHP_PANEL_VHP.csv ; %LET MMYY = JULY16 ; /* reading Readmit csv file into SAS */ DATA devsas.ATT_READMIT&MMYY; INFILE "&outroot" delimiter = ',' MISSOVER DSD FIRSTOBS=2 lrecl=1000; INFORMAT ORID $20.; INFORMAT Facility_Event_Number $20.; INFORMAT PROVIDER $20.; INFORMAT ebm_name $200.; INFORMAT CASE_ID $20.; INFORMAT RULE_ID $10.; INFORMAT Case_name $10.; INFORMAT result_flag $10. ; INFORMAT task_name $10.; INFORMAT admit_date mmddyy10.; INFORMAT discharge_date mmddyy10.; INPUT ORID $ admit_date Facility_Event_Number $ PROVIDER $ discharge_date ebm_name $ CASE_ID $ RULE_ID $ Case_name $ result_flag $ task_name $; FORMAT ADMIT_DATE MMDDYY10.; FORMAT DISCHARGE_DATE MMDDYY10.; RUN; /* reading another input file */ Data devsas.BHP_PANEL_VHP_&MMYY; INFILE "&MUSKIE" delimiter = ',' MISSOVER DSD FIRSTOBS=2 lrecl=5000; INFORMAT medicaid_Id $20.; INFORMAT pay_to_NPI $20. ; INFORMAT pay_to_name $100. ; INFORMAT srvLoc_NPI $25.; INFORMAT srvLoc_Name $100.; INFORMAT prov_typ_Cd $20. ; informat stage $10. ; INFORMAT start_Dt mmddyy10.; INFORMAT end_Dt mmddyy10.; Informat term_days BEST32. ; INFORMAT term_months BEST32.; INFORMAT new_end_dt mmddyy10.; INFORMAT Mth_Start_Dt mmddyy10.; INFORMAT Mth_End_Dt mmddyy10.; INFORMAT Mth_Member_Month BEST32.; INFORMAT Qtr_Start_DT mmddyy10.; INFORMAT Qtr_End_DT mmddyy10.; INFORMAT ttlQ1_2014 BEST32.; INFORMAT ttlQ2_2014 BEST32.; INFORMAT ttlQ3_2014 BEST32.; INFORMAT ttlQ4_2014 BEST32.; INFORMAT Q1_2014_2Day BEST32.; INFORMAT Q2_2014_2Day BEST32.; INFORMAT Q3_2014_2Day BEST32.; INFORMAT Q4_2014_2Day BEST32.; INFORMAT Q1_2014 BEST32.; INFORMAT Q2_2014 BEST32.; INFORMAT Q3_2014 BEST32.; INFORMAT Q4_2014 BEST32.; INFORMAT Q1_2015 BEST32.; INFORMAT Q2_2015 BEST32.; INFORMAT Q3_2015 BEST32.; INFORMAT Q4_2015 BEST32.; INFORMAT Q1_2016 BEST32.; INFORMAT Q2_2016 BEST32.; INFORMAT Q3_2016 BEST32.; INFORMAT Q4_2016 BEST32.; INFORMAT perMonth BEST32. ; INPUT medicaid_Id $ pay_to_NPI $ pay_to_name $ srvLoc_NPI $ srvLoc_Name $ prov_typ_Cd $ stage $ start_Dt end_Dt term_days term_months new_end_dt Mth_Start_Dt Mth_End_Dt Mth_Member_Month Qtr_Start_DT Qtr_End_DT ttlQ1_2014 ttlQ2_2014 ttlQ3_2014 ttlQ4_2014 Q1_2014_2Day Q2_2014_2Day Q3_2014_2Day Q4_2014_2Day Q1_2014 Q2_2014 Q3_2014 Q4_2014 Q1_2015 Q2_2015 Q3_2015 Q4_2015 Q1_2016 Q2_2016 Q3_2016 Q4_2016 perMonth; FORMAT start_Dt mmddyy10.; FORMAT end_Dt mmddyy10.; FORMAT new_end_dt mmddyy10.; FORMAT Mth_Start_Dt mmddyy10.; FORMAT Mth_End_Dt mmddyy10.; FORMAT Qtr_Start_DT mmddyy10.; FORMAT Qtr_End_DT mmddyy10.; RUN; /* creating output for those patients who were members during this period */ Proc SQL; Create table devsas.ATT_readmit_dateMatch_&MMYY as select a.ORID , a.admit_date , a.Facility_Event_Number , a.PROVIDER , a.discharge_date ,a.ebm_name , a.CASE_ID , a.RULE_ID , a.Case_name , a.result_flag , a.task_name , b.pay_to_NPI , b.pay_to_name , b.srvLoc_NPI , b.srvLoc_Name , b.prov_typ_Cd , b.stage , b.start_Dt , b.new_end_dt from devsas.READMIT_july a left join devsas.BHP_PANEL_VHP b on a.ORID = b.medicaid_Id where a.admit_date >= b.start_Dt and a.admit_date <= b.new_end_Dt and b.stage not in ('CCT') order by a.ORID, a.admit_date ; run; /* creating output for those members which have longest term days */ proc sort data =devsas.BHP_PANEL_VHP_&MMYY out = devsas.BHP_PANEL_VHP_&MMYY; by medicaid_Id descending term_days ; run; data devsas.BHP_PANEL_VHP__&MMYY; set devsas.BHP_PANEL_VHP_&MMYY; by medicaid_id ; if first.medicaid_id; run; proc sql; create table devsas.ATT_readmit_longestDays_&MMYY as select a.ORID , a.admit_date , a.Facility_Event_Number , a.PROVIDER , a.discharge_date ,a.ebm_name , a.CASE_ID , a.RULE_ID , a.Case_name , a.result_flag , a.task_name , b.pay_to_NPI , b.pay_to_name , b.srvLoc_NPI , b.srvLoc_Name , b.prov_typ_Cd , b.stage , b.start_Dt , b.new_end_dt from devsas.ATT_READMIT&MMYY a left join devsas.BHP_PANEL_VHP__&MMYY b on a.ORID = b.medicaid_Id order by a.ORID, a.admit_date ; run; /* creating output for those members which ARE MOST RECENT */ proc sort data =devsas.BHP_PANEL_VHP_&MMYY out = devsas.BHP_PANEL_VHP_recent_&MMYY; by medicaid_Id descending new_end_dt ; run; data devsas.BHP_PANEL_VHP_recent_&MMYY; set devsas.BHP_PANEL_VHP_recent_&MMYY; by medicaid_id ; if first.medicaid_id; run; proc sql; create table devsas.ATT_readmit_mostRecent_&MMYY as select a.ORID , a.admit_date , a.Facility_Event_Number , a.PROVIDER , a.discharge_date ,a.ebm_name , a.CASE_ID , a.RULE_ID , a.Case_name , a.result_flag , a.task_name , b.pay_to_NPI , b.pay_to_name , b.srvLoc_NPI , b.srvLoc_Name , b.prov_typ_Cd , b.stage , b.start_Dt , b.new_end_dt from devsas.ATT_READMIT&MMYY a left join devsas.BHP_PANEL_VHP_recent_&MMYY b on a.ORID = b.medicaid_Id order by a.ORID, a.admit_date ; run; /* creating output files directly to folder */ proc export data=devsas.ATT_readmit_mostRecent_&MMYY dbms=xlsx outfile="C:\SAS\output_files\reAdmit_mostRecent&MMYY" replace; run; proc export data=devsas.ATT_readmit_longestDays_&MMYY dbms=xlsx outfile="C:\SAS\output_files\reAdmit_longestDays&MMYY" replace; run; proc export data=devsas.ATT_readmit_dateMatch_&MMYY dbms=xlsx outfile="C:\SAS\output_files\reAdmit_dateMatch&MMYY" replace; run;

devsas · ‎04-17-2015

That's fair Haikuo. Thanks so much for explanation. You are right, I should try to figure out myself more as it will help.

devsas · ‎04-17-2015

Thank you again, Hai! I didnt know much about Perl before this discussion, but iam glad you answered it. Because now i see that its such an efficient way to solve these kind of issues. I'm trying to learn after your post, but still struggling with some concepts. When you have little time, can you please break down in words the expression you used to flag those cases and also different symbols you used and their significance? Also, I have similar problems and was wondering if you can help in those too. Perhaps that will help me eventually getting the gist of these concepts. I looked at the cheat sheet of SAS/perl but still not fully getting it. Here are the other conditions. If you can break down these conditions individually that would be perfect. I mean flag1, flag2 etc Admit Date – The beginning date of the service must only be in the format m/d/yyyy. Must not be a future date. The admit date must be prior to or equal to the discharge date. Dx01 – Must not be missing/Null and be between 3-5 characters (all digits or beginning with one letter). NPI – The NPI of the rendering or billing provider. Must not be missing/Null and be 10 digits. Bill Type – POSSIBLE Values ARE ‘P’ for professional, ‘I’ for inpatient or ‘O’ for outpatient. This field must not be missing/Null. Risk Assessment Code – May be missing/Null for 2013 dates of service. If a value is present for 2013 dates of service, it should be value ‘A’. It is required (must not be missing/Null) for 2014 dates of service onward. Only possible values are A, B or C. Thanks so much!

devsas · ‎04-16-2015

Thank you sir, yes it works now. My bad, I was spelling variable incorrectly. I see that you have used prxmatch function here, im not sure if i understand it correctly. Can you point towards any source where I can learn this?

Online Status	Offline
Date Last Visited	‎10-04-2019 10:53 PM

Re: Flagging records based on a criteria

Re: Flagging records based on a criteria

Re: Flagging records based on a criteria

Re: Flagging records based on a criteria

Re: Flagging records based on a criteria

Flagging records based on a criteria

How to pick records for ID's which dont have certain codes

Re: Extracting all records for only two unique variable values

Extracting all records for only two unique variable values

Re: How to filter for only one record per provider number

Re: How to pick records for ID's which dont have certain codes

Re: How to pick records for ID's which dont have certain codes

Re: How to filter for only one record per provider number

Re: How to filter for only one record per provider number

Re: Displaying data range in proc sql summary

How to pick records for ID's which dont have certain codes

Re: How to filter for only one record per provider number

Re: Summary of flags

Calculating driving distances based on addresses

Re: counting distinct values over a range of variables

Re: Extracting id's satisfying time period conditions

Re: Extracting id's satisfying time period conditions

Re: Extracting id's satisfying time period conditions

Extracting id's satisfying time period conditions

Re: Parsing Comma Separated Values

Re: Using arrays?

Re: Using arrays?

Using arrays?

Re: Array help

Re: Macro for inputting and outputting different files

Re: Macro for inputting and outputting different files

Macro for inputting and outputting different files

Re: Checking several conditions at once

Re: Checking several conditions at once

Re: Checking several conditions at once