Thanks for the heads up! A long time, when FCMP was initially released, I used it a lot more, but now I use it in a more measured manner when processing large SAS tables (millions of rows) because of the performance hit. ... View more

I would suggest you look into partitioning in the database. That way you can drop old partititions almost instantly, and the database table is not locked whilst adding thousands of records. ... View more

Why do you want all the text in a single column? Maybe there is a better solution to your real problem, rather than making a new problem caused by the maximum column length ... View more

I think your main issue has been answered, but have a look at the formats starting with E8601 (Eg. E8601DN10.) Also look into formats NLDATE or YYMMDDP or YYMMDDN. The datetime19. formats mentioned elsewhere have English month names in them, and after Brexit, only the Irish speak English in the EU 😞 ... View more

I think you are confusing macro statements and data step statements. If you are not experienced in writing macros, you should always use OPTIONS MPRINT; to see what code is being generated. As another tip, write the code without a macro first. (You dont have to write 24 lines, just 3 will do fro a start) Dont try writing a macro until that works! Then replace repetitive code and with macro statements, and use OPTIONS MPRINT to see that you are generating the same code that worked without a macro. ... View more

I have seen an error message like The text expression length 65535 exceeds maximum length 65534....' when reading from an xlsx sheet where there were too many columns. Ti fix it it, mark the real data, copy it to another sheet, delete the original sheet and rename the new new sheet. The logic i suggest assumes the input columns names are 31 chars long (as I add the prefix) and the macro assumes that there column names are standard SAS column names (no blanks, no special characters). The NLDate format should be specified without a decimal. eg. NLDATE10 (and not NLDATE10.2) ... View more

I am fixing a typo - had 3 instead of _i_ and added a couple of lines for the 2nd calculation %let orig_num_cols = %ut_varlist(table=intable, type=NUM); /* create a 2nd list of the numeric columns but add an underscore prefix to each column name */ %let copy_row_cols=%ut_varlist(table=intable, type=NUM, new_prefix=C); %let copy_retain_cols= %ut_varlist(table=intable, type=NUM, new_prefix=_); data new_table1 new_table2 ; set intable; keep &orig_num_cols. ; array orig_nums &orig_num_cols; array copy_nums &copy_row_cols.; /* create new cols as copy of first row and retain the values*/ length &copy_retain_cols 8; retain &copy_retain_cols; array row1_cols &copy_retain_cols; /* copy the values to the retained new columns */ if _n_=1 then do; do _i_= 1 to dim( orig_nums); row1_cols(_i_)=orig_nums(_i_); end; end; /* calculate row n */ do _i_=1 to dim(orig_nums); /* copy the rows input values so they original cols can be overwritten in the 1st calculation but used for the 2nd calculation*/ copy_nums(_i_)=orig_nums(_i_) ; /* calculation for new_table1 */ orig_nums(_i_) =copy_nums(_i_) - row1_cols(_i_); end; output new_table1; /* 2nd calculation for 2nd table*/ do _i_=1 to dim(orig_nums); orig_nums(_i_) =copy_nums(_i_) / row1_cols(_i_); end; output new_table2; run; ... View more

I assumed that since you are doing arithmetic, that there are numeric columns. could you run a proc contents on the input table and post the results here,   Does the sas log show any warnings or errors?  It should have if the input table does not exist.  Can you post your log here please! Could you try the macro call using sashelp.cars.  Does that produce results?   ... View more

This is untested code, assuming input table is called intable.     This uses a macro that was posted in https://communities.sas.com/t5/SAS-Programming/Macro-function-to-return-a-list-of-variables-in-a-table-in-a/m-p/633536 Download the attached macros and either %include them or add to your sasautos macro library.    The code creates the 1st wanted table, but ou can simply copy and modify the code for the 2nd one. %let orig_num_cols = %ut_varlist(table=intable, type=NUM); /* create a 2nd list of the numeric columns but add an underscore prefix to each column name */ %let copy_retain_cols= %ut_varlist(table=intable, type=NUM, new_prefix=_); data new_table1  ; set intable; array orig_nums &orig_num_cols; /* create new cols as copy of first row and retain the values*/ length  &copy_retain_cols 8; retain  &copy_retain_cols; array row1_cols &copy_retain_cols; /* copy the values to the retained new columns */ if _n_=1 then do;    do _i_= 1 to dim( orig_nums);   row1_cols(_i_)=orig_nums(3);  end; end; /* calculate row n */ do _i_=1 to dim(orig_nums);     orig_nums(_i_) = orig_nums(_i_) - row1_cols(_i_);  end; /* drop the copy of row columns, and _i_ */ drop _i_ &copy_retain_cols;  run;   ... View more