Getting Started with SAS® Text Miner

http://support.sas.com/documentation/cdl/en/tmgs/63281/HTML/default/viewer.htm#titlepage.htm

Cascading Prompts Made Easy

A simple and doable example is illustrated in below paper –

http://support.sas.com/resources/papers/proceedings10/041-2010.pdf

My 100th Post (macro variables created in a single list)

proc sql noprint;

create table test as select distinct
unq_catg_desc,
quote(strip(unq_catg_desc)),
"'"|| (strip(unq_catg_desc)) ||"'",
unq_catg_i,
unq_catg_i format 8. as var
from abc.unq_hr;

select * into
:E1 SEPERATED BY " , " ,
:E2 SEPERATED BY " , " ,
:E3 SEPERATED BY " , " ,
:M1 SEPERATED BY " " ,
:M2 SEPERATED BY " , "
from test;

quit;
%PUT &E1; %PUT &E2; %PUT &E3;
%PUT &M1; %PUT &M2;

Apple , Custard Apple ,
"Apple" , "Custard Apple" ,
'Apple' , 'Custard Apple' ,
29 25
29 , 25 ,

More: http://www2.sas.com/proceedings/sugi27/p071-27.pdf

Registering a table in SAS Metadata Server

For creating a dynamic prompt in stored process/ EG, a SAS user should register his/her table (i.e., the respective source for prompt). For registering the table in SAS Metadata Server, one can make use of the METALIB procedure.

proc metalib;

omr (library="abc");
select("MSTR");
/* Create a summary report of the metadata changes. */
report;
run;

More: http://support.sas.com/documentation/cdl/en/engimap/61078/HTML/default/viewer.htm#a003088532.htm

ERROR: Template 'MyTemplate' was unable to write to template store!

While creating graphs, I made use of below code to avoid this error-

ODS PATH work.templat(update) sasuser.templat(read) sashelp.tmplmst(read);

More: http://www.runsubmit.com/questions/179/error-with-ods-and-proc-template

Play with Graphs

http://support.sas.com/sassamples/graphgallery/index.html
http://robslink.com/SAS/Home.htm

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"