topic Re: Code improvement SELECT often same source in SAS Programming

Code improvement SELECT often same source

SaschaD — Tue, 08 Oct 2019 11:30:02 GMT

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

Re: Code improvement SELECT often same source

Shmuel — Tue, 08 Oct 2019 11:38:04 GMT

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.

Re: Code improvement SELECT often same source

Kurt_Bremser — Tue, 08 Oct 2019 11:50:58 GMT

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

Re: Code improvement SELECT often same source

Patrick — Tue, 08 Oct 2019 11:58:17 GMT

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.

Re: Code improvement SELECT often same source

SaschaD — Thu, 10 Oct 2019 07:28:39 GMT

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

Re: Code improvement SELECT often same source

Kurt_Bremser — Thu, 10 Oct 2019 07:39:52 GMT

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.