topic Re: PUT FUNCTION OPTIMIZATION in SAS Programming

PUT FUNCTION OPTIMIZATION

robertrao — Mon, 01 Feb 2016 17:30:43 GMT

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.

Re: PUT FUNCTION OPTIMIZATION

Reeza — Mon, 01 Feb 2016 17:54:02 GMT

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?

Re: PUT FUNCTION OPTIMIZATION

robertrao — Mon, 01 Feb 2016 19:18:45 GMT

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.

Re: PUT FUNCTION OPTIMIZATION

ballardw — Mon, 01 Feb 2016 20:08:46 GMT

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.

Re: PUT FUNCTION OPTIMIZATION

Reeza — Mon, 01 Feb 2016 21:12:50 GMT

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

Re: PUT FUNCTION OPTIMIZATION

robertrao — Mon, 01 Feb 2016 22:11:35 GMT

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;

Re: PUT FUNCTION OPTIMIZATION

Reeza — Mon, 01 Feb 2016 22:19:36 GMT

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.

Re: PUT FUNCTION OPTIMIZATION

FreelanceReinh — Mon, 01 Feb 2016 22:21:43 GMT

@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."