I've attached the source and target and below are the rules to derive the target,   1) Based on ID order the DIAG Codes by line number  2) Populate only Distinct DI codes in above order. 3) Assign the LI_SEQ based on the order of distinct Di Codes.   I don't want to use proc transpose and I need help in writing SQL or it would nice if you point to the document which can help here.   Ignore the data inside table as I shown only few data in source and target to help understand the logic   Source Table:   Source Target: Target ... View more

Need help to tackle this update statement? It's not working now due to unexcepted Select statement error in the SET statement.   UPDATE XF.POS_PROC_COD_TEST PCI SET PCI.PROC_CD_DESC = FROM ( SELECT PDC.id, max( CASE WHEN ( COALESCE(SERVICE_FROM_DT, TO_DATE('20990101', 'YYYYMMDD')) ) BETWEEN EFFECTIVE_DATE AND (CASE WHEN EXPIRATION_DATE IS NULL THEN '2099-01-01' ELSE EXPIRATION_DATE END) THEN DESCRIPTION END) PROC_CD_DESC_NEW FROM XF.POS_PROC_COD_TEST PDC LEFT JOIN XF.POS_CLAIM_HEDR TPCC ON PDC.O_CLAIM_ID = TPCC.O_CLAIM_ID LEFT JOIN ( SELECT DISTINCT ICD_PROC_CODE, EXPIRATION_DATE,EFFECTIVE_DATE, DESCRIPTION FROM COM_DB.COM_DATA.MR_ICD_PROC) CDMRIP ON CDMRIP.ICD_PROC_CODE = PDC.PROC_CD GROUP BY PDC.id ) Q1 WHERE PCI.id = Q1.id and PCI.id = 0411; ... View more

Any help to resolve this issue? Getting an error like unexpected 'on' at line number 4     update pre_claim_lin_1_test pci set pci.LINE_DIA_PRIM_DESC = diag.description from (select max(description) description from COMDB.ON_DATA.MR_DIAGNOSIS_CODE c inner join raw.prepa_claim_inst r on r.PRINCIPAL_DIAG = c.DIAGNOSI_CODE where c.ICD1_INDICATOR<>'N')diag on 1=1 and pci.id =7828 ;   ​If I tried to remove ON and add WHERE, I'm getting an error like invalid  field diag.description  update pre_claim_lin_1_test pci set pci.LINE_DIA_PRIM_DESC = diag.description from (select max(description) description from COMDB.ON_DATA.MR_DIAGNOSIS_CODE c inner join raw.prepa_claim_inst r on r.PRINCIPAL_DIAG = c.DIAGNOSI_CODE where c.ICD1_INDICATOR<>'N')diag where pci.id =7828 ;   ... View more

I know this is not a direct SAS question but still if someone can help it would be nice.  I'm connecting to Snowflake with explicit passthrough.   Any idea how to write the query like below without introducing any new fields, using exclude keyword using CTE using all the fields in group by  This sql is not working due to 'unexcepted over'. I want to select the max of ntwk_cd group or partition by the field PBK   SELECT top 1 * over(partition by PBK order by ntwk_cd desc) FROM PRAC12 WHERE ID = '098' and PBK=063; ... View more

Following is not working if I add max function before substr. Group_id is character data type. Any help?   MEMBER_NO||''||DEP_ID||'-'||max(SUBSTR(GROUP_ID, 1, 6))||max(SUBSTR(GROUP_ID, 8, 2))||'-'||PATIENT_DOB||'-'||PATIENT_GENDER_ID as ID MEMBER_NO||''||DEP_ID||'-'||max(SUBSTR(GROUP_ID, 1, 6)||SUBSTR(GROUP_ID, 8, 2))||'-'||PATIENT_DOB||'-'||PATIENT_GENDER_ID as ID ... View more

Below case when is not working when both the fields discharge_date and admit_date is NULL. If the values of both the fields are NULL then I want to display it as NULL in the field ADMIT_DISCHARGE but it's showing as '1'. Any help to correct this CASE WHEN or would like to know how to simplify it using with any other ways in SQL?   Both discharge_date and admit_date is character datatype   SELECT DISCHARGE_DATE, ADMIT_DATE, CASE WHEN DISCHARGE_DATE IS NULL THEN NULL WHEN ADMIT_DATE IS NULL THEN NULL WHEN DISCHARGE_DATE IS NULL AND ADMIT_DATE IS NULL THEN NULL WHEN DISCHARGE_DATE = ADMIT_DATE THEN '1' ELSE DISCHARGE_DATE - ADMIT_DATE END AS ADMIT_DISCHARGE   ... View more

May I know how to tweak/extend the following code to handle both the conditions at the same time? Both Zip and Zip_Pllus is character datatype.   I want to do this, catx('-',ZIP,ZIP_PLUS) only when ZIP and/or ZIP_PLUS is missing or NULL. Also I'd like to know what would be the alternative to Catx function which can be used in SQL?   case when not missing(zip) then catx('-',ZIP,ZIP_PLUS) else ' ' end as PRO_ZIP length=10 ... View more