Inactive Foreign Key Error (Migration issues of SAS 9.1 to 9.2)

Foreign key integrity constraints can become inactive when SAS data sets are moved via the COPY, CPORT, CIMPORT, UPLOAD, or DOWNLOAD procedures. It is possible to happen with PROC MIGRATE too. The reason may be because of PROC MIGRATE being potential enough to migrate datasets with integrity constraints but not with referential constraints. In that case, a user has to activate the foreign key by using IC REACTIVATE.

proc datasets library=XYZ;
modify AAA;
ic reactivate for references ABC;
run;
quit;

Things to do -

1. Execute proc contents of the dataset to view the inactive foreign key.
2. Execute the above proc statement for reactivating the respective foreign key.
3. Once executed check the log which would say that the foreign key is reactivated
4. Now execute proc contents again for the same dataset, to see the status as null for “Inactive” column.

IMPORTANT: The “describe table constraints” has to be executed (in the old box - SAS 9.1) to understand the referential constraints and to pass the dataset to the ‘references’ of ic reactivate statement.


ERROR WHILE EXECUTING IC REACTIVATE

The below error is expected to occur if the foreign key reference dataset (ABC.STR) has lost its constraints during UNIX copying (which should actually be migrated with PROC MIGRATE).

2984 proc datasets library=XYZ;
2985 modify AAA;
2986 ic reactivate forkey references ABC;
ERROR: Primary key does not exist in data set ABC.STR.DATA.
2987 run;

Issues Related To System Options (Migration issues of SAS 9.1 to 9.2)

1. A variable name can be created with space in SAS EG 4.1 without explicitly adding the option VALIDVARNAME ANY. Whereas the same couldn't be done in 4.3.

2. In the SAS EG 4.1 the user had write access to the SASUSER library. Whereas in 4.3, the write access has been denied.

Both the above issues were related to SAS SYSTEM OPTIONS differing between 4.1 and 4.3 (in our ENV). Below are the details of difference:

SAS Version 4.1: Option Name - Settings Description/ its use

RSASUSER NORSASUSER - Enables a user to open a file in the Sasuser library for update. VALIDVARNAME ANY - A variable name with space can be created.

SAS Version 4.3: Option Name - Settings Description/ its use

RSASUSER RSASUSER - Limits access to the Sasuser data library to read-only.
VALIDVARNAME V7 - Control the type of SAS variable names that can be created during a SAS session.

Requesting for change in System Options will resolve this issue.

Why Proc Migrate (Migration issues of SAS 9.1 to 9.2)

“PROC_MIGRATE” - Migrates datasets with its integrity constraints. Whereas a simple FTP through UNIX will make the dataset lose its integrity constraints.

Enclose Values Within Quotes (Migration issues of SAS 9.1 to 9.2)

In old box (i.e., SAS 9.1), there was an option "Enclose values within quotes" in GUI Prompts that ensured all values in the list that the code substituted during execution was within quotes as they were string.

Example: %LET unq_catg_desc = %STR("Bakery");

In new box (i.e., SAS 9.2), this option is not available and the code error out as it is specifically looking for string. Hence the user will have to add it in the code.

Example: %LET unq_catg_desc = Bakery;

Migrating SAS Enterprise Guide Projects (Migration issues of SAS 9.1 to 9.2)

When migrating an Enterprise Guide project from SAS 9.1 (EG 4.1) to SAS 9.2 (EG 4.3) there are three main techniques that are available to achieve this.

Those are:

1. Opening the EG 4.1 project in EG 4.3 and save. This approach is suitable if there are no required changes to Server names, Libnames, paths etc.

2. Utilizing the Enterprise Guide Migration Wizard (in Citrix Folder). This approach is used for migrating multiple EG Projects at one shot.

3. Manual Migration with Project Maintenance wizard via Tools. The Enterprise Guide 4.1 project needs to be opened in Enterprise Guide 4.3 so as to manually make the necessary changes.

Refer the below link for more details on each option:
http://support.sas.com/resources/papers/proceedings11/313-2011.pdf

Prompts Manager Display Extra Decimal Places (Migration issues of SAS 9.1 to 9.2)

In parameter manager of SAS EG 4.1, when a macro variable is created with ‘Float’ as Data Type and when integers are entered in the List of values there is no difference in the appearance of GUI Screen. The GUI Screen displayed integer values only.

Whereas when the same EGP when migrated to the new version 4.3, the prompts manager adds ‘.0’ to all values in the list. Hence the GUI Screen displays all values with ‘.0’. This is because of this feature being set in auto correction mode.

One can make required modification in SAS EG 4.1 and then migrate it. This will resolve the issue.

Using Multiple Selection Prompts (Migration issues of SAS 9.1 to 9.2)

Web Suggestion: (From Angela Hall)

For example in the old box (i.e., SAS 9.1), let us say we create a prompt for region (called 'region_prompt') and then use that in the query of sashelp.shoes. The GUI Prompt created only one macro variable called region_prompt which contained all user selection:

proc sql; create table test as select * from sashelp.shoes where shoes.region="region_prompt";
quit;

But now in the new box (i.e., SAS 9.2), if we allow users to select 1 or more values for region, SAS creates n number of macro variables with the same name but adding _Count to it. Such as region_prompt_count, this represents the amount of selections the user chose. Therefore the SQL query needs to take all of these selections into account. ALSO - if only 1 selection is chosen, there is no region_prompt1 - so you must account for that as well. Here is an example:

proc sql; create table test as select * from sashelp.shoes
where shoes.region in (
%if REGION_PROMPT_COUNT = 1 %then "&Region_Prompt";
%else %do i=1 %to &REGION_PROMPT_COUNT;
"&&Region_Prompt&i"
%end;
);
quit;

SAS Tech Support Suggestion:

Since the user is writing their own code that uses the prompts, Enterprise Guide does not automatically change over the macro variable code (like it would if they were using the prompt in a Query). But user can go through and update their code where they had the WHERE clause so that it uses the new %_eg_WhereParam macro variable. This is what is now used to account for parameter lists. This is how it would look in the code. The first parameter is the dataset.variable the user is querying, the second is the macro variable, the third is the operator, and the fourth is S or N for string or numeric type.
where %_eg_WhereParam(a.unq_catg_desc, unq_catg_desc, IN, TYPE=S)

Both these suggestion will work for a single code but user cannot make changes to each and every code during Migration and hence can make use of the below autocall macro:

%macro param_macro(var= /*Required Macro variable Name*/
,FMT_Char=/*Y/N Required to present it with quote or without*/);
/*
Name : param_macro.sas
Purpose : %param_macro(), converts an array of values entered in an
UI prompt to 1 macro variable

Call the macro by passing the macro variable name assigned
in the parameter manager.

Usage : OPTIONS SASAUTOS=('Path' '!SASROOT/sasautos');
%param_macro(var=)
*/

options mlogic mprint symbolgen;

%global &&var.;

Data _null_;
length x $20000.;
%if &&&var._COUNT GT 0 %then %do;
%if %upcase(&FMT_CHAR)=Y %then %do;
x = '"'"&&&var.1"'"';
%end;
%else %do;
x="&&&var.1";
%end;
%end;
%if &&&var._COUNT = 1 %then %do;
%if %upcase(&FMT_CHAR)=Y %then %do;
x = '"'"&&&var."'"';
%end;
%else %do;
x = "&&&var.";
%end;
%end;
%else %do i=2 %to &&&var._COUNT;
%if %upcase(&FMT_CHAR)=Y %then %do;
x = strip(x)',"'"&&&&&var.&i"'"';
%end;
%else %do;
x = strip(x)",""&&&&&var.&i";
%end;
%end;
call symput("&var.",x);
run;

%put NOTE: Number of selections made in the UI : &&&var._COUNT;
%put NOTE: Macro Variable &&var. resolves to: &&&var.;
%mend param_macro;

More: http://blogs.sas.com/content/bi/2009/11/10/using-multiple-selection-prompts-in-sas-stored-process-code/

Describe View

Below is the piece of SAS code to find view definition –

proc sql;
describe view libname.view;
quit;

Output:
NOTE: SQL view LIBNAME.VIEW is defined as:

select distinct str.STR_ID
from XYZ.AAA str
where str.STR_ID= 101;

Make use of view definition in log and recreate view as below (If migrated to a new version of SAS)–

proc sql;
create view LIBNAME.VIEW as
select distinct str.STR_ID
from XYZ.AAA str
where str.STR_ID= 101;
quit;

Query DICTIONARY Tables and SASHELP Views

To access SAS System Information, user needs to query DICTIONARY Tables and SASHELP Views

proc sql;
create table work.XOPTIONS as
select * from dictionary.OPTIONS;
quit;

proc sql;
create table work.XVIEWS as
select * from dictionary.VIEWS;
quit;

proc sql;
create table work.XTABLE_CONSTRAINTS as
select * from dictionary.TABLE_CONSTRAINTS;
quit;

proc sql;
create table work.XREFERENTIAL_CONSTRAINTS as
select * from dictionary.REFERENTIAL_CONSTRAINTS;
quit;

proc sql;
create table work.XCHECK_CONSTRAINTS as
select * from dictionary.CHECK_CONSTRAINTS;
quit;

proc sql;
create table work.XCONSTRAINT_TABLE_USAGE as
select * from dictionary.CONSTRAINT_TABLE_USAGE;
quit;

proc sql;
create table work.XCONSTRAINT_COLUMN_USAGE as
select * from dictionary.CONSTRAINT_COLUMN_USAGE;
quit;

proc sql;
create table work.XINDEXES as
select * from dictionary.INDEXES;
quit;

proc sql;
create table work.XFORMATS as
select * from dictionary.FORMATS;
quit;

proc sql;
create table work.XLIBNAMES as
select * from dictionary.LIBNAMES;
quit;

proc sql;
create table work.XMACROS as
select * from dictionary.MACROS;
quit;

proc sql;
create table work.XCATALOGS as
select * from dictionary.CATALOGS;
quit;

proc sql;
create table work.ODICTIONARIES as
select * from dictionary.DICTIONARIES;
quit;

proc sql;
create table work.OMEMBERS as
select * from dictionary.MEMBERS;
quit;

proc sql;
create table work.OGOPTIONS as
select * from dictionary.GOPTIONS;
quit;

proc sql;
create table work.OSTYLES as
select * from dictionary.STYLES;
quit;

proc sql;
create table work.OTABLES as
select * from dictionary.TABLES;
quit;

proc sql;
create table work.OTITLES as
select * from dictionary.TITLES;
quit;

DATA work.OVCOLUMN;
SET sashelp.VCOLUMN;
RUN;

DATA work.OVEXTFL;
SET sashelp.VEXTFL;
RUN;

SAS Online Training

-
http://www.mathstat.dal.ca/cluster/manuals/sasdoc/59322/paths.htm
-

Find out start and end date of a week

-
data _null_;
*The intnx function can be used to find out the end date of a week (when the user is in the middle of the week);
start=intnx('week',today(),1)-1;
*Then user can then apply the below code to fetch another 12 weeks of data from this weekend date;
end=(start+7*12);
call symput('start_dt',put(start,date9.));
call symput('end_dt ',put(end,date9.));
run;
%put &start_dt &end_dt;

*In a same way the user can find out a start date of a week;
example=intnx('week',today(),0);
-
More: http://www2.sas.com/proceedings/sugi30/255-30.pdf
-

Change the font "of all post"

-
Go to Edit HTML and find "post-body" to embed this code:

.post-body {
position: relative;
font-family: Trebuchet MS !important;
}

Performance tuning while working with large datasets

1. WHERE to subset data
2. KEEP / DROP to reduce cpu time
3. LENGTH to reduce variable size
4. CHARACTER variables need to be created as much as possible
5. IF-THEN/ELSE to improve efficiency
6. MACROS for redundant code
7. PROC SORT only when needed
8. PROC SQL to reduce the number of steps
9. INDEX to read large datasets
10. COPY to copy dataset with index
11. COMPRESS to reduce number of bytes
12. DATA _NULL_ for processing null datasets
13. PROC APPEND instead of set
14. PROCs with CLASS statement need to be used
15. SASFILE to reduces I/O processing
16. STORED PROGRAM FACILITY for complex data steps
17. BUFSIZE for the size of the input/output buffers
18. REUSE for whether free space is reused
19. POINTOBS to randomly access by an observation number
20. NOMACRO to conserve memory
21. KILL unwanted datasets
22. FORMAT/ INFORMAT instead of if then else (for logics)
23. VIEWS to create virtual tables
24. SAS FUNCTIONS to perform common tasks
25. COMBINE steps to reduce number of DATA and or PROC steps

More: http://www.mwsug.org/proceedings/2009/how/MWSUG-2009-H06.pdf

Append & Force

-

The procedure append is recommended when the dataset is being overwritten. This helps the user concatenate large number of datasets.

-

Syntax:

-
proc append base=SASHELP.dummy data=WORK.dummy force;
run;
-
The force option displayed here is used to forcibly append the dataset (while encountering differing attributes for the same variable).

-

Check for the existence of a dataset

-

The exist function checks for the existence of a dataset.

-

options mprint mlogic symbolgen;

%macro test;

%if %sysfunc(exist(work.dummy))=0 %then %do;

%goto quit;

%end;

%else %do;

proc sql; select count(distinct pt) into: tst from dummy;

run;

%put &tst;

%end;

%quit:

%mend;

-
%test;

-

More: http://www2.sas.com/proceedings/sugi29/126-29.pdf
-

Kill your datasets

This code will help the user kill the temporary datasets in work library.

proc datasets library=work kill;

run;

FAQs

1. List out different ways to delete duplicates?
2. What are the different procs that you have used so far?
3. Difference between nodup and nodupkey?
4. How do you get unique observations?
5. List out different functions that you have used inside a macro?
6. Tell me about the options that you have used in proc compare & proc report
7. Can you write down the syntax for proc means or proc freq?
8. What is the use of multilabel option in proc format?
9. Brief me about the last project that you have worked on?
10. What was the interesting technical issue that you have come across & how did you solve it?
11. Why are you looking for a change?
12. How do you rate yourself in macro?
13. Compare merge and proc sql joins?
14. How do you avoid merge by values?
15. What is the advantage of using merge over proc sql?
16. Which is efficient if or where condition? Why?
17. What are the different ways to create a macro variable?
18. What is the scope of global and local macro variable?
19. What are the different items that you will look for in a log?
20. Difference between format and informat?
21. What is the use of data _null_?
22. How do you write a code to see 'only the duplicate observations'?
23. What is PDV?
24. List out some automatic macro variables in sas?
25. What is the result of using two set statement?

Double Dash

Usually to keep a set of variables with same prefix we use “-“

For example:

COL1 COL2 COL3 COL4 COL5 COL6 COL7

To keep the above variables, we use (keep COL1 - COL7;)

but what if the variables does not have same prefix?

Yes, there is another way to keep the variables even when they do not have same prefix!

“--“ can be used to keep the variables in the order in which they occur in a dataset.

data dummy;
input a b1 b2 c;
datalines;
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
;
run;

data dummy1;
set dummy;
keep a -- b2;
run;

More: http://studysas.blogspot.com/2009/07/even-you-can-use-hash-and-double-dash.html

Multi-Value Ranges

This was one of my interview questions.

"How do you write a proc format for overlapping values? Can you write down the format for variable: age?"

I have pasted the same here. The multilabel option helps a user in defining format for overlapping values.

proc format;

value age(multilabel)

0 - 12 = "Children"

13 - 19 = "Teenager"

20 - 25 = "Young Adult";

0 - 19 = "Children & Teenager";

low - 25 = "Children, Teenager & Young Adult";

25 - high = "Adult";

run;

Another example:

proc format; 
value cat(multilabel)
90 - high = "90% improvement"
75 - 90 = "75% improvement"
50 - 75 = "50% improvement";
run;

The below example is different one in which the proc format is used for the usual range values...

proc format; 
value rv
120 - high = '5'
60 < 120 = '4'
35 < 60 = '3'
25 < 35 = '2

25 = '1';

run;

What is IDE???

IDE/ Integrated development environment is a programming environment integrated into a software application. It incorporates a GUI builder, a code editor, a compiler and/or an interpreter & a debugger.

The basic features of this IDE for SAS include:

• Collection of edit macros and templates
• Creation of new templates
• SAS highlighting features
• Indention
• Fast commenting
• Expansion of keystrokes into SAS language constructs
• Development, testing, and fixing (with SAS/PC).

These built-in features in an "Integrated development environment" facilitates a programmer maximize his/ her efficiency and manage time with a fairly large work load.

More: http://multi-edit-2008.software.informer.com/11.2/