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"

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;

How user can select values from a dynamic list

In SAS 9.1, the GUI prompts were not able to read the values inside a dataset dynamically. For example, let us consider a dataset named Unique Hour (UNQ_HR) which contains a variable named Unique Category Description (unq_catg_desc). Incase if Unique Category Description of this dataset gets updated every month and if it is used in GUI Prompts, then for every other month the SAS User has to follow the static "uploading of values" in GUI Prompt from the updated datasets (so that the GUI reflects updated values of dataset).

But SAS 9.2 gives a solution/ flexibility for dynamically reading updated values present inside a dataset.

SAS EG 9.1: dynamic UI cannot be created, below is the way in which a static list can be updated

1. Open the XYZ.egp

2. Click on Tools on the top right corner of the SAS EG and select Parameters Manager.

3. The Parameters Manager Window opens up showing the lists of GUI Parameters. Select "unq_catg_desc" and click on the Edit button.

4. The Edit button opens up the Edit Parameter Definition Window. Click on Data Type and Values tab towards the right.

5. The Load Values button is located at the bottom left corner. Click on it to select the SAS Server option.

6. Click on the Servers Icon (located towards the left).

7. Navigate to the path: Servers - SAS Main - Libraries - ABC

8. Locate the UNQ_HR Dataset in ABC Library and click Open button.

9. The column names of the dataset are displayed in the Select Column Window. Select the unq_catg_desc Column Name and click OK.

10. Click on Save and Close.

11. Click on Yes when the Parameters Manager Window asks.

12. Click on Close button to proceed with execution.

SAS EG 9.2: dynamic UI can be created

Pre requisite to create a dynamic UI –

We need to have the respective table in any of the path of Data source in Edit Prompt. If it is not there, then we have to register the required table in required path through SAS DI Studio.

Steps to create dynamic UI –

Step 1: Fill below param’s highlighted in red
Method for populating promt - User selects values from a dynamic list
Number of values - Multiple ordered values
Data source - The path (the path in which the dynamically readable dataset and it value is present)

Step 2: In Data source tab locate the physical path of where that table is (in our case it is ABC.unq_hr)

Step 3: Point to the table and column. Say o.k

User Defined Error & Messages to SAS log

/*throw error when excel input contains duplicate values*/
proc sort data = excel_read_in out = dedup
dupout = dups nodupkey;
by str_i fcst_acct_yr_i fcst_acct_mo_i fcst_acct_mo_wk_i;
run;

proc sql noprint;
select count (*) into : nobs_err1 from dups;
run;
%put &nobs_err1;

DATA _NULL_;
%IF &nobs_err1 > 0 %THEN %DO;
PUT "ERROR: The excel sheet is loaded with duplicate values. Please have a look at rmtwork.dups dataset and eliminate duplicate values in excel sheet.";
ABORT;
%END;
RUN;

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;