DATA Step, Macro, Functions and more

Multiply a column by a single value from another dataset

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Multiply a column by a single value from another dataset

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 CONSUMPTIONBRANDS        
Idfolder (participants)Sprite1Sprite2Sprite99Sprit100SchweppeSchwepp2Schwepp3Schwepp4Schwepp5
ISP1002245478662
ISP10184623847864
ISP1027581155225
ISP1033875538217
ISP10341544443365
ISP1065948850115
ISP106878454415004

 

 

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)
 AspartameAcesulfame-KCyclamateSaccharineSucralose
Sprite100000
Sprite200000
Sprite999,314,30,200
Sprit1001,93,40,200
Schweppe7,28,8006,9
Schwepp2013,40010,0
Schwepp314,118,9000
Schwepp43,84,80,23,80
Schwepp5014,80,20

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.

table A : food consumption

tableau concentration limo entouré.JPG

 

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

Thanks and have a nice day,


Accepted Solutions
Solution
‎07-04-2016 09:13 AM
Super User
Super User
Posts: 7,070

Re: Multiply a column by a single value from another dataset

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;

Capture.PNG

View solution in original post


All Replies
Contributor
Posts: 41

Re: Multiply a column by a single value from another dataset

[ Edited ]

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) ?

Occasional Contributor
Posts: 7

Re: Multiply a column by a single value from another dataset

Posted in reply to JohnHoughton

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

Contributor
Posts: 41

Re: Multiply a column by a single value from another dataset

[ Edited ]

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;

Solution
‎07-04-2016 09:13 AM
Super User
Super User
Posts: 7,070

Re: Multiply a column by a single value from another dataset

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;

Capture.PNG

Occasional Contributor
Posts: 7

Re: Multiply a column by a single value from another dataset

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.

Super User
Super User
Posts: 7,070

Re: Multiply a column by a single value from another dataset

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.

Super User
Posts: 10,041

Re: Multiply a column by a single value from another dataset


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;

Occasional Contributor
Posts: 7

Re: Multiply a column by a single value from another dataset

Thanks all for your help !

 

It seems to work now.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 370 views
  • 0 likes
  • 4 in conversation