I have the following table in SAS EG: Date             Referral Type                 Vol       365 Days Ago    Vol 365 Days Ago 11/19/2020   BUSINESS RULES           21    11/20/2019                      0 11/19/2020   OTHER DEPT RFRRLS     5    11/20/2019                       0 11/18/2020   BUSINESS RULES       1267    11/19/2019                      0 11/18/2020   PROPERTY SURVEY     144    11/19/2019                      0 11/18/2020   OTHER DEPT RFRRLS    69    11/19/2019                      0   The "365 Days Ago" column is calculated by this formula: INTNX('day',t1.REFERRED_DATE, -365)   Volume that corresponds to the "365 Days Ago" date and the Referral Type is further down in the table in the "Vol" column.  I am trying to get the value from the "Vol" column to populate the "Vol 365 Days Ago" column, where the values in "Date" column and the "Referral Type" align with the "365 Days Ago" value.  Fore example, if the volume for the date of 11/20/2019 where the type is BUSINESS RULES is 50, then the first row returned would be: Date             Referral Type                 Vol       365 Days Ago    Vol 365 Days Ago 11/19/2020   BUSINESS RULES           21    11/20/2019                      50   I have been search the Community library but not finding what I need.  Any thoughts/ideas? Thanks! ... View more

I have the following code to load JASON output in to my SAS project: filename out TEMP; filename cacm TEMP; proc http url='https://XYZ.STU.com/api/now/table/...policy.contract_state_code&sysparm' method="GET" out=out headerout=cacm auth_basic webusername="cacm_rest_user" webpassword="XXXXXXXXXXXXXXXXXXXXX"; run; libname CACM json "%sysfunc(pathname(out))" ; proc copy inlib=cacm outlib=WORK; run; libname CACM JSON "%sysfunc(pathname(out))" fileref=data; data out; set CACM.alldata; run; data _null_; rc = jsonpp("data","log"); run;   This works fine and gives me an "OUT" table, a "RESULT" table and an "ALLDATA" table.  However, it stops my process flow there unless I manually run the branch.  I want to continue the process flow, which clean up the data and joins with other tables in the SAS project.  I think I am missing a step to be able to use the output in these three tables as an input to other part of my SAS project.  I do get the following error when I run the above code: ERROR: Physical file does not exist, /config/sas94/Lev1/SASApp/data. Any ideas? ... View more

I have a column of interaction logs that vary in length and I am looking to extract just the "Subject: CDF" portion (see the example below) of the string of characters.  I would normally use the subpad or scan or substr functions, in this case the position of the "S" in the "Subject: CDF" portion changes from row to row. Any Ideas? Data example: "11:58:35: Created interaction. 11:58:35: QUEUENAME 11:58:35: Logging To 11:58:35: To: Mailbox, XXXXXX [XXXXXX@abc.com] 11:58:35: Logging From 11:58:35: From: xxxxxxxx@abc.com 11:58:35: Logging Subject 11:58:35: Subject: CDF IA 2017-08-17T11:50:38 11:58:35: Check State NonPilot 11:58:35: Auto Attendant 11:58:35: Get Work Type....." What I want to return is just "CDF" of the subject line.  Also the length of the "CDF" can vary from line to line.  That is in the first row it might be "CDF", however the next row might be "FGHIJ", etc.   Thanks!  ... View more

I brought data in from an API to SAS which gave me the following output: P P1      P2                                             V      Value 1 result                                                   0 2 result c_number                                  1      123 2 result cp_policy.contract_state_code  1      OH 2 result cref_referred_date                     1      2019-09-23 2 result cref_response_date                   1      2019-09-27 2 result c_case_open_timestamp           1      2019-09-23 1 result                                                    0 2 result c_number                                   1      456 2 result cp_policy.contract_state_code   1      MO 2 result cref_referred_date                      1      2020-01-24 2 result cref_response_date                    1      2020-02-04 2 result c_case_open_timestamp            1      2020-01-24 1 result                                                     0 2 result c_number                                    1      789 2 result cp_policy.contract_state_code    1      MN 2 result cref_referred_date                       1      2020-03-19 2 result cref_response_date                     1      2020-03-19 2 result c_case_open_timestamp             1      2020-03-13   Now I want to transpose so I get the following: c_number cp_policy.contract_state_code cref_referred_date cref_response_date c_case_open123           OH                                             2019-09-23            2019-09-27              2019-09-23 456           MO                                            2020-01-24             2020-02-04              2020-01-24 789           MN                                            2020-03-19             2020-03-19              2020-03-13   SAS EG wrote the following code, but I only get the last row of data to come over (i.e., 789    MN    2020-03-19         2020-03-19        2020-03-13):   /* ------------------------------------------------------------------- Code generated by SAS Task Generated on: Friday, August 21, 2020 at 11:54:11 AM By task: Transpose Input Data: SASApp:WORK.OUT Server: SASApp ------------------------------------------------------------------- */ %_eg_conditional_dropds(WORK.TRNSTransposed, WORK.SORTTempTableSorted); /* ------------------------------------------------------------------- Sort data set SASApp:WORK.OUT ------------------------------------------------------------------- */ PROC SQL; CREATE VIEW WORK.SORTTempTableSorted AS SELECT T.Value FROM WORK.OUT as T ; QUIT; PROC TRANSPOSE DATA=WORK.SORTTempTableSorted OUT=WORK.TRNSTransposed(LABEL="Transposed WORK.OUT") PREFIX=Column LET NAME=Source LABEL=Label ; VAR Value; /* ------------------------------------------------------------------- End of task code ------------------------------------------------------------------- */ RUN; QUIT; %_eg_conditional_dropds(WORK.SORTTempTableSorted); TITLE; FOOTNOTE;   Any ideas on how I should edit the code to bring over all rows of data instead of just the last line?         ... View more