Options VALIDVARNAME=UPCASE

-
VALIDVARNAME= V7 UPCASE ANY

VALIDVARNAME= option is generally used in SAS whenever we want to control the SAS variable names in the dataset.

VALIDVARNAME= V7 UPCASE ANY

The default option will be VALIDVARNAME=V7 until we specify as UPPERCASE or ANY.

When we mention options VALIDVARNAME=V7, that means we are telling SAS to change the name of the Database column (etc EXCEL sheet column) to valid SAS name with certain rules keeping in mind.

Here are those rules that SAS needs to follow, when it changes the DBMS column name to valid SAS name.

Only 32 mixed case (lower or uppercase) characters are allowed in each variable.

Names should be starting with an underscore or an alphabet (either uppercase or lower case character).

Invalid characters in the DBMS column (ex. $) should be changed to underscores.

See the SAS Language Reference: Dictionary to get more details about the rules.

VALIDVARNAME=UPCASE
When we mention options VALIDVARNAME=UPPERCASE we are telling SAS to change the column name of the Database column to uppercase variables irrespective of type of variables in the DBMS column.


And whenever we want the same kind of characters in SAS dataset which are in the DBMS column (ex .(=) sign and the Asterisk(*) or the forward slash(\) we have to mention options

VALIDVARNAME=ANY
If we do, this will allows any characters which are in the DBMS column to be kept in the SAS dataset.

To understand the concept better here I am giving the example:

Example
The following example shows how the Pass-Through Facility works with
VALIDVARNAME=UPPERCASE.

options validvarname=uppercase;
proc sql;
connect to oracle as tables(user=USERID orapw=passward path=’INSTANCE’);
create table lab as
select lab_rslt, lab_test
from connection to oracle
(select "laboratory result$", "laboratory test$"
from DBMStable);
quit;

When we check the Output we observe that the variables in the DBMS column is changed to upper case as well as V7 (default option) converts those variables into UPPERCASE variables. Ex: " laboratory result$" becomes LAB_RSLT and " laboratory test$" becomes LAB_TEST.

Comments

  1. karthikFebruary 10, 2009 at 8:25 AM

    hi
    can you let me know how we can include id variable representing the no. of observations in proc sql.

    what i'm saying is the equivalent step in sql for the following code

    data a;
    set a;
    id=_n_;

    ReplyDelete
  2. sarathFebruary 15, 2009 at 6:35 PM

    Proc sql;
    create table a1 as
    select monotonic() as ID,
    from a;
    quit;

    as we are using here proc sql, the output dataset name should be different than the input dataset.
    so instead of keeping 'a' as the output dataset I've used a1.

    sarath

    ReplyDelete
  3. UnknownApril 1, 2009 at 5:55 AM

    sarath, why shuld we use proc sql for combining datasets? instead why shuld'nt we use combing data sets by concatinating mergeing and interleaving etc.. tell me in which cases we use proc sql and in which cases we use merging, interleaving etc....

    ReplyDelete
  4. sarathApril 7, 2009 at 4:43 PM

    Neelima...

    we can use.. Proc sql... for almost all the situations....(not only merging/Interleaving)

    whatever you do.. with the SAS datastep.. you can achive the same result .. using PROC SQL....

    PROC SQL ..... is sleak and simple...

    There is no.. such .. situation.. where .. we have to use ... PROC SQL... infact .... Many of my co-workers(even though they have 5-6 yrs exp min...always hesitate to use PROC SQL...

    ReplyDelete
  5. UnknownNovember 28, 2012 at 12:50 AM

    hai can anyone tell me , how can we get the attributes of first five datasets in library?

    ReplyDelete
  6. sarathNovember 28, 2012 at 11:23 PM

    Hi Sudeshna,

    Here is the automated macro code to get the attributes of first five datasets from any library.

    libname sdtm 'G:\SASDATA\_DIS\P9002_0109\Validation\data';

    data contents(compress=no); *Create a dummy dataset;
    run;

    %macro cmpare (lib,n);
    data _null_;
    set sashelp.vstabvw;
    where libname="&lib" and memname ne " ";
    call symput ('dsname'||left(_n_), trim(left(memname)));
    run;

    %do i=1 %to &n;
    proc contents data=sdtm.&&dsname&i. out=&&dsname&i. noprint;
    run;

    data contents;
    set &&dsname&i. contents ;
    keep memname name label length type format informat;
    if memname ne '';
    run;
    %end;


    %mend cmpare;
    %cmpare(lib=SDTM,n=5);

    SDTM is the library name and n=5 is the number of datasets whose atrributes we want to check.

    All the best.

    Sarath

    ReplyDelete

Post a Comment

Popular posts from this blog

SAS Interview Questions and Answers: CDISC, SDTM and ADAM etc

-
1) What do you know about CDISC and its standards? CDISC stands for Clinical Data Interchange Standards Consortium and it is developed keeping in mind to bring great deal of efficiency in the entire drug development process. CDISC brings efficiency to the entire drug development process by improving the data quality and speed-up the whole drug development process and to do that CDISC developed a series of standards, which include Operation data Model (ODM), Study data Tabulation Model (SDTM) and the Analysis Data Model ADaM ). 2) Why people these days are more talking about CDSIC and what advantages it brings to the Pharmaceutical Industry? A) Generally speaking, Only about 30% of programming time is used to generate statistical results with SAS®, and the rest of programming time is used to familiarize data structure, check data accuracy, and tabulate/list raw data and statistical results into certain formats. This non-statistical programming time will be significantly reduced...
Read more

Comparing Two Methods for Removing Formats and Informats in SAS: DATA Step vs. PROC DATASETS

-
Comparing Two Approaches to Removing Formats and Informats in SAS Comparing Two Approaches to Removing Formats and Informats in SAS When working with SAS datasets, there are times when you need to remove formats and informats that have been previously assigned to variables. Two primary approaches can be used for this task: Using the DATA Step Using the PROC DATASETS Procedure This article compares and contrasts these two approaches to help you determine which method is most appropriate for your needs. Approach 1: Using the DATA Step The DATA step is a versatile and commonly used method for removing formats and informats. By assigning variables to a null format or informat, you can effectively remove these attributes from your dataset. Example: data mydata_clean; set mydata; format _all_; informat _all_; run; In this example, the mydata dataset is processed in the DATA step, and...
Read more

Studyday calculation ( --DY Variable in SDTM)

-
USE OF THE “STUDY DAY” VARIABLES The permissible Study Day variables (--DY, --STDY, and --ENDY) describe the relative day of the observation starting with the reference date as Day 1. They are determined by comparing the date portion of the respective date/time variables (--DTC, --STDTC, and --ENDTC) to the date portion of the Subject Reference Start Date (RFSTDTC from the Demographics domain). The Subject Reference Start Date (RFSTDTC) is designated as Study Day 1. The Study Day value is incremented by 1 for each date following RFSTDTC. Dates prior to RFSTDTC are decremented by 1, with the date preceding RFSTDTC designated as Study Day -1 (there is no Study Day 0). This algorithm for determining Study Day is consistent with how people typically describe sequential days relative to a fixed reference point, but creates problems if used for mathematical calculations because it does not allow for a Day 0. As such, Study Day is not suited for use in subsequent numerical computations...
Read more