topic Re: Truncated record?? in SAS Procedures

Truncated record??

brophymj — Mon, 18 Aug 2014 09:19:16 GMT

Quick question

I have a small sas program that runs 'proc sql; create table' procedure which runs perfectly when I open the program and run it but when I try to call it using %include call(Reconciliation), I get an error message saying "Truncated Record" and "Syntax Error"

Since it works fine when I open up the program and run it rather than calling it from another program I assume it is something to do with my %Include... code.

Any ideas?

Many thanks

Re: Truncated record??

RW9 — Mon, 18 Aug 2014 09:28:44 GMT

Can you post the code, can check it then? Also, what are you running on , SAS9.3 or EG etc.

Re: Truncated record??

brophymj — Mon, 18 Aug 2014 09:33:02 GMT

SAS 9.2 for Windows

I have attached the program I call called "Reconciliation". I call this from another program using %include call(Reconciliation) where call defined as Filename Call 'location...';.

Re: Truncated record??

RW9 — Mon, 18 Aug 2014 09:47:26 GMT

Ah yes, you have this: "%" in the text. The double quotes around a % indicates that SAS should find the macro variable from the symbol table and replace this, though its not a macro call. Replace the double quotes with single quotes. You also have a special character in: "Difference -I90 VS SAS Program ", and "EP Premium Breakdown " - note the space after it.
Updated:

proc sql;

create table Rec_Summary_MFR

(

REC_SUMMARY_MFR char(200),

EY2012 char(200)

);

insert into Rec_Summary_MFR

set REC_SUMMARY_MFR="Earned Premium - SAS Program",

EY2012= (select PUT(round(sum(EP2012),1),BEST.) from EP_CALC where POLTYR = 'MFR')

set REC_SUMMARY_MFR="Earned Premium - I90",

EY2012= (select PUT(round(sum(enprmw),1),BEST.) from TPERND WHERE POLTYR = 'MFR' AND ernyrw = 2012)

set REC_SUMMARY_MFR="Difference -I90 VS SAS Program",

EY2012= CATT(input(put((((select round(sum(enprmw),1) from TPERND WHERE POLTYR = 'MFR' AND ernyrw = 2012)/(select round(sum(enprmw),1) from TPERND WHERE POLTYR = 'MFR' AND ernyrw = 2012))-1)*100,5.2),best.),'%')

set REC_SUMMARY_MFR="EP Premium Breakdown",

EY2012= ""

set REC_SUMMARY_MFR="Difference 1 - Batch month before expiry date but after 1st month of exposure period",

EY2012= CATT((select PUT(round(sum(DIFF12012),1),BEST.) from EP_CALC WHERE POLTYR = 'MFR')," (",INPUT(PUT(((select round(sum(DIFF12012),1)from EP_CALC WHERE POLTYR = 'MFR')/(select round(sum(EP2012),1)from EP_CALC WHERE POLTYR = 'MFR'))*100,5.1),BEST.),'%)')

set REC_SUMMARY_MFR="Difference 2 - Batch month after expiry date and premium full earned in batch month",

EY2012= CATT((select PUT(round(sum(DIFF22012),1),BEST.) from EP_CALC WHERE POLTYR = 'MFR')," (",INPUT(PUT(((select round(sum(DIFF22012),1)from EP_CALC WHERE POLTYR = 'MFR')/(select round(sum(EP2012),1)from EP_CALC WHERE POLTYR = 'MFR'))*100,5.1),BEST.),'%)')

set REC_SUMMARY_MFR="Difference 3 - Batch month before expiry date and premium full earned in batch month",

EY2012= CATT((select PUT(round(sum(DIFF32012),1),BEST.) from EP_CALC WHERE POLTYR = 'MFR')," (",INPUT(PUT(((select round(sum(DIFF32012),1)from EP_CALC WHERE POLTYR = 'MFR')/(select round(sum(EP2012),1)from EP_CALC WHERE POLTYR = 'MFR'))*100,5.1),BEST.),'%)')

;

quit;

Re: Truncated record??

brophymj — Mon, 18 Aug 2014 10:01:51 GMT

Hi RW9

I made that change but I'm still getting an error message. I have attached a screenshot of the message. I changed the followin in my program - replaced "%" with '%' and put a space after the special character

Re: Truncated record??

RW9 — Mon, 18 Aug 2014 10:08:03 GMT

Yes, you still have special characters in the code. Use the text I posted above, copy that into your SAS window and submit it (mine fails as the dataset are not present, but it should work ok for you). Then copy and paste that over your include file.

Re: Truncated record??

brophymj — Mon, 18 Aug 2014 10:30:59 GMT

I copied your code exactly and I'm still getting the same error message. I've attached a screenshot of the error.

Re: Truncated record??

RW9 — Mon, 18 Aug 2014 10:34:31 GMT

Does it run correctly just in a Enhanced Editor window? (i.e. don't use as include)

Re: Truncated record??

brophymj — Mon, 18 Aug 2014 10:35:31 GMT

Yes

Re: Truncated record??

Patrick — Mon, 18 Aug 2014 10:39:51 GMT

Code working fine as such but throwing a "truncated" error when using in an %include statement. Hmm.... Your code lines are very long (almost 280 characters). What comes to mind is option "S2" SAS(R) 9.3 System Options: Reference, Second Edition

Use below before your %include statement and let us know if this helps.

options s2=max;

Re: Truncated record??

brophymj — Mon, 18 Aug 2014 10:54:43 GMT

When i use the code

options s2=max;
%include call(reconcilaition);

nothing appears to run

Re: Truncated record??

RW9 — Mon, 18 Aug 2014 11:00:02 GMT

Plus 1 to Patrick's suggestion. I am still not sure how it is 'running correctly' in you SAS Editor. I am just running through aligning code and layout and am noticing odd things which should come up as issues:

Row for EY2012=, the second from has no space between it and the preceding (. This happens in a couple of instances. I would suggest some code re-formatting to make it a bit more readable so these kind of things jump out. For instance each sub-query on a new row, indented within functions etc. Will see if I can do an example after lunch.

Re: Truncated record??

Patrick — Mon, 18 Aug 2014 11:10:03 GMT

I observe the same in my environment (SAS9.4M0 under Win7). Don't understand the "why" yet.

Can you try and run your code similar to below and tell us if this works for you (it does for me):

filename call 'C:\test';

options s=0 s2=0;

%include call(test.sas) /source2 ;

Re: Truncated record??

brophymj — Mon, 18 Aug 2014 12:00:57 GMT

It runs but I'm getting the same error message as before.

Re: Truncated record??

brophymj — Mon, 18 Aug 2014 12:05:23 GMT

It does work in editor. I know the formatting is a bit messy but it does definitely work. It's only when I %include call(..) that it falls down

Re: Truncated record??

Tom — Mon, 18 Aug 2014 12:08:11 GMT

So I looked at your program file and you have lines in there that are up to 276 characters long, with TABS. Expanding the tabs makes the line up to 297 characters long.

NOTE: 34 records were read from the infile 'c:\downloads\Code.txt'.

The minimum record length was 0.

The maximum record length was 276.

NOTE: 34 records were written to the file

'c:\downloads\code_expanded.txt'.

The minimum record length was 0.

The maximum record length was 297.

Try letting SAS know that it should expect such long lines by adding a LRECL option to the %INCLUDE statement.

%include 'Code.txt' / lrecl=1000 ;

In the future just put a few more line breaks into your commands so that they will be readable by both humans and SAS.

Re: Truncated record??

RW9 — Mon, 18 Aug 2014 12:25:42 GMT

Just to add onto my previous post, try shrinking each line a bit, having sections of code on different lines, e.g. select and from on different rows, all indented the same. I always try to keep my code no longer the 100 characters, avoids all that scrolling across, also you can have unlimited lines but width is restricted which maybe what you are hitting here. (note the spacing seems different on here so paste into code window to see properly).

proc sql;
create table Rec_Summary_MFR
(
    REC_SUMMARY_MFR char(200),
    EY2012 char(200)
);

insert into Rec_Summary_MFR
set     REC_SUMMARY_MFR="Earned Premium - SAS Program",
          EY2012= (select put(round(sum(EP2012),1),BEST.)
                   from   EP_CALC
                   where POLTYR="MFR")
set     REC_SUMMARY_MFR="Earned Premium - I90",
          EY2012=(select PUT(round(sum(enprmw),1),BEST.)
                  from    TPERND
                  where   POLTYR="MFR"
                      and ERNYRW=2012)
set     REC_SUMMARY_MFR="Difference -I90 VS SAS Program",
          EY2012=(select strip(put((round(sum(BASE.ENPRMW),1) / round(sum(LAST.ENPRMW),1),5.2))||"%"
                  from    (select * from TPERND where POLTYR="MFR" and ERNYRW=2012) BASE
                  left join (select * from TPERND where POLTYR="MFR" and ERNYRW=2011) LAST
                  on      BASE.ERNTRW=LAST.ERNTRW)
....;
quit;

Re: Truncated record??

Patrick — Mon, 18 Aug 2014 12:27:08 GMT

Oh - is that simply lrecl and not s2!!!

Re: Truncated record??

Tom — Mon, 18 Aug 2014 12:39:47 GMT

The LRECL was causing the record to be truncated as it was read into the input buffer. Settings for S2 are applied to the data in the input buffer.

Re: Truncated record??

Patrick — Mon, 18 Aug 2014 13:29:50 GMT

Think we had this discussion already in the past when I got this wrong. Having a "deja-vue" right now....

Seems I've got the analysis right but subscribed the wrong remedy. Hope this will stick with me from now on 😉