BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
CHAN07
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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

12 REPLIES 12
novinosrin
Tourmaline | Level 20

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
Calcite | Level 5

hello,

 

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

 

Thanks

Chandan Mishra

Patrick
Opal | Level 21

@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
Calcite | Level 5

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
Calcite | Level 5

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
Opal | Level 21

@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
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

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
Super User

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
Opal | Level 21

@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
Obsidian | Level 7

@Patrick Yes this works. Thanks.

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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