Multiply a column by a single value from another dataset

[Foody]

Hello,

I need any help to try to solver this problem... I want to calculate a sweetener intake = food consumption x sweeteners concentration in food

I have a first data base (table A) with the food consumption of 2000 participants (IDFolder). Each column refer to a food product/brand. And in each column, the values are the food consumption.

FOOD CONSUMPTION	BRANDS
Idfolder (participants)	Sprite1	Sprite2	Sprite99	Sprit100	Schweppe	Schwepp2	Schwepp3	Schwepp4	Schwepp5
ISP1002	2	4	5	4	7	8	6	6	2
ISP1018	4	6	2	3	8	4	7	86	4
ISP1027	5	8	1	1	5	5	2	2	5
ISP1033	8	7	5	5	3	8	2	1	7
ISP1034	1	5	4	4	4	4	3	3	65
ISP1065	9	4	8	8	5	0	1	1	5
ISP1068	7	8	4	5	441	5	0	0	4

In a second data table (table B), I have the concentration in sweeteners (aspartame, acésulfame-K...) for the food products.

	Concentration (mg/ 100 ml)
	Aspartame	Acesulfame-K	Cyclamate	Saccharine	Sucralose
Sprite1	0	0	0	0	0
Sprite2	0	0	0	0	0
Sprite99	9,3	14,3	0,2	0	0
Sprit100	1,9	3,4	0,2	0	0
Schweppe	7,2	8,8	0	0	6,9
Schwepp2	0	13,4	0	0	10,0
Schwepp3	14,1	18,9	0	0	0
Schwepp4	3,8	4,8	0,2	3,8	0
Schwepp5	0	14,8	0,2	0	7,2

I would like to multiply for each food, the food consumption column (1 full column in table A) by the sweeteners concentration in this food (a single case in table B). I don't know how to do and if it is possible... or maybe, i should arrange or modify the dataset B.

if someone could help me, i would be very grateful !

Thanks and have a nice day,

[JohnHoughton]

To help understand the aim, is the final result for id ISP1002 in the Sprite99 column expected be 5*(9.3 + 14.3 + 0.2) =119 (i.e based on the sum of all concentration of the sweeteners) ?

[Foody]

Sorry, i forgot to precise the calculations would be done by Sweeteners. This is an example just for the aspartame :

- The calculation for the Aspartame intake

Idfolder (participants)	Sprite1	Sprite2	Sprite99	Sprit100	Schweppe	Schwepp2	Schwepp3
ISP1002	2 * 0	4 * 0	5 * 9.3	4 * 1.9	7 * 7.2	8 * 0	6 * 14.1
ISP1018	4 * 0	6 * 0	2 * 9.3	3 * 1.9	8 * 7.2	4 * 0	7* 14.1
ISP1027	5 * 0	8 * 0	1 * 9.3	1 *1.9	5 * 7.2	5* 0	2* 14.1
ISP1033	8 * 0	7 * 0	5  * 9.3	5 * 1.9	3 * 7.2	8* 0	2* 14.1
ISP1034	1 * 0	5 * 0	4 *9.3	4 *1.9	4 *7.2	4* 0	3* 14.1
	Concentration [aspartame]=0	Concentration [aspartame]=0	Concentration [aspartame]=9.3	Concentration [aspartame]=1.9	Concentration [aspartame]=7.2	Concentration [aspartame]=0	Concentration [aspartame]=14.1

	Concentration (mg/ 100 ml)
	Aspartame
Sprite1	0
Sprite2	0
Sprite99	9,3
Sprit100	1,9
Schweppe	7,2
Schwepp2	0
Schwepp3	14,1
Schwepp4	3,8
Schwepp5	0

Then, i will do the same for the Acesulfame-K intake, Cyclamate intake and so on, for each food products.

Sorry it isn't easy to understand..

Thanks

[JohnHoughton]

Hi 

this will work , using transpose & proc sql to merge.

data have1;
input
Idfolder $ Sprite1 Sprite2 Sprite99 Sprit100 Schweppe Schwepp2 Schwepp3 Schwepp4 Schwepp5;
datalines;
ISP1002 2 4 5 4 7 8 6 6 2
ISP1018 4 6 2 3 8 4 7 86 4
ISP1027 5 8 1 1 5 5 2 2 5
ISP1033 8 7 5 5 3 8 2 1 7
ISP1034 1 5 4 4 4 4 3 3 65
ISP1065 9 4 8 8 5 0 1 1 5
ISP1068 7 8 4 5 441 5 0 0 4
;
run;
data have2;
input food $ Aspartame Acesulfame Cyclamate Saccharine Sucralose;
datalines;
Sprite1 0 0 0 0 0
Sprite2 0 0 0 0 0
Sprite99 9.3 14.3 0.2 0 0
Sprit100 1.9 3.4 0.2 0 0
Schweppe 7.2 8.8 0 0 6.9
Schwepp2 0 13.4 0 0 10.0
Schwepp3 14.1 18.9 0 0 0
Schwepp4 3.8 4.8 0.2 3.8 0
Schwepp5 0 14.8 0.2 0 7.2
;
run;

proc transpose data=have1 out=temp1 (rename=(col1=amount)) name=food ;

by idfolder;

run;
proc sort data=have2;

by food;

run;
proc transpose data=have2 out=temp2 (rename=(col1=conc)) name=sweetener;

by food;

run;
proc sql ;
create table temp3 as
select temp1.food,idfolder,sweetener,(conc*amount) as total
from temp1,temp2
where temp1.food=temp2.food
order by sweetener,idfolder;
proc transpose data=temp3 out=want;

by sweetener idfolder;

var total;

id food;

run;

[Tom · Accepted solution]

You want to use PROC SCORE for this. But you will need to transpose your ingredients list.

First let's create you list of foods that the participants consumed.

data food_list;
  input id :$10. Sprite1 Sprite2 Sprite99 Sprit100 Schweppe Schwepp2 Schwepp3 Schwepp4 Schwepp5 ;
cards;
ISP1002 2 4 5 4 7 8 6 6 2
ISP1018 4 6 2 3 8 4 7 86 4
ISP1027 5 8 1 1 5 5 2 2 5
ISP1033 8 7 5 5 3 8 2 1 7
ISP1034 1 5 4 4 4 4 3 3 65
ISP1065 9 4 8 8 5 0 1 1 5
ISP1068 7 8 4 5 441 5 0 0 4
;

Now let's read in your ingredients list as is (making sure to use valid variable names).

data ingredients_transposed;
  input product :$10.  Aspartame Acesulfame_K Cyclamate Saccharine Sucralose ;
cards;
Sprite1 0 0 0 0 0
Sprite2 0 0 0 0 0
Sprite99 9.3 14.3 0.2 0 0
Sprit100 1.9 3.4 0.2 0 0
Schweppe 7.2 8.8 0 0 6.9
Schwepp2 0 13.4 0 0 10.0
Schwepp3 14.1 18.9 0 0 0
Schwepp4 3.8 4.8 0.2 3.8 0
Schwepp5 0 14.8 0.2 0 7.2
;

First we need to transpose it so that the products become columns to match the FOOD_LIST table.

proc transpose data=ingredients_transposed out=ingredients;
  id product ;
run;

Then we need to add the variable _TYPE_ so we can use it as a scoring dataset for PROC SCORE.

data score ;
  _TYPE_='SCORE';
  set ingredients ;
run;

Now we are ready to call PROC SCORE and calculate how much of each ingredient each participant consumed.

proc score data=food_list score=score out=want ;
 var Sprite1 Sprite2 Sprite99 Sprit100 Schweppe Schwepp2 Schwepp3 Schwepp4 Schwepp5 ;
 id id ;
run;

[Foody]

Thank you Tom and JohnHoughton !

I tested both your programs and it works. I have a last question..

I have a big Excel file with more than 500 variables for the food brand.

Is it possible to copy-paste the whole Excel file under the instruction Cards/Datalines ?

I have a table called 'Limonade', is it possible to replace :

data food_list;
  input id :$10. Sprite1 Sprite2 Sprite99 Sprit100 Schweppe Schwepp2 Schwepp3 Schwepp4 Schwepp5 ;
cards;
ISP1002 2 4 5 4 7 8 6 6 2
ISP1018 4 6 2 3 8 4 7 86 4
ISP1027 5 8 1 1 5 5 2 2 5
ISP1033 8 7 5 5 3 8 2 1 7
ISP1034 1 5 4 4 4 4 3 3 65
ISP1065 9 4 8 8 5 0 1 1 5
ISP1068 7 8 4 5 441 5 0 0 4

By :

data food_list;
set limo;  /* Limo is the consumption table with Tonic/soda products */
run;

Sorry if my question seem to be naive or stupid, I am a beginner in this software.

Anyways : a big thanks for your help.

[Tom]

Turn you EXCEL file into a nice rectangular structure with the first row having the variable names and you can read it directly with SAS.

You can try using the XLSX libname option first. That will treat each tab in the Excel workbook as a separate dataset.

libname src xlsx 'myfile.xlsx';
proc copy inlib=src out=work;
run;

If that doesn't work then you might try using PROC IMPORT or even the Import Wizard.

[Ksharp]

It is absolutely a single MATRIX Multiply .



data have1;
input
Idfolder $ Sprite1 Sprite2 Sprite99 Sprit100 Schweppe Schwepp2 Schwepp3 Schwepp4 Schwepp5;
datalines;
ISP1002 2 4 5 4 7 8 6 6 2
ISP1018 4 6 2 3 8 4 7 86 4
ISP1027 5 8 1 1 5 5 2 2 5
ISP1033 8 7 5 5 3 8 2 1 7
ISP1034 1 5 4 4 4 4 3 3 65
ISP1065 9 4 8 8 5 0 1 1 5
ISP1068 7 8 4 5 441 5 0 0 4
;
run;
data have2;
input food $ Aspartame Acesulfame Cyclamate Saccharine Sucralose;
datalines;
Sprite1 0 0 0 0 0
Sprite2 0 0 0 0 0
Sprite99 9.3 14.3 0.2 0 0
Sprit100 1.9 3.4 0.2 0 0
Schweppe 7.2 8.8 0 0 6.9
Schwepp2 0 13.4 0 0 10.0
Schwepp3 14.1 18.9 0 0 0
Schwepp4 3.8 4.8 0.2 3.8 0
Schwepp5 0 14.8 0.2 0 7.2
;
run;
proc iml;
use have1;
read all var _num_ into x[c=vnames r=Idfolder];
close;
use have2;
read all var _num_ into y;
close;

z=x*y;

create want from z[r=Idfolder c=vnames];
append from z[r=Idfolder];
close;
quit;

[Foody]

Thanks all for your help !

It seems to work now.