## SAS Complex Logic to calculate Volume Benchmark

Solved
Occasional Contributor
Posts: 12

# SAS Complex Logic to calculate Volume Benchmark

Hello Everyone

 Obs TIMEPERIOD Region Terr_Name PROD METRIC VOL_BENCH 1 01-MONTH W AT MKT A 1.25 2 01-MONTH W AT 02 A 3 01-MONTH W AT 03 A 4 01-MONTH W AT 05 A 5 01-MONTH W AT MKT B 0 6 01-MONTH W AT 01 B 7 01-MONTH W AT 02 B 8 01-MONTH W AT 03 B 9 01-MONTH W BI MKT C 1 10 01-MONTH W BI 02 C 11 01-MONTH W BI 03 C 12 01-MONTH W BI 04 C 13 01-MONTH W BI 05 C 14 03-MONTH W AT MKT A 0.2917 15 03-MONTH W AT 02 A 16 03-MONTH W AT 03 A 17 03-MONTH W AT 05 A 18 03-MONTH W AT MKT B 0.31 19 03-MONTH W AT 01 B 20 03-MONTH W AT 02 B 21 03-MONTH W AT 03 B 22 03-MONTH W BI MKT C 0.0714 23 03-MONTH W BI 02 C 24 03-MONTH W BI 03 C 25 03-MONTH W BI 04 C 26 03-MONTH W BI 05 C

The data is unique on

 TIMEPERIOD Region Terr_Name PROD METRIC

Now, I have the Vol Benchmark for market. I just need to paste it for all the rows unique by given values. Like for example for obs 2,3,4 the value needs to what is obs 1 for market i.e. 1.25. Similarly, for obs 23, 24, 25 and 26 the value needs to be 0.0714. How to use SAS to assign these values? Basically, the rows unique at given level should take the vol_bench of the market.

Thanks

Chandan Mishra

Accepted Solutions
Solution
‎08-01-2017 04:42 PM
PROC Star
Posts: 1,769

## Re: SAS Complex Logic to calculate Volume Benchmark

data want;
set have;
retain temp;
if prod="MKT" then temp=vol_bench;
else vol_bench=temp;
drop temp;
run;

I don't know what you meant by complex logic. Please pardon me if I don't understand you

All Replies
Solution
‎08-01-2017 04:42 PM
PROC Star
Posts: 1,769

## Re: SAS Complex Logic to calculate Volume Benchmark

data want;
set have;
retain temp;
if prod="MKT" then temp=vol_bench;
else vol_bench=temp;
drop temp;
run;

I don't know what you meant by complex logic. Please pardon me if I don't understand you

Occasional Contributor
Posts: 12

## Re: SAS Complex Logic to calculate Volume Benchmark

hello,

No, You understood absolutely correctly. I thought this was complex. Pardon me as I am very new to SAS.

Thanks

Chandan Mishra

Posts: 4,736

## Re: SAS Complex Logic to calculate Volume Benchmark

@CHAN07

Please provide sample data via a working SAS data step so we can test code we propose without first having to spend time writing SAS data step code to convert posted data into a SAS data set.

Below untested code should do what you're after.

``````proc sql;
update table have as M
set
m.vol_bench=
(
select i.vol_bench
from have as I
where
i.prod='MKT'
and i.timeperiod=m.timeperiod
and i.region=m.region
and i.terr_name=m.terr_name
and i.metric=m.metric
)
where m.prod ne 'MKT'
;
quit;``````
Occasional Contributor
Posts: 12

## Re: SAS Complex Logic to calculate Volume Benchmark

Hello

Yes I totally understand. Only this time I was not sure how to even proceed. Thanks for sharing your code.

I will make sure I post my SAS code also along with the sample dataset.

Thanks

Chandan Mishra

Occasional Contributor
Posts: 12

## Re: SAS Complex Logic to calculate Volume Benchmark

Hello @Patrick

This is the error message which I am getting when I am using the code. Can you please double check it.

56 PROC SQL;
57 UPDATE TABLE HAVE AS M
--
79
76
ERROR 79-322: Expecting a SET.

ERROR 76-322: Syntax error, statement will be ignored.

58 SET
59 M.VOL_BENCH=
60 (SELECT I.VOL_BENCH
61 FROM HAVE AS I
62 WHERE
63 I.PROD = 'MKT'
64 AND I.TIMEPERIOD = M.TIMEPERIOD
65 AND I.REGION = M.REGION
66 AND I.TERR_NAME = M.TERR_NAME
67 AND I.METRIC = M.METRIC
68 )
69 WHERE M.PROD NE "MKT";
70 QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.02 seconds
cpu time 0.03 seconds

Thanks

Chandan Mishra

Posts: 4,736

## Re: SAS Complex Logic to calculate Volume Benchmark

@CHAN07

Happy to do so if you provide a SAS data step which creates a SAS table with the sample data you've posted.

Occasional Contributor
Posts: 18

## Re: SAS Complex Logic to calculate Volume Benchmark

[ Edited ]

Hello @Patrick

I created this SAS data step to create the sample data.

```DATA HAVE;
INFILE DATALINES DELIMITER = ',';
INPUT OBS TIMEPERIOD \$ REGION \$ TERR \$ PROD \$ METRIC \$ VOL_BENCH;
DATALINES;
1,	1-Month,	W,	AT,	MKT,	X,	1.25
2,	1-Month,	W,	AT,	R,	X,	.
3,	1-Month,	W,	AT,	F,	X,	.
4,	1-Month,	W,	AT,	C,	X,	.
5,	1-Month,	W,	AT,	MKT,	Y,	0.59
6,	1-Month,	W,	AT,	A,	Y,	.
7,	1-Month,	W,	AT,	F,	Y,	.
8,	1-Month,	W,	AT,	R,	Y,	.
9,	1-Month,	W,	BI,	MKT,	Z,	1.29
10,	1-Month,	W,	BI,	A, 	Z,	.
11,	1-Month,	W,	BI,	F,	Z,	.
12,	1-Month,	W,	BI,	R,	Z,	.
13,	1-Month,	W,	BI,	C,	Z,	.
14,	3-Month,	W,	AT,	MKT,	X,	1.58
15,	3-Month,	W,	AT,	R,	X,	.
16,	3-Month,	W,	AT,	F,	X,	.
17,	3-Month,	W,	AT,	C,	X,	.
18,	3-Month,	W,	AT,	MKT,	Y,	1.68
19,	3-Month,	W,	AT,	A,	Y,	.
20,	3-Month,	W,	AT,	F,	Y,	.
21,	3-Month,	W,	AT,	R,	Y,	.
22,	3-Month,	W,	BI,	MKT,	Z,	0.23
23,	3-Month,	W,	BI,	A,	Z,	.
24,	3-Month,	W,	BI,	F,	Z,	.
25,	3-Month,	W,	BI,	R,	Z,	.
26,	3-Month,	W,	BI,	C,	Z,	.

;
RUN;```

Now when I use the code, it gives the following error.

```213  PROC SQL;
214      UPDATE TABLE HAVE
215      SET HAVE.VOL_BENCH=
-
73
76
ERROR 73-322: Expecting an =.

ERROR 76-322: Syntax error, statement will be ignored.

216      (SELECT I.VOL_BENCH
217      FROM HAVE AS I
218      WHERE
219      I.PROD = 'MKT'
220      AND I.TIMEPERIOD = HAVE.TIMEPERIOD
221      AND I.REGION     = HAVE.REGION
222      AND I.TERR_NAME  = HAVE.TERR_NAME
223      AND I.METRIC     = HAVE.METRIC
224      )
225      WHERE HAVE.PROD NE "MKT";
226  QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time           0.01 seconds
cpu time            0.00 seconds

```

Even when I am using the exact code where I use the alias as M, it gives an error. Please, can you fix it.

Thanks

Chandan Mishra

Super User
Posts: 13,498

## Re: SAS Complex Logic to calculate Volume Benchmark

Post code using the {i} menu icon at the top of the message box in this forum to preserve formatting of error messages.

If the underscore characte is under the "as M" then remove it. I don't think update likes an alias there and expects the SET clause immediately following the table name.

Occasional Contributor
Posts: 18

## Re: SAS Complex Logic to calculate Volume Benchmark

[ Edited ]

Hello @ballardw

I have updated the problem statement with a data step. Can you please check it on your end.

```213  PROC SQL;
214      UPDATE TABLE HAVE
215      SET HAVE.VOL_BENCH=
-
73
76
ERROR 73-322: Expecting an =.

ERROR 76-322: Syntax error, statement will be ignored.

216      (SELECT I.VOL_BENCH
217      FROM HAVE AS I
218      WHERE
219      I.PROD = 'MKT'
220      AND I.TIMEPERIOD = HAVE.TIMEPERIOD
221      AND I.REGION     = HAVE.REGION
222      AND I.TERR_NAME  = HAVE.TERR_NAME
223      AND I.METRIC     = HAVE.METRIC
224      )
225      WHERE HAVE.PROD NE "MKT";
226  QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time           0.01 seconds
cpu time            0.00 seconds

```

Thanks

Chandan Mishra

Super User
Posts: 13,498

## Re: SAS Complex Logic to calculate Volume Benchmark

Use

SET VOL_BENCH=   instead of  SET HAVE.VOL_BENCH=

Since UPDATE has specified the data set then the variable on the SET to receive the value must already be in the data set. So using a dataset.variable notation is not needed (or allowed).

Posts: 4,736

## Re: SAS Complex Logic to calculate Volume Benchmark

@chandan_mishra

Below code works with your sample data.

``````data inter;
set have;
run;

proc sql;
update have as M
set
vol_bench=
(
select i.vol_bench
from inter as I
where
i.prod='MKT'
and i.timeperiod=m.timeperiod
and i.region=m.region
and i.terr=m.terr
and i.metric=m.metric
)
where m.prod ne 'MKT'
;
quit;
``````

What I've got wrong in my initial post:

You can't update a SAS table with itself. That's why I had to introduce table inter.

Occasional Contributor
Posts: 18

## Re: SAS Complex Logic to calculate Volume Benchmark

@Patrick Yes this works. Thanks.

☑ This topic is solved.