DATA Step, Macro, Functions and more

SAS Complex Logic to calculate Volume Benchmark

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

SAS Complex Logic to calculate Volume Benchmark

Hello Everyone

 

ObsTIMEPERIODRegionTerr_NamePRODMETRICVOL_BENCH
101-MONTHWATMKTA1.25
201-MONTHWAT02A 
301-MONTHWAT03A 
401-MONTHWAT05A 
501-MONTHWATMKTB0
601-MONTHWAT01B 
701-MONTHWAT02B 
801-MONTHWAT03B 
901-MONTHWBIMKTC1
1001-MONTHWBI02C 
1101-MONTHWBI03C 
1201-MONTHWBI04C 
1301-MONTHWBI05C 
1403-MONTHWATMKTA0.2917
1503-MONTHWAT02A 
1603-MONTHWAT03A 
1703-MONTHWAT05A 
1803-MONTHWATMKTB0.31
1903-MONTHWAT01B 
2003-MONTHWAT02B 
2103-MONTHWAT03B 
2203-MONTHWBIMKTC0.0714
2303-MONTHWBI02C 
2403-MONTHWBI03C 
2503-MONTHWBI04C 
2603-MONTHWBI05C 


The data is unique on 

TIMEPERIODRegionTerr_NamePRODMETRIC

 

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
PROC Star
Posts: 169

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

View solution in original post


All Replies
Solution
3 weeks ago
PROC Star
Posts: 169

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

Respected Advisor
Posts: 3,889

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

 

 

Respected Advisor
Posts: 3,889

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.

New Contributor
Posts: 3

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: 10,500

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.

New Contributor
Posts: 3

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: 10,500

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

Respected Advisor
Posts: 3,889

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.

New Contributor
Posts: 3

Re: SAS Complex Logic to calculate Volume Benchmark

@Patrick Yes this works. Thanks.

 

 

☑ This topic is SOLVED.

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

Discussion stats
  • 12 replies
  • 247 views
  • 3 likes
  • 5 in conversation