DATA Step, Macro, Functions and more

PUT FUNCTION OPTIMIZATION

Accepted Solution Solved
Reply
Super Contributor
Posts: 1,041
Accepted Solution

PUT FUNCTION OPTIMIZATION

 

Hello ,

I have the following "NOTE" after trying to apply the PUT function to a dataset.

Can i ignore the NOTE???

 

 

/*Format */

proc sort data=order_proc_quest2(keep=enc_id) out=order_proc_quest2_out nodupkey;

by enc_id;

run;

 

data order_proc_quest2_out_formats;

set order_proc_quest2_out(rename=(enc_id=start));

label="true";

fmtname="csnid";

run;

 

proc format cntlin=order_proc_quest2_out_formats;

run;

 

 

proc sql;

create table inp_encs as

select *

from

pat_enc_hsp(keep=ENC_ID HSP_ACCOUNT_ID) as pat_enc_hsp,

HSP_ACCOUNT (keep=hsp_account_id ) as hsp_acct

where pat_enc_hsp.HSP_ACCOUNT_ID=hsp_acct.hsp_account_id and

put(pat_enc_hsp.enc_id,csnid.) = "true";

quit;

run;

 

 

 

NOTE: Optimization for the PUT function was skipped because the referenced format, CSNID, does

     not have an OTHER= range defined.

NOTE: Table WORK.INP_ENCS created, with 3086 rows and 7 columns.


Accepted Solutions
Solution
‎02-01-2016 05:54 PM
Trusted Advisor
Posts: 1,117

Re: PUT FUNCTION OPTIMIZATION

Posted in reply to robertrao

@robertrao: A quick web search for "Optimization for the put function" yields three papers and SAS Problem Note 48735. The latter has a misleading title, but it states: "The query will generate correct results. You can ignore the note."

 

As far as I see from the abovementioned resources, this note is linked to SAS/ACCESS Interface to some database, which was not clear to me from your post. It has to do with what they call "unPUT optimization," i.e. the conversion of PUT function calls in SQL queries into expressions (e.g. using CASE/WHEN/ELSE) which can be evaluated by the database (thus improving performance). If the note appears, this means that the query "was instead processed by SAS."

 

View solution in original post


All Replies
Super User
Posts: 19,814

Re: PUT FUNCTION OPTIMIZATION

Posted in reply to robertrao

Haven't seen that one before.

 

Have you considered adding the other? How do you want to handle values that don't match anything in your list?

 

 

Super Contributor
Posts: 1,041

Re: PUT FUNCTION OPTIMIZATION

I havent tried the other=option. In fact i dont know how to use it within this logic.

Also i am not interested in the ones that does not fall within that format. I want to pull only the ones which match up with those in the format.

Super User
Posts: 11,343

Re: PUT FUNCTION OPTIMIZATION

Posted in reply to robertrao

A simple modification to your current custom format would be to add

 

OTHER= ' '

 

to the format definition.

 

Then any value other than the ones you have currently defined would get a value of blank. Since you are comparing for equal to 'true' then the additional description would not be 'true' and those would not be selected.

Super Contributor
Posts: 1,041

Re: PUT FUNCTION OPTIMIZATION

Hello,

Thansk for the response. Using any of the three options specified below would give me the same NOTE as OPTIMIZATION ERROR!!!

 

 

/*Format */

proc sort data=order_proc_quest2(keep=enc_id) out=order_proc_quest2_out nodupkey;

by enc_id;

run;

 

data order_proc_quest2_out_formats;

set order_proc_quest2_out(rename=(enc_id=start));

label="true";

fmtname="csnid";

other='O';  /  HLO='O'; /   Other='' ;

run;

 

proc format cntlin=order_proc_quest2_out_formats;

run;

Super User
Posts: 19,814

Re: PUT FUNCTION OPTIMIZATION

Posted in reply to robertrao

Post your full code/log using the HLO option please. 

 

Make sure to follow the example and the fact that it's only created at the end or beginning. 

Super User
Posts: 19,814

Re: PUT FUNCTION OPTIMIZATION

Posted in reply to robertrao

There's an example in the following PDF on how to add Other to your dataset, see the example on page 6. 

 

http://www2.sas.com/proceedings/forum2007/068-2007.pdf

 

Solution
‎02-01-2016 05:54 PM
Trusted Advisor
Posts: 1,117

Re: PUT FUNCTION OPTIMIZATION

Posted in reply to robertrao

@robertrao: A quick web search for "Optimization for the put function" yields three papers and SAS Problem Note 48735. The latter has a misleading title, but it states: "The query will generate correct results. You can ignore the note."

 

As far as I see from the abovementioned resources, this note is linked to SAS/ACCESS Interface to some database, which was not clear to me from your post. It has to do with what they call "unPUT optimization," i.e. the conversion of PUT function calls in SQL queries into expressions (e.g. using CASE/WHEN/ELSE) which can be evaluated by the database (thus improving performance). If the note appears, this means that the query "was instead processed by SAS."

 

☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 272 views
  • 4 likes
  • 4 in conversation