substract/divide each row from first row

[Lior2020]

Hello,

I'm newbie with sas.

I'm having a table that has many columns (number&names of columns can be changed due to daily processing)

I need to "freeze" the first row, and create a new table such that:

Row(n) in NewTbl = Row(n) - Row(1) for all columns, except the first 3 columns, which names are constant so i can drop them and merge after (if necessary).

also i need another table such that:

Row(n) in NewTbl2 = Row(n)/Row(1) - 1

I would appreciate any help

Thanks in advance 

[DavePrinsloo]

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-tab...

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;

[Lior2020]

Thanks!
when i tried to use the macro, the result was a table with missing values.
i've added:
%put orig_num_cols
to see the reasult of the macro, but it's empty.

do you have any idea why?

[DavePrinsloo]

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?  

[DavePrinsloo]

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;

[Lior2020]

hi
after fixing 'new_prefix' to 'add_prefix' the code worked well on sashelp.cars.
for my data there are two problems:
first, i have many columns with 10-15 lettera name's length. so i'm getting an error 'The text expression length 65535 exceeds maximum length 65534....'

so i took a small subtable to check the code. now i got another error:
'Decimal specified for format NLDATE is invalid'.
after taking out the dates columns, the code worked well on small table.

i was thinking if there could be a better way of using proc content results for columns names due to the large data set,and than use some code for the calculations.or other way....

[DavePrinsloo]

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)

[Lior2020]

I've checked and my table contains 5216 columns, total length of all column's names is 87804 chars, larger than 65535

[PaigeMiller]

You can do the first part (x(n) – x(1)) without macros. Here's an example using the data set SASHELP.CLASS and PROC STDIZE

proc stdize data=sashelp.class(obs=1) method=mean outstat=first_obs;
	var _numeric_;
run;
proc stdize data=sashelp.class(firstobs=2) method=in(first_obs) out=want;
	var _numeric_;
run;

The second part (x(n)/x(1)-1) probably does require macros to determine the list of variables (as shown by @DavePrinsloo), but once you have the list of variables, you could trick PROC STDIZE into doing this calculations as well, once you use the macros to get the variable list. Although, once you have the list of variables, an ARRAY in a DATA step could also get the job done.

[Tom · Accepted solution]

No need for a multiple TABLE if you just want to assign values to multiple VARIABLES.

Below I will show a way you could keep your "matrix" shape, but it looks like you should just restructure your so that instead of multiple variables you have multiple observations.  Any matrix like that can just be transposed into a dataset that has one variable that has the values and one to identify the row number and one to identify the col number.   Let's say you have a table named TEMPERATURE with 100 observations that each have 20 readings at 20 different time points. Instead convert this to a dataset with 100*20 observations with one variable for the time point value and one for the temperature.  Now it is much clearer and easier to perform your calculations.

proc sql ;
create table want as 
select a.*
     , (a.temp-b.temp) as temp_change
     , (a.temp/b.temp - 1) as temp_pseudo_ratio
from have a
   , have b
where a.id = b.id
  and a.time > 1
  and b.time = 1
order by a.id,a.time
;
quit;

If you want to stick with your "table/matrix" structure then you just need to add a temporary array to remember the values of the first observation.  Let's say your original dataset is named HAVE and the variables are named TEMP1, TEMP2, .... etc.  So this step will create a new dataset with the same structure.  For every observation (expect the first one) the values will be decreased by the amount of that variable in the first observation.  I just picked 2000 as the size of the temporary array to make it larger than any reasonable input would have.  Remove the ELSE if you want the first observation replaced with the difference also.

data want ;
  set have ;
  array _first [2000] temporary ;
  array temp temp: ;
  if _n_=1 then do index=1 to dim(temp);
   _first[index] = temp[index];
  end;
  else do index=1 to dim(temp);
    temp[index] = temp[index] - _first[index];
  end;
run;