Hello - we want to develop code that would force the job to exit based on a certain value in a table.   Basically this is for a validation check - if the count in a table is zero, the job should finish successfully. If the count is > zero - then the job should error out and stop processing.    If there a way to force a job to stop based on this logic? Thank you! ... View more

Hello,   I'm trying to use SAS Add-in for Excel with a stored process having one input parameter. My customer would like the ability to enter multiple input parameters at a time (i.e. copy and paste 30 SSNs at once instead of entering one at a time with the input parameter box).   I discovered that I can use the instream feature with the stored process. I've got it set up so that the add-in allows me to select the list of SSNs - but then this error arises:   WARNING: Apparent symbolic reference SSNLIST not resolved.   ERROR: CLI describe error: Microsoft ODBC SQL Server Driver SQL Server Invalid column name '&SSNList'. : Microsoft ODBC SQL Server Driver SQL Server Statement(s) could not be prepared.   Here is my stored process code: -------------------------------------------------------------------------------- libname Instr xml;   data work.SSNList;   set Instr.&_webin_sasname; run;   proc sql; connect to odbc ("dsn=SQL server DW; Trusted_Connection=yes; database=DW");     select * from connection to odbc (SELECT t1.IP_Key,                  t1.client_address,                 t1.createddate,                 t1.confirm_num,                 t1.ssn,      FROM Test1.XXX_Address_VW t1   WHERE t1.SSN IN ("&SSNList") );   disconnect from odbc; QUIT; -------------------------------------------------------------------------------------   I know that the error is with "&SSNList" - but what should it be to use the instream feature?   Thank you for any suggestions. ... View more

Hello,   I have a DI Studio job with a User Written transformation where I'm calling a SQL stored procedure. I need to pass today's date to the stored procedure. My code is giving me this error:   ERROR: CLI execute error: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'convert'.   Here is the code:   proc sql; connect to odbc ("dsn=SQL server Xxx; Trusted_Connection=yes; database=Xxxxxx"); execute (CCR_Rpt.spUpdate_Pers_Pref_Report @RunDate = convert(date, getdate())) by odbc;   What do I need to do to correctly pass today's date to the stored procedure?   Thank you for your help!   DI Studio 4.9     ... View more

Hello,   How can I read the file date from an external file in SAS?   I have a job that is already reading the fline name to load in multiple external files daily – I would like to include the date of the file in the data set I create.   For example if the file was created on 3/27/2017 - I would like the code to read in 3/27/2017 so I can add it to my data set.   I’m using User Written code in DI Studio to read in the file name – but I can't find how to read in the file date. Here’s the code I have so far: --------------------------------------------------------   filename DIRLIST pipe 'dir "\\filelocation\*.csv" /b '; data dirlist ;    infile dirlist lrecl=200 truncover;    input InFile_name $100.; run;   data _null_;    set dirlist end=end;    count+1;    call symputx('read'||put(count,7.-l),cats('filelocation',InFile_name));    call symputx('dset'||put(count,7.-l),scan(InFile_name,1,'.'));    if end then call symputx('max',count); run;   options mprint symbolgen; %macro readin;    %do i=1 %to &max;         data &&dset&i;          infile "&&read&i" lrecl=1000 truncover dsd;          input var1 $ var2 $ var3 $;        run;      %end; %mend readin;   libname BICTMPOD '\\filelocation\TEMPODBC';   data BICTMPOD.work_In_File_Names;   set dirlist;   run;   ... View more