Your SAS programs, embedded in web apps and elsewhere

getting error in sas SP but not in SAS EG

Reply
Contributor
Posts: 31

getting error in sas SP but not in SAS EG

Dear Team,

please any one hlep on below error.

I am able to run appened  code in SAS EG but   getting error in SAS stroed process.

/*Defining Styles*/

proc template;

define style Styles.newpdf;

parent = Styles.Printer;

STYLE SystemTitle /

FONT_FACE = "Zurich bt,Arial,Helvetica "

FONT_SIZE = 11;

STYLE systemFooter /

FONT_FACE = "Zurich bt,Arial,Helvetica "

FONT_SIZE = 11pt;

STYLE data /

FONT_FACE = "zurich bt,Arial,Helvetica"

FONT_SIZE = 11pt;

STYLE usertext /

FONT_FACE = "zurich bt,Arial,Helvetica"

FONT_SIZE = 11pt;

STYLE Header /

FONT_FACE = "zurich bt,Arial,Helvetica"

FONT_SIZE =11pt;

style Table from Output /

          rules = ALL

          cellpadding = 2pt     /* Reduced from 4pt to 2pt */

          cellspacing = 0.25pt

          borderwidth = 0.75pt;

end;

run;

/*Creating global date macro parameters and modifying date formats as per database */

options nodate nonumber PS=MAX;

/*Creating global date macro parameters and modifying date formats as per database */

%GLOBAL STARTDATE;

%GLOBAL ENDDATE;

options  symbolgen;

%LET STARTDATE =01SEP2011;

%LET ENDDATE = 30SEp2011;

LIBNAME OFSAINT '/regrep/regrep';

/*changing date format to pass oracle procedure*/

data _null_;

LENGTH DATE $ 10.;

DATE2 ="&STARTDATE"D;

DATE3=COMPRESS(PUT(DATE2,YYMMDD10.));

DATE=COMPRESS(DATE3,'-');

call symput ("DATE1",DATE);

run;

%PUT &DATE1;

data _null_;

LENGTH DATE $ 10.;

DATE4 ="&ENDDATE"D;

DATE5=COMPRESS(PUT(DATE4,YYMMDD10.));

DATE=COMPRESS(DATE5,'-');

call symput ("DATE6",DATE);

run;

%put &DATE6;

/*this date for to  show in  Footnote */

data _null_;

length DATE $ 11.;

DATE7="&ENDDATE"D;

DATE=put(DATE7,date11.);

call symput ("SDATE",DATE);

run;

%put &SDATE;

data _null_;

LENGTH DATE $ 20.;

DATE2 ="&STARTDATE"D;

DATE3=trim(left(put(DATE2,WORDDATE18.)));

call symput ("WDATE1",DATE3);

run;

%put &WDATE1;

data _null_;

LENGTH DATE $ 20.;

DATE2 ="&ENDDATE"D;

DATE3=trim(left(put(DATE2,WORDDATE18.)));

call symput ("WDATE2",DATE3);

run;

%put &WDATE2;

data _null_;

length DATE $ 11.;

DATE7="&sysdate"D;

DATE=put(DATE7,ddmmyy10.);

call symput ("datet",DATE);

run;

%put &datet;

/*Password is encoded in regrep devpas text*/

filename pasfile"/regrep/regrep/devpas.txt";

data _null_;

  infile pasfile obs=1 length=l;

  input @;

  input @1 line $varying1024. l;

  call symput('dbpass',substr(line,1,l));

run;

proc sql;

connect to oracle(user=developer password="&dbpass" path='cprdev');

execute (execute CPR147_NPA_LOSS_ASSETS(to_date(&DATE1,'yyyymmdd'),(to_date(&DATE6,'yyyymmdd')))) by oracle;

create table rbi_147 as select * from connection to oracle(select  * from NPA_LOSS_ASSETS_RPDG_REP order by SR_NO,NAME_OF_BORROWER );

disconnect from oracle;

quit;

proc sql;

connect to oracle(user=developer password="&dbpass" path='cprdev');

create table NPA_LOSS_ASSETS as select * from connection to oracle(select  * from NPA_LOSS_ASSETS_RPDG_TRANS_TEM  );

disconnect from oracle;

quit;

proc sql;

connect to oracle(user=developer password="&dbpass" path='cprdev');

create table NOTES as select * from connection to oracle(select  * from CPR147_cfo_notes_final);

disconnect from oracle;

quit;

/*proc sql;*/

/*create table Item1 as select SR_NO,REMARKS from rbi_147;*/

/*quit;*/

/**/

/*data rbi_par;*/

/*set rbi_147 Item1*/

/*;*/

/*run;*/

/**/

/*where RDATE = to_date(&DATE1,'yyyymmdd') */

data _null_;

set NOTES;

call symput ('NOTE1',NOTES1);

call symput ('NOTE2',NOTES2);

call symput ('NOTE3',NOTES3);

call symput ('NOTE4',NOTES4);

run;

data CPR0147_LOSS_ASSETS;

set NPA_LOSS_ASSETS;

run;

/*Exporting the sas dataset */

proc export data=CPR0147_LOSS_ASSETS

outfile="/regrep/regrep/CPR0147_LOSS_ASSETS.txt"

dbms=dlm replace;

delimiter='|';

run;

/*Importing the sas dataset and declearing attribs applying formats and informats*/

data CPR0147_LOSS_ASSETS;

infile "/regrep/regrep/CPR0147_LOSS_ASSETS.txt" firstobs=2

dlm='|' dsd missover;

attrib MOVEMENT length=$120 informat=$120. format=$120. ; ;

attrib START_DATE_VALUES length=8 informat=20. format=20.2 ;

attrib ADDITIONS_DURING length=8 informat=20. format=20.2 ;

attrib DELETIONS_DURING length=8 informat=20. format=20.2 ;

attrib END_DATE_VALUES length=8 informat=20. format=20.2 ;

input MOVEMENT START_DATE_VALUES ADDITIONS_DURING DELETIONS_DURING END_DATE_VALUES;

run;

/*data _null_;*/

/*set RBI_147;*/

/*call symput('REMARKS ',REMARKS);*/

/*if SR_NO='1' then  REMARKS;*/

/*run;*/

/**/

/**/

/*data CPR0147_Remarks;*/

/*set rbi_147;*/

/*run;*/

/**/

/*/*Exporting the sas dataset */*/

/*proc export data=CPR0147_Remarks*/

/*outfile="/regrep/regrep/CPR0147_Remarks.txt"*/

/*dbms=dlm replace;*/

/*delimiter='|';*/

/*run;*/

/**/

/*/*Importing the sas dataset and declearing attribs applying formats and informats*/*/

/*data CPR0147_Remarks;*/

/*infile "/regrep/regrep/CPR0147_Remarks.txt" firstobs=2*/

/*dlm='|' dsd missover;*/

/*attrib SR_NO length=$8 informat=$20. format=$20. ;*/

/*attrib NAME_OF_BORROWER length=$120 informat=$120. format=$120. ;  */

/*attrib OUTSTANDING length=8 informat=20. format=20.2 ;*/

/*attrib REMARKS length=$220 informat=$220. format=$220. ;*/

/*input SR_NO NAME_OF_BORROWER OUTSTANDING REMARKS ;*/

/*run;

/*Counting total number of rows*/

PROC SQL NOPRINT ;

SELECT COUNT(*) INTO : COUNT FROM rbi_147;

QUIT;

data sign;

input sig$ 21.;

datalines;

Authorised Signatory

;

run;

proc sql noprint ;

SELECT  SR_NO,REMARKS

INTO Smiley FrustratedR_NO,:REMARKS

from rbi_147;

quit;

proc sql noprint ;

select count(distinct SR_NO)

into :numrows

from rbi_147;

quit;

%let numrows=&numrows;

%put numrows=&numrows;

/*Declearing a macro */

%MACRO PRINT;

%IF &COUNT = 0  %THEN %DO;

    DATA PRINT1;

    LENGTH MESSAGE $ 100;

    MESSAGE = "NO RECORDS FOUND FOR THE Period &STARTDATE to &ENDDATE";

    RUN;

    PROC REPORT DATA = PRINT1 NOWD;

    RUN;

%end;

    %ELSE %IF &COUNT > 0  %THEN %DO;

/*Code for ICICI Immage in portal*/

DATA IMAGE;

           LENGTH IMAGE $ 1000;

IMAGE = '<img src="http://sasebi92/regrep/icici_logo.jpg"

width = "200" height = "60" > &nbsp &nbsp &nbsp

&nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp RBI-REGULATORY RETURNS

&nbsp &nbsp &nbsp &nbsp   &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp </img>';

       RUN;

       PROC REPORT DATA = IMAGE STYLE(COLUMN) = [BACKGROUNDCOLOR = CX990000 font_size=5 foreground=white font_weight=bold]

                             STYLE(REPORT)=[BACKGROUNDCOLOR = CX990000 font_size=5 foreground=white font_weight=bold] ;

           DEFINE IMAGE / '';

       RUN;

/*Generating report in pdf */

OPTIONS ORIENTATION=PORTRAIT NODATE NONUMBER;

Footnote justify=right  "Generated From RBI-ADF Portal_&DATEt,&systime hrs";

ods pdf file="/sas/sasebi/apache/htdocs/regrep/CPR147-&ENDDATE..pdf" startpage=    no style=styles.newpdf uniform;

ods escapechar='^';

ods pdf text="^S={preimage='/sas/sasebi/apache/htdocs/regrep/icici_bank_logo123.jpg'} ";

ods pdf text="^S={just=Right Font_weight=bold font_size=4  borderwidth=5 CELLPADDING=20} CONFIDENTIAL   ";

ods pdf text="^S={just=Right Font_weight=bold font_size=4  borderwidth=5  CELLPADDING=20 } ACM/28.10.2011  ";

ods text=" ";

ods text=" ";

ods text="^S={just=center Font_weight=bold font_size=6 } NOTE FROM THE DEPUTY CHIEF FINANCIAL OFFICER ";

ods text=" ";

ods text="^S={just=center Font_weight=bold font_size=4  } Review note on ‘loss assets’ ";

ods text=" ";

ods text="^S={ font_size=4  } &NOTE1 ";

ods text=" ";

ods text= "^S={ font_size=4  } &NOTE2 ";

ods text=" ";

ods text= "^S={ font_size=4  } &NOTE3 ";

ods text=" ";

ods text=  "^S={ font_size=4  } &NOTE4 ";

ods text=" ";

ods text="^S={just=Right Font_weight=bold font_size=3} (Rs in million)  ";

proc report data=CPR0147_LOSS_ASSETS nowd style(header)={background= CX990000 foreground= white FONT_WEIGHT=BOLD FONT_SIZE=1.5} style(column)={FONT_SIZE=2.1}

    style(column)={background=white FONT_SIZE=2.1} style(report)={width=100% FONT_SIZE=2.1};

      COLUMN MOVEMENT START_DATE_VALUES ADDITIONS_DURING DELETIONS_DURING END_DATE_VALUES;

DEFINE MOVEMENT /'MOVEMENT ';

DEFINE START_DATE_VALUES / "&wdate1 ";

DEFINE ADDITIONS_DURING / 'Additions during H1-2012 ';

  DEFINE DELETIONS_DURING /  ' Deletions during H1-2012 ';

  DEFINE END_DATE_VALUES /"&wdate2 ";

RUN;

ods text='1. RBG: Retail Banking Group ';

ods text='2. RIBG: Rural and Inclusive Banking Group ';

ods text='3. SMEAG: Small, Medium Enterprises and Agri Group ';

ods text= ' ';

ods text= '^S={Font_weight=bold font_size=4  }Recommendation ';

ods text= ' ';

ods text= 'The Committee may note the review of loss assets.';

ods text= ' ';

ods text= "___________________________________";

ods text= ' ';

ods text= "^S={Font_weight=bold font_size=4  }Rakesh Jha";

ods text= '^S={Font_weight=bold font_size=4  }Deputy Chief Financial Officer   ';

ods text= ' ';

ods text='^S={Font_weight=bold font_size=4  }Originating Group: Financial Accounting and Reporting Group ';

ods text=' ';

ods text=' ';

ods text=' ';

ods text=' ';

%macro loop;

proc sort data=rbi_147 out=temperv nodupkey;

by SR_NO;

run;

data _null_;

set temperv end=eof;

call symput('SR_NO'!!left(put(_n_,10.)),SR_NO);

call symput('REMARKS'!!left(put(_n_,10.)),REMARKS);

if eof then

call symput('numrows',left(put(_n_,2.)));

run;

%put _user_;

ods pdf startpage=yes;

ods text=' ';

ods text=' ';

ods text="^S={just=right Font_weight=bold font_size=6 } Annexure";

ods text=' ';

ods text="^S={just=center Font_weight=bold font_size=5 } Accounts classified as ‘loss assets’ for more than two years ";

ods text="^S={just=center Font_weight=bold font_size=5 }where legal action has not been initiated at &WDATE2";

%do i=1 %to &numrows;

title ' ';

ods pdf  startpage=never;

proc report data=RBI_147  nowd    style(header)={background= CX990000 foreground= white FONT_WEIGHT=BOLD FONT_SIZE=2.1} style(column)={FONT_SIZE=2.1}

    style(column)={background=white FONT_SIZE=2.1} style(report)={ width=100% FONT_SIZE=2.1};

      COLUMN SR_NO NAME_OF_BORROWER OUTSTANDING;

DEFINE SR_NO / group   'Sr. no. ' ;

DEFINE NAME_OF_BORROWER / "Name of the borrower ";

DEFINE OUTSTANDING / 'Gross principal (Rs in million) ';

where put(SR_NO,num.)="&&SR_NO&i";

RUN;

ods text= ' ';

ods text= "&&REMARKS&i";

/*title ' ';*/

/*proc sql;*/

/*select SR_NO,NAME_OF_BORROWER,OUTSTANDING from RBI_147 where put(SR_NO,num.)="&&SR_NO&i";*/

/*quit;*/

/**/

/*ods text= "&&REMARKS&i";*/

%end;

%mend loop;

%loop;

ods pdf close;

   title ' ';

        DATA tw;

      LENGTH Download$ 1000;

     Download="<a href='http://sasebi92/regrep/CPR147-&ENDDATE..pdf'><img src='http://sasebi92/regrep/pdf_logo2.jpg'></a>";

      OUTPUT;

      RUN;

      PROC PRINT DATA=tw NOOBS;

      RUN;

  %END;

%MEND;

%PRINT;

/*END*/

getting below error in sas Stored Process,

ERROR: The format NUM was not found or could not be loaded.

Regular Contributor
Regular Contributor
Posts: 166

getting error in sas SP but not in SAS EG

Hi,

You have used the below statement in one of you proc reports

where put(SR_NO,num.)="&&SR_NO&i";

The NUM. is a user defined format which may have created by the person who has written this code. Since i cannot see any code for the format in your program i would assume that the format has been added to the library.formats catalog.

So when you run this in EG. You donot get an error as sas is able to read the format.

I believe you can look at creating and storing the format again  in a predefined library or a libref and then use the fmtsearch= option in your STP..

or

Add your proc format code to this program.

That should solve your problem..

Hope this helps....

Contributor
Posts: 31

getting error in sas SP but not in SAS EG

Heloo,

thanks for replay,

I trried to impliment with proc format but   i am unable to do this ,if u give detailed information it would helpfull

Regards,

Ramesh

SAS Super FREQ
Posts: 8,739

Re: getting error in sas SP but not in SAS EG

Hi:

  If the code workes in EG, that means that EG has had the NUM. format defined. You need to find the code or otherwise use PROC FORMAT with the FMTLIB option to see what the definition for the NUM. format is.

  If necessary, then you will need to recreate the format in a location that is available to the Stored Process Server or the Workspace Server (which is how stored processes are run). Each Platform installation has a location where formats can be automatically stored, so you do not need a FMTSEARCH option -- that location is generally in the

Lev1\SASApp\SASEnvironment\SASFormats location,

as defined in your installation. You may have to check with your SAS Administrator to get permission to either 1) add your format to the catalog that is in this location or 2) create your own format catalog in this location and/or modify the FMTSEARCH option used for the servers in question.

  Since this is not just a matter of plugging a trivial amount of code into a window and submitting it, I'd recommend that you work with Tech Support on this issue, because you do NOT want to try something and then mess up the production format catalog in a Platform install.

cynthia

Ask a Question
Discussion stats
  • 3 replies
  • 311 views
  • 0 likes
  • 3 in conversation