Help using Base SAS procedures

Multiply column data to single cells given grouping variables. Multiplication in proc sql/data step.

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

Multiply column data to single cells given grouping variables. Multiplication in proc sql/data step.

Hi,

I have a dataset that contains a variable for year (2010-2013), a variable for unitID (LT0100 to LT2400), a variable "code" ranging from 0-6, and some other variables (a_1-a_8;b_1-b_8;etc...).

At the moment, what I have is all the columns in the dataset below, except for f_7 which I want to create. f_7 should be calculated using a formula like this: " f_5*(mean(f_6) where Cod=0) ". In words, I want to multiply f_5 by the single value of f_6 (only 1 value, where code=0, for each year and unitId).

The code below, and variations of it, is my first attempts. But it fails to either "lock" the multiplication to each single value, or to find values in column f_6.

proc sql;

create table Dataset1 as

  select *, f_5*(mean(f_6)) as f_7

from Dataset

group by unitID ,year

order by year,unitID ,code

;

quit;

UnitIdYearCodea_1a_2a_3f_5f_6f_7
LT0100201002415
LT010020101295913932021216002440,137518332,106
LT01002010245650101427159965570,06129148,0154
LT010020103330177440154020415130,172445416,4547
LT0100201047384528116317458558540,4995361206,379
LT01002010513576203337111254680,01433234,61178
LT010020106145428226749511100660,114878277,4304
LT0300201001684
LT0300201016497061719043269100,15413259,5549
LT0300201021058760972911678240,087232146,8987
LT03002010311564933487387333970,31268526,5531
LT030020104204778641534414442010,372399627,1199
LT030020105257839766021747770,068682115,6605
LT03002010624816654402410260,0048788,214552
LT0400201003104
LT040020101478329374002241110,091693284,6151
LT04002010274550116167652800,039637123,0332
LT0400201037054151339095421810,3283041019,056
LT0400201049019861827507128610,4480471390,738
LT040020105175015376551129650,092319286,5582
LT040020106185470185470
LT0100201101235
LT0100201112959139352233,216002440,137518169,8347
LT0100201124565010156986,59965570,0612975,69315
LT010020113330177444169420415130,172445212,9696
LT0100201147384528127949158558540,499536616,927
LT010020115135762036708,111254680,01433217,70002
LT0100201161454282294244,511100660,114878141,8743
LT0300201102214
LT030020111649706189094,43269100,15413341,2438
LT0300201121058760107020,11678240,087232193,1316
LT0300201131156493383611,87333970,31268692,2735
LT0300201142047786456878,414442010,372399824,4914
LT03002011525783984262,21747770,068682152,0619
LT03002011624816659842410260,00487810,79989
LT0400201102610
LT040020111478329411402241110,091693239,3187
LT04002011274550117783,7652800,039637103,4526
LT040020113705415147299,95421810,328304856,8734
LT0400201149019862010257128610,4480471169,403
LT04002011517501541420,51129650,092319240,9526
LT040020116185470185470

I can´t find any sources on this problem online, and maybe I need to structure the variable f_6 differently but I don´t know exactly how to, in order to get the multiplication done correctly.

Any suggestions on how to solve this problem?

Best regards,

Hank


Accepted Solutions
Solution
‎02-12-2014 11:23 AM
Super User
Posts: 19,869

Re: Multiply column data to single cells given grouping variables. Multiplication in proc sql/data step.

A data step is easier, use retain to hold the multiplier and make sure to set the value when code=0. Then drop the multiplier column at the end, I've left it in for display purposes. This assumes your data is sorted as above.

data want;

set have;

by unitid year;

retain multiplier;

if code=0 then multiplier=f_6;

f_7=f_5*multiplier;

*drop mulitplier;

run;

View solution in original post


All Replies
Solution
‎02-12-2014 11:23 AM
Super User
Posts: 19,869

Re: Multiply column data to single cells given grouping variables. Multiplication in proc sql/data step.

A data step is easier, use retain to hold the multiplier and make sure to set the value when code=0. Then drop the multiplier column at the end, I've left it in for display purposes. This assumes your data is sorted as above.

data want;

set have;

by unitid year;

retain multiplier;

if code=0 then multiplier=f_6;

f_7=f_5*multiplier;

*drop mulitplier;

run;

🔒 This topic is solved and locked.

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

Discussion stats
  • 1 reply
  • 2363 views
  • 1 like
  • 2 in conversation