Columns multiplication with their corresponding variable names II

Reply
Contributor
Posts: 40

Columns multiplication with their corresponding variable names II

[ Edited ]

Dear Experts,

 

Earlier I asked about a question regarding "columns multiplication with their corresponding variable names".

 

I have assumed my columns are properly sorted. It may not be so. Please see the dataset, "Have". Output is weight * height.

I have highlighted an expert's code that may need to be changed. 

 

The output should be

Date X_hw Y_hw Z_hw

1990  5000 8000 .

1991  2400 9000 .

 

data Have;
input Date X_height X_weight X_pressure Y_weight Y_height Y_pressure Z_height Z_pressure
datalines;
      1990   100       50     10         40     200       11           80      33
      1991    80       40     20         30     300       22           70      44
;

%* rearrange the columns with date as first column follow by their IDs and their corresponding descriptors;

proc sql;
select name into :sorted_cols separated by ' '
from dictionary.columns
where libname = 'WORK' and memname = 'Have'
order by name;
quit;

 

data Have;
retain yyyymmdd;
set Have;
run;

 

%* from Rick_sas;

 

proc iml;
  use Have;
  read all var _NUM_ into M[colname=varNames];
  close Have;

  evens = do(2, ncol(M), 2); %* Is there a way to intelligently tell Sas to look for their ID first and then look for its weight and height and then do multiplication of weight and height?. If the data is missing, then the output will be "." ;
  odds = evens + 1;
  mult = M[ , evens] # M[ , odds];
  mult =M[, 1] || mult;
  prefix = substr(varNames[,odds], 1, 7);
  names = prefix + "_hw";
  bnames= varNames[1]|| names;
  create MultOut from Mult[colname= bnames];
  append from Mult;
  close MultOut;

Super User
Posts: 19,086

Re: Columns multiplication with their corresponding variable names II


CheerfulChu wrote:

Dear Sir,

 


Assuming this board is all male would be very incorrect....

Super User
Posts: 9,867

Re: Columns multiplication with their corresponding variable names II

No. You have to rebuild your table .

 

data Have;
input Date X_height X_weight X_pressure Y_weight Y_height Y_pressure Z_height Z_pressure;
datalines;
      1990   100       50     10         40     200       11           80      33
      1991    80       40     20         30     300       22           70      44
;
run;
proc sql;
create table temp as
 select scan(name,1,'_') as name1,scan(name,-1,'_') as name2
  from dictionary.columns
   where libname='WORK' and memname='HAVE' and upcase(name) ne 'DATE';
   
select catx('_',name1,name2) into : vnames separated by ' '
 from (select distinct name1 from temp),
      (select distinct name2 from temp) 
  where upcase(name2) in ('HEIGHT' 'WEIGHT')
   order by name1,name2; 

quit;

data want;
 retain &vnames .;
 set have;
 keep date &vnames ;
run;

proc iml;
use want(drop=Date);
read all var _NUM_ into M[colname=varNames];
close want;

odds = do(1, ncol(M), 2);               /* odd columns */
evens = odds + 1;                       /* even columns */
mult = M[ , evens] # M[ , odds];        /* multiply adjacent values */

prefix = substr(varNames[,odds], 1, 7); /* extract 7-character prefix */
names = prefix + "_hw";                 /* append comon suffix */
create MultOut from Mult[colname=names];/* output to data set */
append from Mult;
close MultOut;
quit;
Ask a Question
Discussion stats
  • 2 replies
  • 291 views
  • 2 likes
  • 3 in conversation