Use a single Redshift authentication domain and put the shared read/write access login on one group (Redshift Write Users) and the shared read access login on the other group (Redshift Read Only Users). Finally, ensure a user is only a member of one group or the other (either via direct or appropriate nested membership) depending on the access they should have. @PaulHomes So with a single pre-assigned library that uses an authdomain how does this work? Can you define two user ids/pw for the same authdomain and then secure who gets access/uses which one? ... View more

Below superseded by Paul Holmes advice.   On a metadate level this should just be about group memberships. On a physical level: DB access is granted by the credentials stored in the authdomain so everyone with read access to this authdomain will have the same access (use the same credentials). Anyone can also issue their own libname statement using this authdomain so the only way to secure this is via read/no read to the authdomain metadata object.    If your library is pre-assigned (=DIS won't generate a libname statement) then a "hack" to get what you want could be: 1. Create another authdomain CDP_Redshift_Prod_RO with a user that got only read access to the DB 2. Only give users with write access to Redshift metadata read access to authdomain CDP_Redshift_Prod 3. In the autoexec check metadata membership of the user under which the process executes. If this user isn't in the group for write access to Redshift but is in the group for read access to Redshift then issue libname statement LIBNAME ROCOUR SASIORST DSN=prod_cdp_ods_sas SCHEMA=xxxx AUTHDOMAIN="CDP_Redshift_Prod_RO " ; which then will overwrite the pre-assigned one. This "overwrite" libname statement must be in a usermods autoexec that gets processed both by batch and online sessions (like in the usermods under the App server).   This approach is not perfect and you need to ensure to keep this coded libname statement fully in sync with the metadata definition of the pre-assigned version.  ... View more

I would understand the term "Table Loader Step" in the context of SAS DIS or Viya Studio Flow - but what is the meaning of it in the context of SAS EG? For what you want: I believe you would need to write your own macro to collect such information. Potentially using Proc Scaproc in such a macro would give you what you want - but not sure. Suggest you show us in more details what you've got (like a picture of a relevant EG flow) and the code for your "table loader" for us to understand better what could be suitable. For the job name: Would this be the EG flow name or the EG node name ...or something else? And would it only need to work for programs run via EG?   Here some sample code how such log collection using Proc Scaproc could look like. Would of course need "generalization" via macro code for repeated use. data transactions; set sashelp.class; run; /* collect log info */ filename collect temp; proc scaproc; record collect; run; proc append data=transactions base=master; run; data out1 out2; set transactions; run; /* write log info to external file */ proc scaproc; write; run; /* parse external file to extract desired info */ data scratch(keep=clienttasklabel taskstarttime procname io processing ds3 _step) proc(keep=_step procname rename=(procname=_prn)); attrib clienttasklabel length=$32 taskstarttime length=8 informat=datetime. format=datetime22.2 procname length=$40. informat=$40. io length=$8 informat=$8. processing length=$8 informat=$8. ds3 length=$45 informat=$45. ; retain procname taskstarttime io processing ds3; retain clienttasklabel "&_clienttasklabel" _step 1; infile collect truncover dlm=' '; input @3 _jobsplit:$8. _type:$20. @; if _jobsplit='JOBSPLIT'; if _type='STEP' then do; call missing(taskstarttime, io, processing, ds3); output proc; _step+1; end; else if _type='TASKSTARTTIME' then do; input taskstarttime; end; else if _type='PROCNAME' then do; input procname; end; else if _type='DATASET' then do; input io processing ds3; output scratch; end; /* else input (_word1-_word4) (:$45.);*/ run; filename collect; data scratch; merge scratch proc; by _step; procname=_prn; drop _step _prn; run; /* print collected info */ proc print data=scratch; run;   ... View more

Given your number 1.040.855 with two dots this looks to me more like the display of a integer that uses dot as thousand separator and comma for decimal like it's common in many European countries. Because this is likely just about display (print) of numbers a simple change of the format used for display will give you what you want.  Below to demonstrate what I'm talking about. %let sv_locale=%sysfunc(getoption(locale,keyword)); options locale=German_Germany; data have; input numvar_1; numvar_2=numvar_1; numvar_3=numvar_1; format numvar_1 nlnum16. numvar_2 nlnum16. numvar_3 best32.; datalines; 17921 783 37948 1040855 0 109 237703 111 4387 ; title 'Print (display) numbers using formats from Have'; proc print data=have; run; title 'Print (display) numvar_2 using format best32.'; proc print data=have; format numvar_2 best32.; run; title 'Print (display) all numerical variable with format best32.'; proc print data=have; format _numeric_ best32.; run; options &sv_locale;   If above doesn't work for you then please share the result of a Proc Contents of your source data so we can understand what data type and format gets used for your values. ... View more

@DrAbhijeetSafai Just adding the bit of code required to store the result in a Excel sheet. Else just using Tom's code so suggest you accept his proposal as solution. options dlcreatedir; libname mylib "%sysfunc(pathname(work))\source"; data mylib.class; set sashelp.class; run; data mylib.air; set sashelp.air; run; proc contents data=mylib._all_ noprint out=contents; run; proc sort data=contents; by libname memname varnum ; run; libname exl xlsx "%sysfunc(pathname(work))\myexcle.xlsx"; proc transpose data=contents out=exl.wide(drop=_name_ _label_) prefix=var; by libname memname ; id varnum; var name; run; ... View more

@HeatherNewton wrote: thanks KSharp. please could you elaborate more? where is the 'add user' button? As I am supporting my client and dont have enterprise guide interface myself I am not able to test. If it is possible, please could you solutions in great details. Thanks. You're either the SAS Admin with all the necessary tools and permissions or you need to refer your client to the site SAS admin ....and then eventually support this admin in a screen share session if it's your client and the responsible person is only SAS admin by title (which in turn is a sign of insufficient project handover). If your client is not capable (or too small) to hire a capable SAS Admin then they are eventually a candidate for a SAS managed environment where SAS provides the SAS admin services. ... View more

@Ksharp  🤣  Sure, you're such a simple guy... A bit more in earnest: This was for a risk solution where we had to run a solution macro in batch that needed to authenticate against the mid-tier to create a auth token for rest calls. By having the pre-compiled macro with credentials in the WORK catalog we could "overlay" the SAS solution provided version that got called in a interactive scenario where the user was already authenticated against the mid-tier. ... View more

@Ksharp wrote: "so really interested why you can't share it." It is BOSS 's requirement. But in reallity, It haven't been done yet. Ah, yes, one of these requirements.... Another personal opinion: I don't like the mstore, especially in a Dev environment. It needs another library that you can't hide in EG/Studio and once assigned it's really hard to change anything in the macro catalog under mstore. What I've done last time: Compile the macros into a catalog in a read only location, create an %init macro that copies the secure compiled macros into the catalog under WORK. The macro code itself was also in a read only folder with tokens for user and password. And then a macro that can be called passing user and password as parameters that then compiles all the other macros and stores them in the read only catalog (for the unlikely but certainly rare case the PW needs to change; and this process fully documented in a run book). ... View more

@Ksharp wrote: Tom, But sometime we need to distribute my macro to other sas programmer,and not want to let others to see raw code,usually would secure these macro. like: %macro mymac/store secure; @Ksharp I don't believe you're someone who has to hide his code for job security so really interested why you can't share it. The only very few times I had to use secure compiled macros was for situations where I couldn't avoid having a password in the code. The only other reason I'm currently aware of for pre-compiling: Depending on the environment it can take a bit until a .sas file is found for compilation so for some interactive web application the reason for pre-compiling could be performance. I personally was never in such a situation but I also mostly implement backend processes. ... View more

That’s how I read the docu, yes. ... View more

@Ksharp wrote: It looks like %sysmacexist() only check the user defined macro ,not built-in system macro . 34 %put %sysfunc(ifc(%sysmacexist(scan), Yes Exists, Does not Exist)); Does not Exist @Ksharp  It looks like %scan() is not a traditional macro. At least I can't find it as a member of any macro catalog under SASROOT. According to the documentation %sysmacexist() only searches through work.sasmacr. It would still find SAS supplied macros like %tslit() or %lowcase() that only get compiled into work.sasmacr when called the first time in a session. ... View more

@PrinceAde wrote: Sorry, sir. I saw in the Text; SAS® Certified Professional Prep Guide: Advanced Programming Using SAS® 9.4 May-be share the section that confuses you and we can share how we understand it.   In SAS9.4 the only data types available for SAS tables are CHAR and NUMERIC. I didn't even know that Proc SQL allows for other datatypes - but they all result in type CHAR and NUM. proc sql; create table work.sample ( var1 char(20) ,var2 varchar(20) ,var3 integer ,var4 smallint ,var5 decimal ,var6 numeric ,var7 float(10,2) ,var8 real ,var9 double precision ,var10 date ) ; quit; proc contents data=work.sample; run;quit; It looks like when requesting a DATE type SAS also adds a DATE. format which is useful. ... View more

@pavank wrote: in log it shows  work.sort does not exist  here i am using CEXIST function to check macro catolog SORT exist or not I'm a bit confused if you want to check for existence of a SAS table or for existence of a compiled macro in the macro catalog under WORK.  Below code run in a fresh new session to demonstrate the different ways to query hopefully giving you sufficient pointers for what you need. data check_step_1; /* does TABLE work.demo1 exist? */ table_exist_flg=exist('work.demo1','data'); /* does compiled macro %sort exist as member in referenced catalog (here: work.sasmacr)? */ macro_compiled_flg_1=cexist('work.sasmacr.sort.macro'); /* does compiled macro %sort exist as member in catalog under WORK? */ macro_compiled_flg_2=%sysmacexist(sort); run; title 'check_step_1'; proc print data=check_step_1; run; /* execution of the macro definition compiles the macro into the catalog under WORK */ %macro sort (dname, new, svar); proc sort data=&dname out=&new; by &svar; run; %mend; data check_step_2; /* does TABLE work.demo1 exist? */ table_exist_flg=exist('work.demo1','data'); /* does compiled macro %sort exist as member in referenced catalog (here: work.sasmacr)? */ macro_compiled_flg_1=cexist('work.sasmacr.sort.macro'); /* does compiled macro %sort exist as member in catalog under WORK? */ macro_compiled_flg_2=%sysmacexist(sort); run; title 'check_step_2'; proc print data=check_step_2; run; /* calling the macro creates the the table as requested via parameter &new */ %sort(sashelp.class, demo1,sex); data check_step_3; /* does TABLE work.demo1 exist? */ table_exist_flg=exist('work.demo1','data'); /* does compiled macro %sort exist as member in referenced catalog (here: work.sasmacr)? */ macro_compiled_flg_1=cexist('work.sasmacr.sort.macro'); /* does compiled macro %sort exist as member in catalog under WORK? */ macro_compiled_flg_2=%sysmacexist(sort); run; title 'check_step_3'; proc print data=check_step_3; run; title;   ... View more

The following is not the solution for your question but it should avoid the problem. I don't have sufficient experience with scheduling under Viya and I guess available functionality also depends on what scheduler you are using (which is?).  Imho one should always try and create production jobs that don't end with a Warning condition. If the process can create a known and expected Warning condition that's o.k. then I'd document this in the code and would also implement some logic to reset the return code (&syscc). See below sample. data master; length var $20; var='AAA'; run; data trans; length var $10; var='BBB'; run; %let sv_syscc=&syscc; proc append base=master data=trans; run; /* reset &syscc to 0 if warning raised due to variable length mismatch between base and data tables */ %if &sv_syscc=0 and &syscc=4 and %sysfunc(find(&SYSWARNINGTEXT,different lengths,i))>0 %then %do; %let syscc=0; %end;     ... View more