Creating Validation using SAS format and Intelligent handling plus accumulating format validation error.

I created a small program to cross validate the incoming data with a Master data file.

Approach : I created SAS formats for various validation fields according to business rules applicable.
Then check the incoming data with the formats already created.
Then I accumulated the warning\error in a different file. This way I could accumlate all the error& warnings in one file.


1) Create a format by hardcoding , (I had created format using MS-access files too -- will cover it later on)
proc format library=prod;
value $TODA
'1001INTEGRATION_' = '7081'
'1001PROD' = '7081'
'1001SYSTEM_TEST' = '7081'
OTHER = '0'
;
run;

/*Store SAS format in to required lib*/

proc format
Fmtlib lib=work
cntlout=prod;
select $TODA;
run;


2) Performing validation
/* Validate all postings */

data work.AdjustmentX (drop=nr imperr impfield local_ammount_err accgroup)
work.errorlist_GL (keep=nr imperr impfield)
work.warnlist_GL (keep=nr imperr impfield)
work.cd12026_errorlist_adj (keep=nr imperr impfield)
;
set work.AdjustmentX end=slut;

if cd01001 > ' ' then do;
tst = put(cd01001,$accunit.); /* Performing validation */
if tst = 1 then do;
tst = put(compress(&cd01041!!cd01001),$ccac.);
if tst ^= 1 then do;
%FieldError(cd01001,Account unit not valid with company code &cd01041,errorlist_GL);
end;
end;
else do;
%FieldError(cd01001,Profit center invalid,errorlist_GL); /* Error macro */
end;
end;
else do;
if transaction_type = 'GL' then do;
%FieldWarning(cd01001,Tick-Off Ledger Id missing,Warnlist_GL); /* Warning Marco*/
end;
end;
run;



3) Accumulate Errors and warning via a general macro into a particular dataset.


Here i have called few general macros if there is a error or if i wana give a warning.

/* general macros*/

%macro FieldError(CD,errtxt,mem);
%if "&errtxt"="" %then %let errtxt=Wops;
%if "&CD"="" %then %let CD=WopsAgain;
imperr = "&errtxt";
impfield = &CD;
call symput('imperr',imperr !! impfield);
nr = _n_;
output &mem;
*put '---------------------' imperr= impfield= "&mem";
%mend;

%macro FieldWarning(CD,Warntxt,mem);
%if "&Warntxt"="" %then %let Warntxt=Wops;
%if "&CD"="" %then %let CD=WopsAgain;
impWarn = "&Warntxt";
impfield = &CD;
call symput('impWarn',impWarn !! impfield);
nr = _n_;
output &mem;
*put '---------------------' impWarn= impfield= "&mem";
%mend;


Note : /* Mention name of error log / warning log dataset as : &mem */



Let me know if anyone has any suggestion / doubts on this .

Happy to help , Chow !!

Tag dataset to SAS share server, how to browse that dataset, SQL distinct identifier

This code run both on windows and Mainframe servers.


Attach a dataset to SAS Share server, (Run this after starting the share server)
ALLOCATE LIBRARY Orgdata 'BWZ0.LA.SASDATA.ORGDATA' SERVER=SHARE01;
DATALIBS Orgdata;


Read data set tagged to a SAS share server
LIBNAME dates 'BWZ0.LA.DATE.STORAGE' server=share01 DISP=SHR



SQl query for finding non-unique values:

proc sql;
create table repeat as
SELECT DISTINCT year,week_nr , teamid from La130.Wdata
group by year,week_nr, teamid
HAVING count(*) > 1 ;
quit;


Ways to remove duplicates in SAS SQL :

proc sort data=LA130.WATA out=nodups5
nodupKEY;
by TEAMID DATENUM;
RUN;

Funtion to trigger SAS job from javascript / html (SMART work):

function gotohome()
{
open("/tst-bin/broker?_service=tstnorm&_program=tstsrc.llastart.sas&_debug=0",target="lla_main");
}

NOTE: Broker must be installed on the SAS server
NOTE: Change the service name accordig to your project
NOTE: program name is like: Servicename.programname.sas
NOTE: Target is the HTML page lable/name.
NOTE: Give debug=131 for getting the SAS log


I have few more examples to call SAS broker with various parameters.
/*--------------------------------------------------------------+
| go to Next page |
+--------------------------------------------------------------*/

function next()
{
var _flag;
var year_nr =yearnr[0];
var week_nr =weeknr[0];
_flag = 'N';
alert(year_nr);
alert(week_nr);
open("/tst-bin/broker?_service=tstnorm&_program=tstsrc.lla500.sas&_debug=0&_flag="+_flag+
"&_year="+year_nr+
"&_week_nr="+week_nr+"",
target="lla_main");
}


/*--------------------------------------------------------------+
| go to Previous page |
+--------------------------------------------------------------*/

function previous()
{
var _flag;
var year_nr =yearnr[4];
var week_nr =weeknr[4];
_flag = 'P';

alert(year_nr);
alert(week_nr);


open("/tst-bin/broker?_service=tstnorm&_program=tstsrc.lla500.sas&_debug=0&_flag="+_flag+
"&_year="+year_nr+
"&_week_nr="+week_nr+"",
target="lla_main");
}



This post is mostly for myself , but if you find few pieces useful here do post comments.

Upload and Download to Mainframe from Windows server !! SAS Upload&Down

Its been a while since i posted, had been busy with ma-life :) .

Here is the thing pals, Njoy. Code is self explanatory but if you face trouble do write in comments :

1st logon to you mainframe server:

data _NULL_;
usrid= upcase(sysget('username')); /* Symget will get the user name you are using on windows server */
call symput('usrid',usrid);
run;
options comamid=tcp;
%let mvs0=mvs3ibm.sys.nb.se 5308; /* mention the ip address of mainframe along with port number */
signon mvs0.spawner USER=&usrid noscript pw=_prompt_; /*password will be prompted*/
rsubmit;
options comamid=xms2;
endrsubmit;


A)
/* Use below code for Uploading to mainframe, Note, inlib=work, change to any libname if you have any thing local *******/

rsubmit;
OPTIONS NOERRORABEND;
LIBNAME MVS 'BLL0N.SAS.CENTRAL.DATA' DISP=old; /* mention your mainframe dataset name here */
proc upload inlib=work outlib=mvs;
select Formats /memtype=data;
run;
endrsubmit;
signoff;


B)
/**********For downloading any thing from mainframe */

rsubmit ;
OPTIONS NOERRORABEND;
LIBNAME MVS 'BLL0N.SAS.CENTRAL.DATA' disp=shr; /* mention your mainframe dataset name here */
proc download inlib=mvs outlib=work V6TRANSPORT;
select weekdata /memtype=data;
run;
endrsubmit;
signoff;


Hope it helps, comments are helpful in getting motivation for future post so do not shy away !

Chow!!