System.InvalidOperationException - SplitterDistance must be between Panel1MinSize and Width - Panel2MinSize

Incase if you receive the below error –

1. Navigate to path “H:\Personal\Appdata\SAS\EnterpriseGuide\4.3”
2. And delete 4.3 folder

More: http://support.sas.com/kb/43/655.html

Order of precedence

The below merge is more like a set statement. But here there is a possibility of the same store occurring in both new_stores and mature_stores table. Hence flagging with ‘if a’ and ‘if b’ could be problem, as one store could fall in both the groups. Care should be taken while making use of this logic. In the below example if a store is found in mature_stores table then it no more belongs to new store category, so the order of precedence is given to mature stores using ‘if a’.


/* Dataset for MATURE & NEW STORE*/
data all_stores;
merge mature_stores(in=a) new_stores(in=b);
by str_i;
if a then FLAG="Mature";
if b then FLAG="New";
run;

Datalines within a macro will throw error

*Datalines outside a macro;
data new1;
input x 8.;
datalines;
1
2
3
;
run;

%macro test2;
*Datalines within a macro will throw error;
data new2;
input x 8.;
datalines;
1
2
3
;
run;
%mend test2;
%test2;

*ERROR: The macro TEST2 generated CARDS (data lines) for the DATA step, which could cause incorrect results. The DATA step and the macro will stop executing.;

%macro test3;
*Make use of the below method if you want to create records;
data new3;
length x 8.;
x=1; output;
x=2; output;
x=3; output;
run;
%mend test3;
%test3;

Else one can make use of proc sql insert into

Execute macro with data _null_

options mprint mlogic symbolgen;


*Create a dataset with no records;
data test_store;
attrib store length=8 ;
attrib go_d length=8 format=date9.;
stop;
run;


*This macro gets executed by call execute to populate test_store - with no observation;
%macro store(str_i);
proc sql noprint;
insert into test_store
select &str_i, go_d from def.str
where str_i = &str_i;
quit;
%mend store;


*Pick random sample of stores for which the test_store dataset should get populated;
data random;
set abc.score_11;
x=ranuni(1234);
run;


*Randomly sort the data set;
proc sort data=random;
by x;
run;


*Keep the first n observations. Since the data points are randomly sorted, these observations constitute a simple random sample;
data sample(drop=x);
set random (obs=10);
run;


*This creates a dataset named test_store WITH ONLY THOSE STORES THAT ARE PRESENT IN SAMPLE;
data _null_;
set sample;
call execute('%store('str_i')');
run;


More: http://www2.sas.com/proceedings/sugi22/CODERS/PAPER86.PDF

Random Sample Selection

PROC SURVEYSELECT or RANUNI function can be used for random sample selection.

More: http://cba.ua.edu/~mhardin/SAMPLE%20Simple%20Random%20Sample%20Without%20Replacement.htm

Extracting data from relational databases

/*example 1: SAS ACCESS/LIBNAME statement*/

libname udblib db2 uid=testuser pass=testpass dsn=strtd101 schema=STR;
libname oralib oracle user=testuser password=testpass path=crotd101 schema=CRO;


/*example 2: SAS ACESS/SQL pass through facility*/


PROC SQL;
connect to db2 as db2tbl (user=testuser password=testpass datasrc=sample in=userspace1);
create table SASLOCAL as
select * from connection to db2tbl
(select * from RUN_PARM);
disconnect from db2tbl;
quit;

proc sql;
connect to oracle as oratbl (user=testuser password=testpass path=crotd101);
create table AE as
select * from connection to oratbl
(select * from AE_ORACLE_TABLE);
disconnect from oratbl;
quit;
 
More: http://support.sas.com/documentation/cdl/en/connref/61908/HTML/default/viewer.htm#a000269041.htm

Learning SAS BI

Getting Started with SAS BI:

SAS® Business Intelligence Client Tools Course Notes (SBIOVR) - This Course Notes is a wonderful material to learn/ practice SAS BI

Weird Error from Dictionary Tables (Migration issues of SAS 9.1 to 9.2)

/*Weird Error from Dictionary Tables during SAS Upgrade*/
proc sql;
select * from dictionary.TABLES;
quit;
proc sql;
select * from sashelp.VCOLUMN; *This internally query’s DICTIONARY.COLUMNS
quit;


ERROR: File WORK.XYZ.DATA does not exist.
ERROR: File WORK.XYZ.DATA does not exist.


Reason behind this problem in our case –


There was a table named XYZ in library ABC but this had a input table from a second library that had not been assigned (i.e., WORK). The WORK Library was not assigned because in a grid enabled SAS environment the WORK library is reassigned as another library (named RMTWORK). As the ABC.XYZ was somehow reading the data from WORK.XYZ it resulted in a weird error. The below link states a similar reason for this problem (in a Note) –

Note: An error occurs if DICTIONARY.TABLES is used to retrieve information about an SQL view that exists in one library but has an input table from a second library that has not been assigned.


http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/viewer.htm#a001385596.htm

How to avoid this error -

We can avoid this error by eliminating the problematic libraries (i.e., in our case it is ABC & WORK). If we filer out ABC & WORK Library in where condition, the ABC & WORK Library data will not even be loaded to SAS & hence it does not throw error. You can find similar problem faced by another SAS User in the below link –


http://www.listserv.uga.edu/cgi-bin/wa?A2=ind0907d&L=sas-l&P=22255


/*Fix for Query*/
proc sql;
select * from dictionary.TABLES
where libname not in ("ABC", "WORK");
quit;
proc sql;
select * from sashelp.VCOLUMN
where libname not in ("ABC", "WORK");
quit;

The solution given here explains only about "how to avoid the error?” as the original query is successful even with errors.

Use of memtype with kill

Kill deletes all SAS files along with the datasets in a particular library. For example, consider a user had assigned a format for display in report and the user also had made use of kill statement to kill the work library. The kill statement which is used at the end of the code not only deletes the dataset in work library, it also washes away the formats of the report because of which the report will no longer have the format that the user had assigned. Hence if the user wants to delete only the datasets then it is better to make use of memtype option which will preserve all other sas file and will kill only the dataset member type.

proc datasets lib=work kill memtype=data;
run;
quit;

Macro Quoting Functions & %NRQUOTE

Category="BASE SAS","ADVANCE SAS"

Consider we have a macro named Category and is used in a macro call.

SAS will throw error if this macro variable gets resolved during macro call, as the value of this macro variable contains quotation marks. But we don’t want this macro variable to get resolved during the macro call instead we wanted this value as an observation within a dataset (which is created within the macro).

Hence we can make use of macro quoting function which mask the value of a macro variable helping the macro facility to interpret double quotation marks as text rather than macro language symbols.

With functions like %STR, %NRSTR, %QUOTE, and %NRQUOTE, unmatched quotation marks and parentheses must be marked with a % sign. Where as you don't have to mark unmatched symbols in the arguments of %BQUOTE, %NRBQUOTE, and %SUPERQ.

More: http://www.okstate.edu/sas/v8/sashtml/macro/zenid-37.htm

Send email from SAS

This blog gives a simple example on "how to send email from SAS"

http://www.afhood.com/blog/?p=400

SUM function to ensure that the result is not missing

Use SUM function to ensure that the result is not a missing value.

sum (aaa, bbb);
sum(aaa, -bbb);

Export & Import for CSV Files

***Export a SAS Dataset to CSV File;

%macro test;

DATA x; x=today(); FORMAT x MMDDYYN8.; RUN;
PROC SQL NOPRINT; SELECT x INTO :x FROM x; QUIT; %PUT &x;
OPTIONS missing="";

DATA _NULL_;
SET AAA.sales END=last;

dateheader=compress("H""&x");
FILE "/abc/dev/xyz/sales.csv" DLM=',';
IF _N_=1 THEN PUT dateheader;

var=compress(fcst_acct_yr_i','fcst_acct_mo_i','fcst_acct_mo_wk_i','str_i','sales);
PUT var;

IF last THEN DO;
e=compress("T"_N_);
PUT e;
END;

RUN;
%mend test;
%test;

***Import a CSV File to SAS Dataset;

%let IN_file=/abc/dev/xyz;
%macro test;

PROC IMPORT OUT=WORK.Temp DATAFILE="&in_file.dummy.csv"
DBMS=CSV REPLACE; GETNAMES=YES;
RUN;

%mend test;
%test;

Unlock the dataset

While running SAS jobs concurrently or when a different user had opened the dataset that you need, the following error message appears:

ERROR: A lock is not available for XYZ.abc.
ERROR: Lock held by process 34013400.
NOTE: The SAS System stopped processing this step because of errors.

In this case, copy the process id (34013400) from log and use the below UNIX command to find out which user had locked your dataset and request them to Close it/ Unlock it.

ps -ef I grep "process_id"

Keep required datasets in WORK Library & Kill the rest

The PROC DATASETS procedure provides the SAVE statement which will delete all members in a library except for the ones noted on the SAVE statement:

proc datasets library = work;
save DUMMY;
quit;
run;

Simple BAR Chart

/* format the values for barchart */
proc format;
value grpc (multilabel)
-11 = '< -10%'
-10 = '-10% to -8%'
-8 = '<-8% to -6%'
-6 = '<-6% to -4%'
-4 = '<-4% to -2%'
-2 = '<-2% to 0%'
0 = '<0% to 2%'
2 = '<2% to 4%'
4 = '<4% to 6%'
6 = '<6% to 8%'
8 = '<8% to 10%'
10 = '> 10%'
;
run;

%macro graph;
*path in which the report should be placed;
ods html path = "abc/dev/report"
body = "Dummy_Chart.html"; *report name;

GOPTIONS xpixels=900;
pattern1 color=Green;
axis1 label=(a=90 'Count of Stores ');
axis2 label=('TY XYZ Over LY %')
value=(angle=90);
title1 'TY XYZ Bar Chart';

proc gchart data=work.TY_XYZ_Over_LY;
vbar temp / sumvar=store_count discrete noframe
width=3
raxis=axis1 maxis=axis2
autoref clipref cref=graybb
coutline=Yellow woutline=2;
format temp grpc.;
run;

ods html close;
%mend graph;
%graph;

Invoke external SAS macros in a SAS program

Autocall macro and %include are two common methods to invoke external SAS macros in a SAS program.

Method I: Autocall macro

%let path=/abc/dev/code/xyz;

option mlogic mprint symbolgen merror source source2 mrecall mautosource sasautos=(
"&path"
"!SASROOT/sasautos");

*macro call of SAS program;
%EXAMPLE;

Method II: %include

%include "/abc/dev/code/xyz/EXAMPLE.sas";

Details of related System Options used:

MERROR - issues the following warning message when the macro processor cannot match a macro reference to a compiled macro:

WARNING: Apparent invocation of macro %text not resolved.

SOURCE - specifies to write SAS source statements to the SAS log.

SOURCE2 - specifies to write to the SAS log secondary source statements from files that have been included by %INCLUDE statements.

MRECALL - searches the autocall libraries for an undefined macro name each time an attempt is made to invoke the macro. It is inefficient to search the autocall libraries repeatedly for an undefined macro. Generally, this option is used while developing or debugging programs that call autocall macros.

!SASROOT:

The !SASROOT directory contains the files required to use SAS. This directory includes invocation points, configuration files, sample programs, catalogs, data sets, and executable files.

More: http://support.sas.com/documentation/

UPDATEMODE=

MISSINGCHECK
prevents missing values in transaction data set from replacing values in master data set.

NOMISSINGCHECK
allows missing values in transaction data set to replace values in master data set.

/*create or update permanent dataset based on their exsistence*/
%macro update;
proc sort data=trans; by store; run;

%if %sysfunc(exist(ABC.master))=0 %then %do;
data ABC.master;
set trans;
run;
%end;

%else %do;
data ABC.master;
update ABC.master trans UPDATEMODE=NOMISSINGCHECK;
by store;
run;
%end;

%mend;
%update;

if _n_=1 then set

If a user wants to add single obeservation of one dataset to each record of the other dataset, then the user can make use of _n_=1.

%let Final_Year=2012;
%let Final_Week=4;

/*get the last date from date dataset based on input parameters (to calculate the age of a store)*/
data lday(rename=(wk_end_d=lday));
set acct_date(where=(yr_i=&Final_Year and wk_i=&Final_Week));
run;

/*add the last date to each record of store to calculate the age*/
data store_lday;
if _n_=1 then set lday(keep=lday);
set store;
***** age *****;
if open_date ne . then do;
age_in_wks=intck('week',open_date,lday);
age_in_mth=intck('month',open_date,lday) - (day(lday) < day(open_date));
end;
run;

Simple HTML Report

*/path in which the report should be placed/report name;

%let body=/abc/dev/report/new_stores;

ods html body="&body..html";
title1 "New Stores";
proc report data=new_stores;
column storeid acct_yr_i acct_mo_i dummy_var;

define storeid /display 'Store ID';
define acct_yr_i /display 'Accounting Year';
define acct_mo_i /display 'Accounting Month';
define dummy_var /display 'Dummy Variable';
run;
ods html close;