About devsas

devsas · ‎07-30-2019

Just in case anyone is interested- i found the solution and it tests fine. data want5; set have; by BL_BCBS_PROD_CD bl_pln_prvdr_num tc_code tc_desc; if first.tc_code and last.tc_code then dont_disp_ind=dont_disp_ind; else if LAST.tc_code and entity_type = 'TYPE=2' then dont_disp_ind='Y' ; run;

devsas · ‎07-30-2019

The following code gets it halfway- meaning it does change to Y for those cases where there are both type=1 and type=2, but it also changes DND to Y when there is only type=2 only and I dont want it to change. data want2; set have; by BL_BCBS_PROD_CD bl_pln_prvdr_num tc_code tc_desc; if LAST.tc_code and entity_type = 'TYPE=2' then dont_disp_ind='Y' ; else dont_disp_ind=dont_disp_ind; run; I tried another code and though I dont any issue with it, it still doesnt solve the problem. Infact it doesnt do even what above code did. Not sure what im missing here. data want4; set have; by BL_BCBS_PROD_CD bl_pln_prvdr_num tc_code tc_desc; IF FIRST.TC_CODE THEN N=0;N= N+1; IF N=2 AND ENTITY_TYPE = 'TYPE=2' then dont_disp_ind='Y'; else dont_disp_ind=dont_disp_ind; run;

devsas · ‎07-29-2019

Thanks for the reply. Oh sorry about the mistake in my code, generally I test it before pasting here, but something slipped away this time. I cannot test the code at this point, but not sure if this will work in case entity type has different value than either of the ones i mentioned. For instance, if two records have same product type, provider and tc code but entity values are type=1 and type =3, I dont want the DND to change in that case. In other words, only when there are two records for same, product, provider and TC code and entity types are type =1 and type =2, i want to display record with type=2 as Y for DND. Rest for everything else, DND remains the same as original. Also, I hope the code wont change if there are additional variables, such as counts, avg_cost etc?

devsas · ‎07-29-2019

Actually DND needs to be changed to 'Y' only for those cases where for the same product type, provider and tc, there are both type 1 and type 2 entity types and type 2 should be changed to Y. Rest everything else remains the same.

devsas · ‎07-29-2019

Thanks. For this particular situation, whenever there is both type=1 and type =2, we should prefer type =1.

devsas · ‎07-29-2019

Good Afternoon everyone. Thanks in advance. So, I have a dataset with many variables, including one for DND(do not display) flag. This dataset is created after running a macro which was not originally created by me. I have to modify this macro to include another rule so that when that happens, DND is 'Y' rather than current 'N'. The rule is that when for a certain product type, there is a provider and a specific treatment category and entity type, and the same provider within the same product type has the same treatment category but a different entity type, a specific entity type should be preferred and the other record should have DND as 'Y'. The following data should clear this up. For simplicity, I have only included variables which are of interest, actual dataset has 15-16 other variables too. data have; input product $ provider_no $ TC $ ENTITY_TYPE $ DND $; DATALINES; MAB1 ABC001 MRI TYPE=1 N MAB1 ABC001 MRI TYPE =2 N MAB1 BCD002 FLU TYPE=1 N MAB1 BCD002 TEST TYPE=2 N ; RUN; DATA WANT; input product $ provider_no $ TC $ ENTITY_TYPE $ DND $; DATALINES; MAB1 ABC001 MRI TYPE=1 N MAB1 ABC001 MRI TYPE =2 Y MAB1 BCD002 FLU TYPE=1 N MAB1 BCD002 TEST TYPE=2 N ; RUN; Thanks so much.

devsas · ‎06-25-2019

Thanks for reading. So, I have a dataset as below- data test; input id $ claim_id $ rev_cd $; datalines; a a1 801 a a1 802 a a2 801 a a3 821 b b1 901 b b1 802 c c2 821 ; run; I want to pick those Id's which do not have certain revenue code - for ex, 802- in any of its claims. Using Proc SQL as below it still picks a and b whereas I only want c. proc sql; select distinct id from test where rev_cd not in ( '802'); quit; One way I can think of is having a table of records with id's having rev_cd as 802 and another table with id's not having rev_cd as 802. That way I can do a left join to extract C. But Im sure there is better way to solve this problem, but cant seem to think of it.

devsas · ‎06-20-2019

Thanks , but for this I need to open each dataset every time and find two unique members. Infact i used the same method, but knew it was not very efficient.

devsas · ‎06-20-2019

Good afternoon everyone! I have a dataset where there are multiple records for each member id along with claim numbers and other fields. I want to extract just a sample for two members with all their corresponding multiple records. Data have; input mem_id $ claim_id $ amount dos :mmddyy10.; format dos mmddyy10.; datalines; A 1 100 01/01/2017 A 1 200 01/01/2017 A 2 150 01/02/2017 B 3 50 01/01/2018 B 4 70 01/02/2018 C 5 10 01/01/2019 C 5 15 01/01/2019 ; Data want; input mem_id $ claim_id $ amount dos :mmddyy10.; format dos mmddyy10.; datalines; A 1 100 01/01/2017 A 1 200 01/01/2017 A 2 150 01/02/2017 B 3 50 01/01/2018 B 4 70 01/02/2018 ; Thanks!

devsas · ‎06-11-2019

@heffo @ballardw thanks to both of you. Both solutions work.

devsas · ‎06-10-2019

Good evening everyone. I have a result table I created after some joins and analysis. My table has provider number, treatment code, facility name. For the most records, each provider number has one facility name attributed to it, but in few cases there are multiple facility names for unique combination of provider number and treatment code. I want to identify those and just keep the first occurring facility name and remove the second record. Have: prov_num tc_code fac_nm 01 a abc 01 a abc_1 01 b abc_1 03 c def 03 c def_1 Want: prov_num tc_code fac_nm 01 a abc 01 b abc_1 03 c def Thanks in advance!

devsas · ‎06-22-2018

Thanks again, man. Actually I realized that the main problem is that one of the fields on which im joining has many empty values. So all those records which I should not be getting are there whenever that field (procedure_code) is blank. So, for instance, if two claims match on member id, dos, prov id and procedure code, it will give corresponding claim number, claim line etc etc for those records. So, it will match line by line for such cases. So far so good. But when the procedure code is blank and lets say claim no abcd with claim lines 1,2,34 match with claim no efgh with claim lines 5,6,7,8 on other three parameters, the result will give 16 records rather than just 4 matching records-like its doing a cross join with every claim line matching with other 4 claim lines from duplicate claim. Those 12 records i dont need and i cannot do distinct because technically they are not distinct. I hope i was able to explain. My SAS is not working and so im using my proc sql skills to solve this in sql server, hence i was requesting a solution in sql. I tried doing various other tricks but still not getting the result. Thanks again for your help.

devsas · ‎06-21-2018

Thanks Patrick. But i dont have a unique identifier, plus i need to do this in proc sql. Btw, i dont think in my query claim is joining with itself.

devsas · ‎06-21-2018

Good Afternoon everyone! So, I have this claims table at line level where I need to find all those records with different claim id's where date of service, member id, provider id and line level procedure code are same so that we can identify duplicate claim id's. I'm doing a simple self join but getting too many extra records where each claim line is joining itself with all claim lines of other claim id where all those 4 parameters match- something like a cross join. I have banged my head so much but still cant find a solution to this seemingly regular problem. Some of the line level procedure code field values are blank, not sure if that is causing the problem. Here is my code in proc sql- Proc sql; SELECT DISTINCT c1.clm_nbr, c1.mbr_nbr, c1.CLM_LN_PROC_CD, c1.CLM_DT_OF_SRVC, c1.clm_rcvd_dt, c1.clm_ln_nbr, c1.clm_ln_stat_cd , c1.RVNU_CD, c1.ln_item_sbmtd_chrg_amt , c1.pd_amt, c2.clm_nbr as clm_nbr_, c2.clm_rcvd_dt as clm_rcvd_dt_, c2.clm_ln_nbr as clm_ln_nbr_, c2.clm_ln_stat_cd as clm_ln_stat_cd_, c2.rvnu_cd as rvnu_cd_, c2.ln_item_sbmtd_chrg_amt as ln_item_sbmtd_chrg_amt_, c2.pd_amt as pd_amt_ into final_ FROM NW_ALL_3 AS c1, NW_ALL_3 AS c2 WHERE c1.MBR_NBR = c2.MBR_NBR and left(c1.CLM_LN_PROC_CD, 5) = left(c2.CLM_LN_PROC_CD,5) and c1.CLM_DT_OF_SRVC = c2.CLM_DT_OF_SRVC and c1.SVCG_PRACTNR_ID = c2.SVCG_PRACTNR_ID AND left(c1.Clm_nbr, 10) < left(c2.CLM_NBR, 10) ; quit;

devsas · ‎04-29-2018

Ok, so I modified your code and almost got what I wanted. Except that i cannot have extra rows for blank periods. Can anybody help in that. Below is the code I used. data expand; set sa.sample1_step1; do date = stdt to enddt; output; end; format date yymmdd10.; keep ptid date drug; run; proc sql; create table sumDoses as select ptid, date, drug from expand group by ptid, date order by ptid, date; quit; proc sort data = sumdoses; by ptid date drug; run; proc transpose data = sumdoses out = trans; by ptid date; var drug; run; data trans1; set trans; drug = catx('+', col1, col2, col3); keep ptid date drug ; run; data want; set trans1; by ptid drug notsorted; retain date_start; if first.drug then date_start = date; if last.drug then do; date_stop = date; output; end; format date_start date_stop yymmdd10.; keep ptid drug date_start date_stop; run;

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: 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

How to filter for only one record per provider number

Re: Duplicate claims-Self join on 4 fields but still getting lot many ...

Re: Duplicate claims-Self join on 4 fields but still getting lot many ...

Duplicate claims-Self join on 4 fields but still getting lot many reco...

Re: Treatment episode output