need easy way to add 3 totals to the dataset

[dyan]

Hello,  I would like to add

varname='TotalDaidzeinEquivalents'; value=daidzein ( value)+254.24/416.38*daidzin (value);

varname='TotalGenisteinEquivalents'; value=genistein ( value)++270.24/432.38*genistin ( value)+;

varname='TotalGlyciteinEquivalents'; value=glycitein ( value)++284.46/446.41*glycitin ( value)+;

to the dataset ( simple sample dataset list below, my real data has bunch of other values and samples). I will keep sampleid and other variable same as its source ( daidzin,  genistin , glycitin)

what I did was :select varname, transpose, calculate to get totals, get the variable name of the dataset to 3 totals and merge it back.  I am just wondering if there is a easy way to do this.

data have;

input SampleID varname$ value1 value2 value3;

datalines;

1     iron        0.7 1 0.05

1     copper      0.5 0 0.05

1     glycitin    532 1 200

1     glycitein   4.8 1 2

1     daidzein    5.76 1 2

1     daidzin     1200 1 200

1     genistein   4.8 1 2

1     genistin    2750 0 200

2     iron        1.2 0  0.05

2     copper      0.96 0  0.05

2     glycitin    478 0 200

2     glycitein   6.7 0 2

2     daidzein    7.5 0 2

2     daidzin     960 0 200

2     genistein   6 0 2

2     genistin    3000 0 200

;

run;

[ArtC]

Is the data set used to generate a report?  if so you could probably create the totals using REPORT in the same step as the report is generated.

[dyan]

no, it is for further mixed model data analysis. in fact, the dataset also has treatment, site and block information, since they are same as its source ( daidzin,  genistin , glycitin) in the same sample id. I did not put them into the simple sample dataset.

[dyan]

no, it is not for report. it is for further mixed model analysis. the data also have treatment, locaiton and block information, but I did not add them into the sample dataset.

[Patrick]

Not really sure how the result data set needs to look like for you (may be you give us an example?). Hope below code is anyway of some use for you.

data have;
  infile datalines truncover;
  input SampleID varname:$10. value1 value2 value3;
  datalines;
1     iron        0.7 1 0.05
1     copper      0.5 0 0.05
1     glycitin    532 1 200
1     glycitein   4.8 1 2
1     daidzein    5.76 1 2
1     daidzin     1200 1 200
1     genistein   4.8 1 2
1     genistin    2750 0 200
2     iron        1.2 0  0.05
2     copper      0.96 0  0.05
2     glycitin    478 0 200
2     glycitein   6.7 0 2
2     daidzein    7.5 0 2
2     daidzin     960 0 200
2     genistein   6 0 2
2     genistin    3000 0 200
;
run;

data Vlong(keep=SampleID Vname Value) /view=Vlong;
  set have;
  length Vname $12;
  array values {*} value1 value2 value3;
  do _i=1 to dim(values);
    Vname=cats(varname,'_',_i);
    value=values(_i);
    output;
  end;
run;

proc transpose data=Vlong out=wide;
  by SampleID;
  var value;
  id Vname;
run;

data want;
  set wide;
  TotalDaidzeinEquivalents  =daidzein_1  +254.24/416.38*daidzin_1;
  TotalGenisteinEquivalents =genistein_1 +270.24/432.38*genistin_1;
  TotalGlyciteinEquivalents =glycitein_1 +284.46/446.41*glycitin_1;
run;

[Tom]

The values of VARNAME are longer than the default length for character variables, so I updated the data step to set the length.

Basically this program will flip the data . Calculate the new variables and then flip it back.

data have;

  length sampleid 8 varname $32 value1-value3 8 ;

  input SampleID varname$ value1 value2 value3;

datalines;

1 iron 0.7 1 0.05

1 copper 0.5 0 0.05

1 glycitin 532 1 200

1 glycitein 4.8 1 2

1 daidzein 5.76 1 2

1 daidzin 1200 1 200

1 genistein 4.8 1 2

1 genistin 2750 0 200

2 iron 1.2 0 0.05

2 copper 0.96 0 0.05

2 glycitin 478 0 200

2 glycitein 6.7 0 2

2 daidzein 7.5 0 2

2 daidzin 960 0 200

2 genistein 6 0 2

2 genistin 3000 0 200

run;

proc transpose data=have out=vertical;

  by sampleid ;

  id varname;

  var value1-value3;

run;

data vertical;

  set vertical;

  TotalDaidzeinEquivalents  =daidzein  +254.24/416.38*daidzin;

  TotalGenisteinEquivalents =genistein +270.24/432.38*genistin;

  TotalGlyciteinEquivalents =glycitein +284.46/446.41*glycitin;

run;

proc transpose data=vertical out=want (rename=(_name_=varname)) ;

  by sampleid;

run;

[PGStats]

For a data driven approach :

data have;
input SampleID varname$ value1 value2 value3;
datalines;
1     iron        0.7 1 0.05
1     copper      0.5 0 0.05
1     glycitin    532 1 200
1     glycitein   4.8 1 2
1     daidzein    5.76 1 2
1     daidzin     1200 1 200
1     genistein   4.8 1 2
1     genistin    2750 0 200
2     iron        1.2 0  0.05
2     copper      0.96 0  0.05
2     glycitin    478 0 200
2     glycitein   6.7 0 2
2     daidzein    7.5 0 2
2     daidzin     960 0 200
2     genistein   6 0 2
2     genistin    3000 0 200
;
 
data tr;
input varname :$32. var1 $ t1 t2 var2 $;
datalines;
TotalDaidzeinEquivalents daidzein 254.24 416.38 daidzin
TotalGenisteinEquivalents genistein 270.24 432.38 genistin
TotalGlyciteinEquivalents glycitein 284.46 446.41 glycitin
;

proc sql;
create table want as
(select h1.sampleID, tr.varname,
h1.value1 + tr.t1/tr.t2*h2.value1 as value1,
h1.value2 + tr.t1/tr.t2*h2.value2 as value2,
h1.value3 + tr.t1/tr.t2*h2.value3 as value3
from tr inner join have as h1 on tr.var1=h1.varname inner join
have as h2 on tr.var2=h2.varname and h1.sampleID=h2.sampleID)
union all
(select SampleID, varname, value1, value2, value3 from have)
order by sampleID, varname;

drop table tr;

select * from want;

quit;

PG