SAS Complex Logic to calculate Volume Benchmark

[CHAN07]

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

[novinosrin · Accepted solution]

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

[CHAN07]

hello,

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

Thanks

Chandan Mishra

[Patrick]

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

[CHAN07]

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

[CHAN07]

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

[Patrick]

@CHAN07

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

[chandan_mishra]

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

[ballardw]

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.

[chandan_mishra]

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

[ballardw]

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

[Patrick]

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

[chandan_mishra]

@Patrick Yes this works. Thanks.