topic EGRC 6.1 - How to copy attachments from Risk Event object in SAS Programming

EGRC 6.1 - How to copy attachments from Risk Event object

JorgeTavares — Fri, 12 Jul 2019 10:05:56 GMT

Hello!

I need to copy attachments associated with risk events from EGRC 6.1 to a folder. There are about 300 risk events that i need to copy the attachments. How can I do this through SAS code?

My sample code (some code was obtained from the community)

proc sql noprint;
select t1.BUSINESS_OBJECT_RK into: rk saperated by '-' 
FROM SASOPRSK.ATTACHMENT_L t1, SASOPRSK.OP_RISK_EVENT_L t2, SASOPRSK.DIM_POINT_L t3
WHERE (t1.SOURCE_SYSTEM_CD = t2.SOURCE_SYSTEM_CD AND t1.BUSINESS_OBJECT_RK = t2.OP_RISK_EVENT_RK AND t2.DIM_POINT_RK = t3.DIM_POINT_RK) AND t1.ATTACHMENT_TYPE_CD ne "LNK" and (t1.BUSINESS_OBJECT_NM = 'OP_RISK_EVENT' AND t3.AUX_RMG_DIM_1_RK = 10023);

select FILE_NM into: file saperated by '-'
FROM SASOPRSK.ATTACHMENT_L t1, SASOPRSK.OP_RISK_EVENT_L t2, SASOPRSK.DIM_POINT_L t3
WHERE (t1.SOURCE_SYSTEM_CD = t2.SOURCE_SYSTEM_CD AND t1.BUSINESS_OBJECT_RK = t2.OP_RISK_EVENT_RK AND t2.DIM_POINT_RK = t3.DIM_POINT_RK) AND t1.ATTACHMENT_TYPE_CD ne "LNK" and (t1.BUSINESS_OBJECT_NM = 'OP_RISK_EVENT' AND t3.AUX_RMG_DIM_1_RK = 10023);
quit;

%macro attachment;
proc sql noprint;

select count(*) into: count
FROM SASOPRSK.ATTACHMENT_L t1, SASOPRSK.OP_RISK_EVENT_L t2, SASOPRSK.DIM_POINT_L t3
WHERE (t1.SOURCE_SYSTEM_CD = t2.SOURCE_SYSTEM_CD AND t1.BUSINESS_OBJECT_RK = t2.OP_RISK_EVENT_RK AND t2.DIM_POINT_RK = t3.DIM_POINT_RK) AND t1.ATTACHMENT_TYPE_CD ne "LNK" and (t1.BUSINESS_OBJECT_NM = 'OP_RISK_EVENT' AND t3.AUX_RMG_DIM_1_RK = 10023);
quit;

%do i = 1 %to &count;
*filename out temp;
%let rk_l=%scan(%bquote(&rk), %bquote(&i) ,%str(-));
%let file_l=%scan(%bquote(&file), %bquote(&i) ,%str(-));

options dlcreatedir;
libname newdir "E:/anexos"; libname pasta "E:/anexos/&rk_l"; filename out "E:/anexos/&rk_l/&file_l";
libname newdir clear;
libname pasta clear;

proc http
method="get"
url="http://lqc6001sas05.unix.grupo.com:7980/SASContentServer/repository/default/sasdav/Products/SASEnterpriseGRC/EnterpriseGRCMidTier6.1/Content/op_risk_event/&rk_l/&file_l" 

webUserName="sasadm@saspw"
webPassword="****"
out=out;
run;
%end;
%mend;
%attachment;

With this code I get the error:

HTTP Status 404 The requested resource is not available

Thank you in advance for your help,

Jorge

Re: EGRC 6.1 - How to copy attachments from Risk Event object

ballardw — Thu, 11 Jul 2019 17:53:50 GMT

Please post code into a code box opened using the forum's {I} or "running man" icon.

I'm not going to even attempt to read code as posted.

Re: EGRC 6.1 - How to copy attachments from Risk Event object

JorgeTavares — Fri, 12 Jul 2019 10:14:53 GMT

I was using IE11 and with this browser the toolbar did not appear. I passed to the Chrome and the toolbar appeared with the "insert code" button.

Thanks

Re: EGRC 6.1 - How to copy attachments from Risk Event object

ballardw — Fri, 12 Jul 2019 16:33:20 GMT

@JorgeTavares wrote:

I was using IE11 and with this browser the toolbar did not appear. I passed to the Chrome and the toolbar appeared with the "insert code" button.

Thanks

I'm using IE11 and don't have a problem with seeing the tool bar but who knows what local settings may have been inflicted on you.

Did you get the connection to work with a hardcoded (no macro variables at all) before attempting the macro code?

When testing macros you want to use the system options MPRINT and often SYMBOLGEN to see what actual code was created. It is a good idea when creating filenames or connection strings to use the dot at the end of the macro variables to make sure that the macro processor knows the difference between the end of your macro value and the "boiler plate" part of the string.

url="http://lqc6001sas05.unix.grupo.com:7980/SASContentServer/repository/default/sasdav/Products/SASEnterpriseGRC/EnterpriseGRCMidTier6.1/Content/op_risk_event/&rk_l./&file_l."

You really want to make sure that the rk_l and file_l variables don't have such things as leading spaces or / characters.

I am a little concerned about the use of %bquote on your RK variable as if you have some of the characters that %bquote protects in the values they are likely to be problems in the connection string.

%bquote is not needed in the %scan function for &I or the dash character. Example:

%macro dummy;
%let rk=something separated-by-dashes when-I do not-the actual data;
%do i= 1 %to %sysfunc(countw(&rk.,-));
   %let rk_l = %scan(&rk,&i,-);
   %put RK_L is &rk_l.;
%end;
%mend;
%dummy;

Just to make the code a little cleaner.

I am marginally concerned about the possibility of mismatched rk_l and file_l if values are missing for any of the records.

And instead of making two (potentially quite time consuming) passes through the data I might be tempted to create the rk_l/file_ value directly such as :(obviously untested code)

%macro dummy;
%let rk=something separated-by-dashes when-I do not-the actual data;
%do i= 1 %to %sysfunc(countw(&rk.,-));
   %let rk_l = %scan(&rk,&i,-);
   %put RK_L is &rk_l.;
%end;
%mend;
%dummy;

%macro attachment;

proc sql noprint;
select catx('/', strip(t1.BUSINESS_OBJECT_RK),strip(FILE_NM) ) into: list seperated by '-' 
FROM SASOPRSK.ATTACHMENT_L t1, 
     SASOPRSK.OP_RISK_EVENT_L t2, 
     SASOPRSK.DIM_POINT_L t3
WHERE (t1.SOURCE_SYSTEM_CD = t2.SOURCE_SYSTEM_CD 
      AND t1.BUSINESS_OBJECT_RK = t2.OP_RISK_EVENT_RK 
      AND t2.DIM_POINT_RK = t3.DIM_POINT_RK) 
      AND t1.ATTACHMENT_TYPE_CD ne "LNK" 
      and (t1.BUSINESS_OBJECT_NM = 'OP_RISK_EVENT' 
      AND t3.AUX_RMG_DIM_1_RK = 10023);
quit;

%let count=&sqlobs;
options dlcreatedir;

%do i=1 %to &count;
   %let List_l = %scan(%bquote(&list.),&i,-);
   libname newdir "E:/anexos"; 
   %let rk_l= %scan(&list_l,1,/);
   libname pasta "E:/anexos/&rk_l."; 
   filename out "E:/anexos/&list_l.";
   libname newdir clear;
   libname pasta clear;

   proc http
   method="get"
   url="http://lqc6001sas05.unix.grupo.com:7980/SASContentServer/repository/default/sasdav/Products/SASEnterpriseGRC/EnterpriseGRCMidTier6.1/Content/op_risk_event/&list_l." 

   webUserName="sasadm@saspw"
   webPassword="****"
   out=out;
   run;
%end;
%mend;
%attachment;

The catx function will create strings like rkvalue/listvalue. Strip function makes sure you don't have leading or trailing spaces.

The macro variable &sqlobs contains the number of records resulting from the last proc sql select so no need to make a separate call just to count records. Or use the %sysfunc(countw()) approach shown in the dummy macro.

I'm wonder why create libnames just to clear them though. Perhaps you meant the CLEAR statements to go AFTER the proc http??

404 errors often arise from incorrect paths so the actual content of your URL string is suspect.