BookmarkSubscribeRSS Feed
SaschaD
Obsidian | Level 7

Hello,

 

my code below works fine so far, but it takes so much time.

It is possible to write this code more comfortable to speed up?

All lines have the same source.

 

UPDATE GRV_Export_Dummy D
		SET
			'Supervisor'n = (SELECT 'Supervisor__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'Account'n = (SELECT 'Account__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'Street'n = (SELECT 'Street__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'Country'n = (SELECT 'Country__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'Postcode'n = (SELECT 'Postcode__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'City'n = (SELECT 'City__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'Contract_start'n = (SELECT 'Contract_start__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'Contract_form'n = (SELECT 'Contract_form__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'Contract_level'n = (SELECT 'Contract_level__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR)
		WHERE D.Contract IN (SELECT V.VTGONR FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR);

 

Thanks,

Sascha

 

5 REPLIES 5
Shmuel
Garnet | Level 18

What is the full code of this updating step ?

May be it can be rewritten using different procedure ?

 

It seems to me that the bottle neck is I/O, network and system overhead, connecting to server.

Describe the environment you work on.

 

Kurt_Bremser
Super User

@SaschaD wrote:

Hello,

 

my code below works fine so far, but it takes so much time.

It is possible to write this code more comfortable to speed up?

All lines have the same source.

 

UPDATE GRV_Export_Dummy D
		SET
			'Supervisor'n = (SELECT 'Supervisor__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'Account'n = (SELECT 'Account__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'Street'n = (SELECT 'Street__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'Country'n = (SELECT 'Country__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'Postcode'n = (SELECT 'Postcode__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'City'n = (SELECT 'City__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'Contract_start'n = (SELECT 'Contract_start__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'Contract_form'n = (SELECT 'Contract_form__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR),
			'Contract_level'n = (SELECT 'Contract_level__c'n FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR)
		WHERE D.Contract IN (SELECT V.VTGONR FROM SERVER.VIEW_TABLE V WHERE D.Contract = V.VTGONR);

 

Thanks,

Sascha

 


Use a data step hash object to load the secondary table:

data GRV_Export_Dummy_new;
set GRV_Export_Dummy;
if 0 then set SERVER.VIEW_TABLE (drop=VTGONR); /* sets variable attributes */
if _n_ = 1
then do;
  declare hash lookup(dataset:"SERVER.VIEW_TABLE (rename=(VTGONR=Contract))");
  rc = lookup.definekey("Contract");
  rc = lookup.definedata(
    "Supervisor__c","Account__c","Street__c","Postcode__c","City__c",
    "Contract_start__c","Contract_form__c","Contract_level__c"
  );
  rc = lookup.definedone();
end;
if lookup.find() = 0
then do;
  Supervisor = Supervisor__c;
  Account = Account__c;
  Street = Street__c;
  Country = Country__c;
  Postcode = Postcode__c;
  City = City__c;
  Contract_start = Contract_start__c;
  Contract_form = Contract_form__c;
  Contract_level = Contract_level__c;
end;
drop
  Supervisor__c
  Account__c
  Street__c
  Country__c
  Postcode__c
  City__c
  Contract_start__c
  Contract_form__c
  Contract_level__c
  rc
;
run;
Patrick
Opal | Level 21

Isn't that just a case of "update table with values from another table"? If so then SQL code could look like below:

proc sql;
  UPDATE GRV_Export_Dummy D
    SET
      d.Supervisor = v.Supervisor__c,
      d.Account = v.Account__c,
      ....
    from GRV_Export_Dummy D, SERVER.VIEW_TABLE V
    WHERE D.Contract = V.VTGONR
  ;
quit;

IF table SERVER.VIEW_TABLE is big and table GRV_Export_Dummy is not on the same server then a multi step approach would likely perform better. ....but you would need to tell us a bit more what you're dealing with - volumes included.

SaschaD
Obsidian | Level 7

Hi Patrick,

 

You are right, its just an update from another table.

 

SERVER.VIEW_TABLE has 10.000 lines / DUMMY 6.000 lines

Both tables on the the same Server.

 

 I tried the Code which you have posted, but I got the error below:

 

141        proc sql;
142          UPDATE GRV_Export_Dummy D
143            SET
144              D.Supervisor= v.Supervisor__c
                  _
                  73
                  76
ERROR 73-322: Expecting an =.

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

CODE:

proc sql;
  UPDATE GRV_Export_Dummy D
    SET
      D.Supervisor= v.Supervisor__c
    from GRV_Export_Dummy D, SERVER.VIEW_TABLE V
    WHERE D.Contract= V.VTGONR
  ;
quit;

Regards,

Sascha

Kurt_Bremser
Super User

SQL is not really good at this, because it syntactically needs a sub-select for every variable.

Since your datasets are really TINY, the hash approach should work, barring any syntax error caused by unavailable example data.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 5 replies
  • 608 views
  • 3 likes
  • 4 in conversation