OPTIONS COMPRESS=YES OBS=MAX FULLSTIMER NOCENTER VALIDVARNAME=ANY;
%LET SYSCC=0;
/* USER INPUT */
/* JFILE: PHYSICAL LOCATION OF JSON FILE CaSe SeNsItIvE WITH TICK MARKS*/
%LET JFILE='/appl/a1xprod2/trello/DARDC_20190213.txt';
/* END USER INPUT */
/* LOAD TABLE <<FIRST TIME ONLY>> */
%MACRO LOADDAR (D,T);
%LET OASISD=%STR(USER=&USERDARS. PASS=&PASSDARS. PATH='POAU9C1');
LIBNAME OASISD ORACLE &OASISD. SCHEMA='DARSBOX' ; RUN;
%IF (&SYSCC. < 5) %THEN %DO;
PROC DELETE DATA=OASISD.&T. ; RUN;
DATA OASISD.&T. (BULKLOAD=YES BL_OPTIONS='ERRORS=25' BL_DEFAULT_DIR="&PRODOUT."); SET &D.; RUN;
PROC SQL; CONNECT TO ORACLE AS OAS (&OASISD.); EXECUTE (GRANT SELECT ON DARSBOX.&T. TO DARSBOX_READ_RL) BY OAS; QUIT;
%END;
%MEND LOADDAR;
/* INSERT INTO EXISTING */
%MACRO UPDTDAR (D,T);
%LET OASISD=%STR(USER=&USERDARS. PASS=&PASSDARS. PATH='POAU9C1');
LIBNAME OASISD ORACLE &OASISD. SCHEMA='DARSBOX' ; RUN;
%IF (&SYSCC. < 5) %THEN %DO;
/* INSERT DATA INTO EXISTING TABLE */
PROC SQL; INSERT INTO OASISD.&T. (BULKLOAD=YES BL_OPTIONS='ERRORS=25' BL_DEFAULT_DIR="&PRODOUT.") SELECT * FROM &D.; QUIT;
%END;
%MEND UPDTDAR;
%MACRO CLRINPUT;
%IF (&SYSCC. < 5) %THEN %DO;
DATA _NULL_; CALL SYSTEM("mv &JFILE. /appl/a1xprod2/trello/loaded/"); RUN;
PROC DATASETS LIB=WORK MEMTYPE=DATA NOLIST KILL; QUIT;
%END;
%MEND CLRINPUT;
/* GET DATE FROM JSON FILE */
DATA _NULL_; CALL SYMPUT('RPTDT',INPUT(SUBSTR(&JFILE.,INDEX(&JFILE.,'_')+1,8),YYMMDD8.)); RUN;
/* LIBNAMES */
LIBNAME PRODOUT "&PRODOUT.";
LIBNAME IN JSON &JFILE. MAP='USER.MAP' AUTOMAP=REUSE;
/* ADDITIONAL DATE HANDLING */
DATA _NULL_ ;
CALL SYMPUT('ASOFDT',TRIM(LEFT(PUT(&RPTDT.,YYMMDDN8.)))) ;
CALL SYMPUT('RPTDATE',TRIM(LEFT(PUT(&RPTDT.,DATE10.)))) ;
CALL SYMPUT('TODAYDT',TRIM(LEFT(PUT(&RPTDT.,MMDDYYD8.)))) ;
RUN;
/* EXPORTING CSV */
%MACRO MAKECSV(DSN);
DATA _NULL_;
IF 0 THEN SET &DSN NOBS=NOBS;
CALL SYMPUT('NUM',TRIM(LEFT(PUT(NOBS,8.))));
STOP;
RUN;
%IF &NUM=0 %THEN %DO; **CHECK IF ZERO RECORDS FOUND.;
DATA &DSN.;
SET &DSN.;
DATA _NOOBS;
RUN_DATE='NO DATA ';
PROC APPEND BASE=&DSN. DATA=_NOOBS FORCE; /* APPEND TO DATASET BEING PRINTED */
RUN;
%END;
PROC EXPORT DATA=&DSN.
OUTFILE="&PRODOUT./&DSN._&TODAYDT..CSV"
DBMS=CSV REPLACE;
RUN;
%MEND MAKECSV;
/* START MAIN PROGRAM */
/* START MAIN PROGRAM */
/* START MAIN PROGRAM */
/* GET CARD INFORMATION */
PROC SQL;
CREATE TABLE C AS
SELECT
A.IDBOARD AS BOARD_ID
,B.NAME AS BOARD_NAME
,A.IDLIST AS LIST_ID
,A.SHORTLINK AS CARD_SHORTLINK
,A.URL AS CARD_URL
,A.ORDINAL_CARDS AS CARD_OID
,A.ID AS CARD_ID
,A.NAME AS CARD_NAME
,A.DATELASTACTIVITY
,COMPRESS(A.DESC,'0D0A'X) AS CARD_DESC LENGTH=1024 FORMAT=$1024.
FROM IN.CARDS A
INNER JOIN (SELECT DISTINCT ID, NAME FROM IN.DATA_BOARD) B ON A.IDBOARD=B.ID
ORDER BY A.ORDINAL_CARDS;
QUIT;
/* GET LIST INFORMATION */
PROC SQL;
CREATE TABLE L AS
SELECT
ORDINAL_LISTS AS LIST_OID
,ID AS LIST_ID
,NAME AS LIST_NAME
,IDBOARD AS BOARD_ID
FROM IN.LISTS
ORDER BY LIST_OID;
QUIT;
/* CARDS WITH LIST NAME */
PROC SQL;
CREATE TABLE CL AS
SELECT C.*, LIST_OID, LIST_NAME
FROM C
LEFT JOIN L ON C.LIST_ID=L.LIST_ID AND C.BOARD_ID=L.BOARD_ID
ORDER BY C.CARD_OID;
QUIT;
/* GET FIRST MEMBER ON CARD */
PROC SQL; CREATE TABLE M AS SELECT * FROM IN.MEMBERS; QUIT;
PROC SQL; CREATE TABLE CIM AS SELECT * FROM IN.CARDS_IDMEMBERS; QUIT;
PROC SQL;
CREATE TABLE CARD_M AS
SELECT
A.ORDINAL_CARDS AS CARD_OID
,A.ORDINAL_IDMEMBERS AS IDMEMBERS_OID
,M.FULLNAME AS MEMBER
,M.ORDINAL_MEMBERS AS MEMBER_OID
FROM CIM A
LEFT JOIN M ON A.IDMEMBERS1=M.ID;
QUIT;
PROC SORT DATA=CARD_M; BY CARD_OID IDMEMBERS_OID; RUN;
PROC SORT DATA=CARD_M NODUPKEY; BY CARD_OID; RUN;
/* GET ALL LABELS */
PROC SQL; CREATE TABLE CLB AS SELECT * FROM IN.CARDS_LABELS; QUIT;
PROC TRANSPOSE DATA=CLB OUT=CARD_LABELS(DROP=_NAME_) PREFIX=LABEL_;
BY ORDINAL_CARDS;
VAR NAME;
RUN;
/* LOAD MEMBER AND LABELS */
PROC SQL;
CREATE TABLE CLMB(DROP=BC CC IDMEMBERS_OID) AS
SELECT A.*, B.*, C.*
FROM CL A
LEFT JOIN CARD_M(RENAME=(CARD_OID=BC)) B ON A.CARD_OID=B.BC
LEFT JOIN CARD_LABELS(RENAME=(ORDINAL_CARDS=CC)) C ON A.CARD_OID=C.CC;
QUIT;
/* LOAD STANDARD DATA */
PROC SQL; CREATE TABLE DC AS SELECT * FROM IN.DATA_CARD; QUIT;
/* COLLAPSE DATA INTO ONE ROW PER CARD */
PROC SQL;
CREATE TABLE TMP AS
SELECT ID, MAX(DUECOMPLETE) AS DC, MAX(DUE) AS CDD /* TAKE MAX DUECOMPLETE AND DUE */
FROM DC
GROUP BY ID;
QUIT;
PROC SQL;
CREATE TABLE STD AS
SELECT
A.*
,TMP.DC AS DUECOMPLETE
,TMP.CDD AS DUE
FROM CLMB A
LEFT JOIN TMP ON A.CARD_ID=TMP.ID;
QUIT;
/* 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 /* 2/7/2019: ADDED CHECKED WITH BASE TEST CARD */
,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;
/* TURN DATA ONE VALUE AT A TIME AND MERGE TOGETHER AT THE END, 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;
/* FINAL OUTPUT - CLEAN UP DATES AND NUMBERS */
PROC SQL;
CREATE TABLE DARDC_CARDS AS
SELECT
DHMS(&RPTDT.,0,0,0) AS RUN_DATE FORMAT=DATETIME20.
,BOARD_NAME
,LIST_NAME
,CARD_NAME
,CARD_SHORTLINK
,CARD_URL
,CARD_ID
,DHMS(INPUT(SUBSTR(DATELASTACTIVITY,1,10),YYMMDD10.),0,0,0) AS CARD_LAST_UPD FORMAT=DATETIME20.
,CARD_DESC
,MEMBER
,LABEL_1
,CASE WHEN DUECOMPLETE=1 THEN 'Y' ELSE 'N' END AS STD_DUECOMPLETE_IND
,DHMS(INPUT(SUBSTR(DUE,1,10),YYMMDD10.),0,0,0) AS CARD_STD_DUE_DATE FORMAT=DATETIME20.
,CASE WHEN UPPER(INTAKE)='TRUE' THEN 'Y' ELSE 'N' END AS INTAKE_IND
,ASSIGNED_TO
,WORK_TYPE
,REQUESTOR
,INPUT(LOE_ESTIMATE,BEST32.) AS LOE_ESTIMATE FORMAT=8.2
,INPUT(LOE_ACTUAL,BEST32.) AS LOE_ACTUAL FORMAT=8.2
,DHMS(INPUT(SUBSTR(REQUESTED_DATE,1,10),YYMMDD10.),0,0,0) AS REQUESTED_DATE FORMAT=DATETIME20.
,DHMS(INPUT(SUBSTR(COMPLETED_DATE,1,10),YYMMDD10.),0,0,0) AS COMPLETED_DATE FORMAT=DATETIME20.
,DHMS(INPUT(SUBSTR(DUE_DATE,1,10),YYMMDD10.),0,0,0) AS DUE_DATE FORMAT=DATETIME20.
FROM COMBINED;
QUIT;
/* INSPECT OUTPUT */
TITLE 'INSPECT DATA';
PROC FREQ DATA=DARDC_CARDS; TABLES LIST_NAME*LOE_ESTIMATE LOE_ESTIMATE/LIST MISSING; RUN;
TITLE '';
/* OUTPUT */
%MAKECSV(DARDC_CARDS);
*%LOADDAR(DARDC_CARDS,TRELLO_DARDC_CARDS); /* FIRST TIME LOAD FOR THE TABLE */
%UPDTDAR(DARDC_CARDS,TRELLO_DARDC_CARDS); /* UPDATE TABLE IN EXISTENCE */
/* CLEARS ALL WORK DATASETS AND MOVE INPUT FILE TO LOADED */
%CLRINPUT;
/* END MAIN PROGRAM */
/* END MAIN PROGRAM */
/* END MAIN PROGRAM */
