The code is fine, I get results, however the data in Custom Fields is correct for some of the cards, but not all. So my question is for folks who have successfully parsed information from the custom fields in a card from a Trello board. Have I created the correct logic to get the most recent custom field value for each card? The part of the code that deals with Custom Fields: LIBNAME IN JSON &JFILE. MAP='USER.MAP' AUTOMAP=REUSE;
/* LOAD CUSTOMFIELD DATA */
PROC SQL;
CREATE TABLE DCFI AS
SELECT
A.ORDINAL_CUSTOMFIELDITEM AS CFI_OID
,A.IDMODEL AS CARD_ID
,A.IDCUSTOMFIELD AS CF_ID
,B.NAME AS CF_NAME
,A.IDVALUE
FROM IN.DATA_CUSTOMFIELDITEM A
INNER JOIN IN.CUSTOMFIELDS B ON A.IDCUSTOMFIELD=B.ID
ORDER BY A.ORDINAL_CUSTOMFIELDITEM;
QUIT;
/* GET VALUES */
PROC SQL;
CREATE TABLE CF_VALUES AS
SELECT
DCFI.*
,COALESCE(TEXT,DATE,NUMBER,CHECKED,CFOV.OPT_NAME) AS CF_VALUE
,CFIV.TEXT
,CFIV.DATE
,CFIV.NUMBER
,CFIV.CHECKED
,CFOV.OPT_NAME
FROM DCFI
LEFT JOIN IN.CUSTOMFIELDITEM_VALUE CFIV
ON DCFI.CFI_OID=CFIV.ORDINAL_CUSTOMFIELDITEM
LEFT JOIN ( SELECT CFO.ID AS OPT_ID, OV.TEXT AS OPT_NAME
FROM IN.CUSTOMFIELDS_OPTIONS CFO
INNER JOIN IN.OPTIONS_VALUE OV
ON CFO.ORDINAL_OPTIONS=OV.ORDINAL_OPTIONS ) CFOV
ON DCFI.IDVALUE=CFOV.OPT_ID
ORDER BY DCFI.CARD_ID, DCFI.CFI_OID ;
QUIT;
TITLE 'CHECK COALESCE IF COMBINED_COUNT=TOTAL_VALUES THEN WE ARE GOOD';
PROC SQL;
SELECT SUM(TC,DC,NC,CC,OC) AS COMBINED_COUNT, TOTAL_VALUES
FROM (
SELECT
COUNT(TEXT) AS TC
,COUNT(DATE) AS DC
,COUNT(NUMBER) AS NC
,COUNT(CHECKED) AS CC
,COUNT(OPT_NAME) AS OC
,COUNT(CF_VALUE) AS TOTAL_VALUES
,COUNT(*) AS TOTAL_RECORDS
FROM CF_VALUES
);
QUIT;
TITLE;
/* FIND MOST RECENT (MR) VALUE BY CF_OID FOR EACH CARD_ID CF_ID */
PROC SQL;
CREATE TABLE CF_MR_VALUES AS
SELECT
A.CARD_ID
,A.CFI_OID
,A.CF_ID
,UPPER(TRANSLATE(TRIM(A.CF_NAME),'_',' ')) AS CF_NAME LENGTH=14 FORMAT=$14.
,A.CF_VALUE
FROM CF_VALUES A
INNER JOIN (SELECT CARD_ID, CF_ID, MAX(CFI_OID) AS MAX_CFI_OID
FROM CF_VALUES GROUP BY CARD_ID, CF_ID) B
ON A.CFI_OID=B.MAX_CFI_OID;
QUIT;
/* OUTPUT SHOULD BE 1 ROW PER CARD */
PROC TRANSPOSE DATA=CF_MR_VALUES OUT=CARD_CFV(DROP=_NAME_);
BY CARD_ID;
ID CF_NAME;
VAR CF_VALUE;
RUN;
/* ADD CF DATA TO CARD */
PROC SQL;
CREATE TABLE COMBINED(DROP=CFVID) AS
SELECT A.*, B.*
FROM STD A
LEFT JOIN CARD_CFV(RENAME=(CARD_ID=CFVID)) B ON A.CARD_ID=B.CFVID;
QUIT;
... View more
