Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- SAS Complex Logic to calculate Volume Benchmark

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

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

3 weeks ago

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

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

3 weeks ago

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

hello,

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

Thanks

Chandan Mishra

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

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;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

2 weeks ago

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

2 weeks ago

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

2 weeks ago - last edited 2 weeks ago

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

2 weeks ago

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

2 weeks ago - last edited 2 weeks ago

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

2 weeks ago

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

2 weeks ago

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*.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

a week ago