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

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

@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

7 REPLIES 7
Reeza
Super User

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?

 

 

robertrao
Quartz | Level 8

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.

ballardw
Super User

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.

robertrao
Quartz | Level 8

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;

Reeza
Super User

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. 

Reeza
Super User

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

 

FreelanceReinh
Jade | Level 19

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

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 7 replies
  • 1639 views
  • 4 likes
  • 4 in conversation