As I understand your requirement you want a single table representing all the subjects in your study with the most up to date non null data for each.  This is what the UPDATE statement in a datastep is designed to do. You need to create a skeleton table to hold the wanted data. Proc SQL ;     Create Table Want Like Demog01_02 ; Quit ; It's worth creating a simple macro to do the job. %Macro NextYear(yr) ;   Data Want ;      Update want                 Demog&yr ;   Run ; %Mend ; Then for only 10 years copy and paste is efficient %NextYear(01_02 ) ; %NextYear(02_03 ) ; %NextYear(03_04 ) ; etc Regards Richard in Oz ... View more

Start with understanding the differences in date and time handling between SAS and MS Excel.  SAS measures dates in days; and times and datetimes in seconds.  Excel measures times in fractions of a day, so dates are just the integer part of a datetimevalue.  Hence in Excel to 'convert' a datetime value to a date value you apply a format which ignores the fractional time content of the datetime value.  This approach will not work in SAS because of the orders of magnitude difference between the underlying datetime value and the corresponding date value. If you are using Proc Import to import import xls files then your site has licensed the SAS Access to PC File Formats product.  This product manages the conversion from Excel date or datetime values automatically (it also has to apply an offset because day zero for Excel is 0JAN1900 (ie 31DEC1899)). SAS imports numerical values as numbers unless it identifies a date, time or datetime format in Excel, in which case the conversion takes place and SAS applies an appropriate SAS date, time or datetime format to the output value.  The csv import process does the same, based on the formatted date/time/datetime values it encounters.  However, in older versions of SAS at least it will not recognise the format 10/25/1955 12:00:00 AM and will simply import the data as text.  This may be why the datepart function gave the result zero. You can make SAS datetime values "look" like date values using the DTDATE format.  But the underlying value is not changed. Your options thus are do the conversion in Excel by simply changing the column format to a date format write (or convert) a datastep to read the csv data using a date informat eg MMDDYY10 to read the date part only of the text datetime data import the data from an xls sheet, check that the import has resulted in a numeric field in SAS, and apply the datepart function in a subsequent data or sql step in sas (also changing the format to a date format), as in response 2. ... View more

As you no longer require duplication of values to replace missing values the process can be simplified. Key features:      create an invalue seq for an input function rather than using put with automatic type conversion      Create a restricted Cartesian product of subjid and day (normally a bad idea!) to form a template of all  possible subjid and day combinations      Use a left join to update the template with value from new      Use the sum function, with zero, to convert missing values to zero NOTE:  This code generates values for subjid = 4 and day = week2 and week4, which are not in your want table.  If you use this code you may need a datastep to delete these values based on whether the last undeleted row was a baseline, if I understand your data correctly.  I can add that if you require. proc format;          invalue seq             'baseline'=1               'week2'=2               'week4'=3               'week8'=4               'week12'=5               ; quit ; data new;    length subjid $8 day $10 value 8;    input subjid day value;    datalines; 1  baseline 10 1  week2    12 1  week4    14 1  week8    16 1  week12   12 2  baseline 10 2  week2    12 2  week4    10 3  baseline 10 3  week2     3 3  week8     4 4  baseline 10 4  week8     4 ; Proc SQL ; Create Table template As   Select distinct     a.subjid    , b.day    ,  input (b.day, seq.) as seq   From new a    , new b   Order    By a.subjid    , seq      ;        Create Table  want  (drop = seq) As    Select     tmp.*     , Sum (new.value, 0) As value    From template tmp    Left Join      new   new     On new.subjid = tmp.subjid     And new.day  = tmp.day    Order     By tmp.subjid     , tmp.seq      ;            Select *    from want ;    Quit ; subjid    day         value ------      ---           ----- 1          baseline  10 1          week2     12 1          week4     14 1          week8     16 1          week12   12 2          baseline  10 2          week2     12 2          week4     10 2         week8     0 2          week12   0 3          baseline  10 3          week2     3 3          week4     0 3          week8     4 3          week12   0 4          baseline  10 4          week2     0 4         week4     0 4          week8     4 4          week12   0 Regards Richard in Oz ... View more

Have you considered placing the unzipped text files in a folder/directory to which you have exclusive read/write access?  I have seen this used successfully in several sites.  The advantage is that you can read and analyse the data without any password requirement, but other users cannot access the data even if they use your code.  Once the data has been read it can be zipped and password protected for archive, for additional security.  The same folder can be the library holding record level datasets, if required. ... View more

It seems clear to me from the log that the connection to EXTRCTN happens relatively quickly but the connection to EUSD takes longer.  The first %syslput statement appears to have executed successfully. Meanwhile the second %sylput statement (with remote=EUSD) appears to have been executed locally before the connection was complete.  I think you need some statement to force the connection before the %syslput is executed. I don't have access to a SAS environment with multiple remote sessions so this is my best guess: place dummy rsubmit statements before %syslput commands. %macro master_test(papa);   options autosignon=yes sascmd='sas -nosyntaxcheck -autoexec "C:\....\autoexec_for_MASTER.sas"';   rsubmit  process=EXTRCTN wait=yes ;   endrsubmit ;   %unquote(%NRSTR(%syslput _global_/like='papa' remote=EXTRCTN));   options autosignon=yes notes sascmd='sas -nosyntaxcheck -autoexec "C:\....\autoexec_for_MASTER.sas"';   %let e_construction_type=&papa;   rsubmit  process=EUSD wait=yes ;   endrsubmit ;   %unquote(%NRSTR(%syslput _global_/like='e_construction_type' remote=EUSD));   rsubmit  process=EUSD wait=no persist=no;        data a;             a=&e_construction_type;        run;   endrsubmit;   waitfor _all_ EUSD;   endrsubmit; %mend; ... View more