In SAS Data Integration Studio External Files are read using the File Reader transformation, this will read out the necessary information and build a DATA Step from it. So usually there is no need to build your own transformation. If you want to read out this information in your code, then there is the SAS(R) 9.4 Language Interfaces to Metadata. The Metadata Model is described in the documentation, Information on an External file is here SAS(R) 9.4 Metadata Model: Reference To query the Metadata Server for information about Metadata Objects, one can use Proc Metadata, or you can use DATA Step functions. Find below a sample program, that will read out all External File definitions and some information about each column within a file. In order to be able to communicate with a Metadata Server you have to set the appropriate Metadata Server related SAS System Options such as  METASERVER, METAUSER, METAPASS, ... If you run the program using SAS Enterprise Guide, you might already have a connection to the Metadata Server data _null_;   length     file_uri $ 256     file_name $ 256     file_puri $ 256     file_pname $ 256     col_uri $ 256     col_name $ 60     col_sasname $ 32     col_sastype $ 1   ;   call missing(OF file_:, OF col_:);   n = 1;   nobj=metadata_getnobj("omsobj:ExternalTable?@Id contains '.'", n, file_uri);   do i = 1 to nObj;     rc = metadata_getnobj("omsobj:ExternalTable?@Id contains '.'", i, file_uri);     rc = metadata_getattr(file_uri, "Name", file_name);     rc = metadata_getnasn(file_uri, "OwningFile", n, file_puri);     rc = metadata_getattr(file_puri, "Filename", file_pname);     putlog "INFO: Table " i= rc= file_uri= file_name= file_puri= file_pname=;     nCols = metadata_getnasn(file_uri, "Columns", n, col_uri);     do j = 1 to nCols;       rc = metadata_getnasn(file_uri, "Columns", j, col_uri);       rc = metadata_getattr(col_uri, "Name", col_name);       rc = metadata_getattr(col_uri, "SASColumnName", col_sasname);       rc = metadata_getattr(col_uri, "SASColumnType", col_sastype);       putlog "INFO: Column " j= rc= col_uri= col_name= col_sasname= col_sastype=;     end;   end; run; ... View more

Sounds like a heck of time for so little records, independent of update technique. And yes, there are stuff you can do to optimize the code. But first things first. Locate in the log the step(s) that consumes the most resources, and try to start from there. What aboit other processing - is this the only job that performs poor? Or coudl it be a system constraint? Why are you using the Type 1 Loader? I seldom use it. Depending on your requirement you could use a standard Table Loader, or the Type 2 Loader. ... View more

That looks like the job for an array in a data step. Same solution as above, just no macro code. data w2lcrdg;      set &syslast;      length carry1-carry50 8.;          array  carry (50) carry1-carry50   ;      array avail(50) avail1-avail50;           do i=1 to 50;      carry(i) = max(0, carry(i-1) - avail(i));      end;      drop avail:  ; run; ... View more

Just take a deep breath... For future considerations, doing ETL and DW is far more easy if the data is transposed, leaving fewer columns to maintain in metadata. Have nice evening! ... View more

The other mentioned issue with numerics is known as precision. Apply a correct format and rounding is included. https://support.sas.com/techsup/technote/ts654.pdf  ... View more

If you want to set the length of the variable you are creating based on the NAME of the variable (&LEVEL) then you can do it this way.  Basically create a local macro variable that you will set to 5 or 20 based on the value of LEVEL.  I am not sure what logic you want, below I show how to do it by have a list of values that cause the variable to be defined with length=5 . %macro builder (horizon= ,level= ,output_name= ,output_row= ,date_var= ,calculation= ,data_source= ,where= ); %local i length short; %let short = AAA BBB CCC ; %if %sysfunc(indexw(%upcase(&short),%upcase(&level)) %then %let length=5; %else %let length=20 ; proc sql;   create table &horizon._&level as     select       unit     , compress(&domain) as domain length=2     , &level length=&length     , "&output_name" as output_name length=50     , &output_row as output_row %do i=1 %to 10     , sum(case when datdif(date()-&dateoffset, &date_var, 'act/act') = &i                 then &calculation                 else 0 end) as d&i %end;     from &data_source     %unquote(&where)     group by business_unit, domain, &level   ; quit; proc append base=perm.&horizon._&level data=TEM_&level force; run; %mend builder; ... View more