StudySAS Blog

Exploring the Analysis Data Model – ADaM Datasets

2013-03-14T12:15:00.003-04:00

Today, I stumbled upon a blog which is interesting and resourceful. I liked the article so much so want to share with all my friends here. Here is the direct link for the post to download or to review: Actual Article: The Analysis Data Model (ADaM) is a standard released by the Clinical Data Interchange Standards Consortium (CDISC) and has quickly become widely used in the submission of clinical trial information. ADaM has very close ties to another of CDISCs released standards, Study Data Tabulation Model (SDTM). The main difference between these two CDISC standards is the way in which the data is displayed. SDTM provides a standard for the creation and mapping of collected data from Raw sources, where as ADAM provides a standard for the creation of analysis-ready data, often using SDTM data as the source. The purpose of the analysis-ready ADaM data is to provide the programmer with a means to create tables, listings and figures with...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

How to use MISSING(), NMISS() and the CMISS() functions

2013-01-26T03:47:00.003-05:00

SAS provides several functions to test for missing values but in this post we will focus on MISSING(), CMISS() and NMISS() functions. The NMISS() function is reserved for numeric variables. The MISSING() and CMISS() functions can be used with either character or numeric variables. The CMISS() and NMISS() functions are designed by SAS to count the number of arguments with missing values whereas the MISSING function checks whether or not a variable is missing. The MISSING(), CMISS(), and NMISS() functions provide a simple approach to check for missing values and these functions let you write few lines of code by avoiding large if-statements when you need to check for missing values in several values at the same time. MISSING() function is very useful when you need to check any variable has a missing value or not, but not sure if it’s a character or numeric? MISSING function works for either character or numeric variables and it also checks for the special...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

Studyday calculation ( --DY Variable in SDTM)

2013-01-12T13:36:00.001-05:00

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...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

Creating Custom or Non-Standard CDISC SDTM Domains

2012-11-29T14:32:00.002-05:00

Here is the nice article about creating custom SDTM domains......... Creating Custom or Non-Standard CDISC SDTM Domains Within the Clinical Data Interchange Standards Consortium (CDISC) Study Data Tabulation Model (SDTM), standard domains are split into four main types: special purpose, relationships, trial design and general observation classes. General observation classes cover the majority of observations collected during a study and can be divided among three general classes: The Interventions class captures investigational, therapeutic and other treatments that are administered to the subject (with some actual or expected physiological effect) either as specified by the study protocol (e.g., “exposure”), coincident with the study assessment period (e.g., “concomitant medications”), or other substances self-administered by the subject (such as alcohol, tobacco, or caffeine). The Events class captures planned protocol milestones such as randomization and study...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

ENCODING=Dataset Option

2012-06-20T15:40:00.000-04:00

Let me explain the reason writing this post…. My coworker was having problem reading in a SAS dataset that he got from the Sponsor. It was a SAS dataset encoded with UTF-8 and other coding related stuff. When he tried to get in the rawdata using Libname statement libname rawdata ‘/sas/SAS913/SASDATA/CLIENT /ABC123/raw’; data datasetname; set rawdata.datasetname; run; When he runs the SAS code above, SAS stops at the current block, and returns an error that looks like this: ERROR: Some character data was lost during transcoding in the dataset RAWDATA.DATSETNAME. NOTE: The data step has been abnormally terminated. NOTE: The SAS System stopped processing this step because of errors. NOTE: SAS set option OBS=0 and will continue to check statements. This may cause NOTE: No observations in data set. NOTE: There were 20314 observations read from the data set RAWDATA.DATSETNAME. WARNING: The data set WORK.DATASETNAME may be...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

Create a .CSV file of SAS dataset without column names or header row?

2012-06-12T15:32:00.000-04:00

SAS places the variables names in Row 1 when you try to create an excel or .CSV file of the SAS dataset. I have found a tip to tell SAS not to keep variable names in the row 1 of .CSV file. SAScommunity.org page has put together nice information regarding how to do this. 1 Run PROC EXPORT with PUTNAMES=NO 2 Run PROC EXPORT and recall and edit the code 3 Run PROC EXPORT and use a DATA step to rewrite the file without the first row 4 DATA _NULL_ with a PUT statement 5 DATA _NULL_ with a PUT statement, all fields quoted 6 ODS CSV and PROC REPORT with suppressed column headers 7 The %ds2csv SAS Institute utility macro 8 The CSV tagset and the table_headers="NO" option Run PROC EXPORT with PUTNAMES=NO Sample program proc export data=data_to_export outfile='C:\data_exported.csv' dbms=csv ...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

ERROR 29-185: Width Specified for format ---- is invalid

2012-06-07T03:03:00.002-04:00

You see "ERROR 29-185: Width Specified for format ---- is invalid" message in the log file when you try to specify the DATE format but used an invalid width. DATE format will not result in a date if it is too long or too short. Valid values are 5-9 in SAS 9.1.X versions. If you use newer version (SAS 9.2) then you won't see this Error message in the log. ( I am assuming that this is fixed in SAS 9.2). Try using format date9. instead of date11. if you are using SAS 9.1.x (either Windows or Unix) version. data _null_; date ='23-SEP-2004'd; put date date11.;*This statement gives you error in SAS 9.1.2/9.1.3 versions; put date date9.; run;

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

My 5 Important reasons to use Proc SQL

2012-05-19T07:40:00.001-04:00

• Proc SQL requires few lines of SAS code compared with datastep and or Proc steps • Frequency counting can be done in no time… which is very helpful during the QC or validation • Proc SQL can merge datasets together using different variable names unlike datastep. • Proc SQL can merge many datasets together in the same step on different variables • Proc SQL allows you to join more than two datasets together at the same time on different levels • The merge process Proc SQL join does not overlays the duplicate by-column, where the Merge statement of the data step does. Data step vs Proc SQL • Data step Merge– Pre sorting of the dataset by the by-variable needed before the merging process – Requires common variable names – May need few more lines of code than Proc SQL • PROC SQL Join process works different than the...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

Transcoding Problem: Option (correctencoding=wlatin1)

2012-02-24T19:01:00.001-05:00

Have you ever tried to convert the default encoding to Wlatin1 (Windows SAS Session Encoding)? Let me tell you the story behind writing this post…. Today I was asked to send SAS datasets to one of the client. I transferred the SAS datasets to the client and immediately after, I got an email from the so called client saying the encoding of SAS datasets is different this time when compared with the last transfer. He said It’s causing problems in Proc compare process. Opps… bummer…. Client’s email got me little worried ...I checked the Proc contents details and saw the change in the encoding. I investigated the issue and found out that Unicode SAS with UTF-8 encoding uses 1 to 4 bytes to handle Unicode data. It is possible for the 8-bit characters to be expanded by 2 to 3 bytes when transcoding occurs, which causes the truncation error. Because of the truncation problem I was asked to change the unicoding back to WLATIN1 so that the character data present in the SAS datasets...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

SDTM Compliance Checks

2011-11-13T00:37:00.001-05:00

Validation checks or tools to check the compliance of SDTM data JANUS is a standard database model which is based on the CDISC’s SDTM standard. JANUS is used by the FDA to store the submitted SDTM clinical data. As a part of data definition file submission pharmaceutical companies have to submit SAS datasets in transport file (.xpt) format along with annotated CRF and Define.xml file. The reason being this is… to properly load the clinical data into JANUS database which is maintained by the FDA. It is very easy for FDA reviewers to review the clinical data once they load the clinical data into their JANUS database. They can even produce ad-hoc reports and perform cross-study review at the same time. FDA runs compliance checks on the data submitted to make sure the data was collected as per the SDTM standard. FDA checks the compliance of data by running the WebSDM™ developed by PhaseForward). WebSDM™ is a SDTM compliance check validation tool performs a set of SDTM...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

How to remove carriage return and linefeed characters within quoted strings.

2011-10-27T16:23:00.003-04:00

HANDLING SPECIAL EMBEDDED CHARACTERS To manage and report data in DBMS that contains very long text fields is not easy. This can be frustrating if the text field has special embedded symbols such as tabs, carriage returns (‘OD’x ), line feeds (‘OA’x) and page breaks. But here is simple SAS code which takes care of those issues. The normal line end for Windows text files is a carriage return character or a line feed character so The syntax for taking out all carriage return ('OD'x) and line feed ('OA'x) characters is comment= Compress(comment,'0D0A'x); or comment= TRANWRD(comment,'0D0A'x,’’); If you just want to take out the Carriage Return, use this code: comment= TRANWRD(comment,'0D'x,''); You could also try this one too.. Comment=compress(Comment, ,"kw");*k is for keep, w is for...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

Counting the number of missing and non-missing values for each variable in a data set.

2011-10-13T10:19:00.000-04:00

/* create sample data */ data one; input a $ b $ c $ d e; cards; a . a 1 3 . b . 2 4 a a a . 5 . . b 3 5 a a a . 6 a a a . 7 a a a 2 8 ; run; /* create a format to group missing and non-missing */ proc format; value $missfmt ' '='missing' other='non-missing'; value missfmt .='missing' other='non-missing'; run; %macro lst(dsn); /** open dataset **/ %let dsid=%sysfunc(open(&dsn)); /** cnt will contain the number of variables in the dataset passed in **/ %let cnt=%sysfunc(attrn(&dsid,nvars)); %do i = 1 %to &cnt; /** create a different macro variable for each variable in dataset **/ %let x&i=%sysfunc(varname(&dsid,&i)); /** list the type of the current variable **/ %let typ&i=%sysfunc(vartype(&dsid,&i)); %end; /** close dataset **/ %let rc=%sysfunc(close(&dsid)); %do i = 1 %to &cnt; /* loop through each variable in PROC FREQ and create */ /* a separate output data set */ proc freq...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

When do I use a WHERE statement instead of an IF statement to subset a data set?

2011-08-17T10:07:00.000-04:00

When programming in SAS, there is almost always more than one way to accomplish a task. Beginning programmers may think that there is no difference between using the WHERE statement and the IF statement to subset your data set. Knowledgeable programmers know that depending on the situation, sometimes one statement is more appropriate than the other. For example, if your subset condition includes automatic variables or new variables created within the DATA step, then you must use the IF statement instead of the WHERE statement. This tip shows you how and when to apply the WHERE and IF statements to get correct and reliable results. It also reviews the similarities as well as the differences between these two SAS programming approaches. Detail differences in program efficiency between the two approaches will not be covered in this tip. For more details refer to http://support.sas.com/kb/24/286.html

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

Transporting SAS Files using Proc Copy and or Proc Cport/Proc Cimport

2011-07-04T11:36:00.011-04:00

When moving SAS datasets /catalogs from one type of computer to another, there are several things to be considered, such as the operating systems of the two computers, the versions of SAS and the type of communication link between the computers. The easiest way to move SAS datasets from one system to another system is to: Create a transport file using any SAS version.Move the transport file to the new system. Import the transport file on the new system. Transport datasets are 80-byte length binary files made from SAS datasets. PROC COPY or PROC CPORT can create Transport datasets but they both create different types of transport files. Transport files can be created and read using either PROC COPY or PROC CPORT & PROC CIMPORT, but you cannot mix and match. Transport files created with PROC COPY must be read with PROC COPY; those created by PROC CPORT must be read with PROC CIMPORT. PROC COPY uses an engine (i.e. XPORT) to create a SAS transport file. PROC COPY is used...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

How to generate the month name from a numeric date value

2011-06-14T16:34:00.004-04:00

Task: I have a SAS date and wanted to create a variable with the month name. Here is how to do it...... Use MONNAMEw. format which is simple and easy. You need to be using SAS 9.X versions to make it work. /*Use MONNAMEw. format*/ data month; input date:mmddyy8.; month_name=put(date,monname3.); datalines; 01/15/04 02/29/04 07/04/04 08/18/04 12/31/04 ; run; proc print; run;

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

ERROR: The MS Excel table (worksheetname) has been opened for OUTPUT.

2011-06-14T15:38:00.001-04:00

I happend to stumbleupon a post from SAS support blog regarding the ERROR message in the LOG file when trying to output a SAS dataset in the form of Excel sheet. Direct link: ERROR: The MS Excel table (worksheetname) has been opened for OUTPUT. This table already exists, or there is a name conflict with an existing object. This table will not be replaced. This engine does not support the REPLACE option. ERROR: Export unsuccessful. See SAS Log for details. When you use the EXPORT procedure on an Excel workbook, the workbook might be corrupted and the following error message generated: This problem can occur if a previous EXPORT procedure attempts to export a SAS data set in the workbook that does not contain any observations. The following example illustrates an export procedure on such a data set: %macro blowup; data a; a=1; stop; run; %do i=1 %to 2; proc export data=a outfile="c:\temp\test.xls" dbms=excel2000...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

How to read next record while working on the current record. (LEAD FUNCTION)

2011-02-06T15:08:00.004-05:00

Even though there is no function is available in SAS to do exactly the opposite work of the LAG function (i.e: reading the next record while working on the current one), there are few things you can do to do exactly that. Here are few simple techniques which are proved to work without any problem. *SAMPLE DATASET; data test; input id age grp ; datalines; 1 10 1 2 20 1 3 30 1 4 40 1 5 50 1 1 10 2 2 20 2 3 30 2 4 40 2 5 50 2 ; run; *1) Using the POINT feature along with automatic variable _N_; This solution was suggested by Paul M. Dorfman; data leads; _n_ ++ 1; if _n_ le n then do; set one point=_n_; leadage=age; end; set one nobs=n; run; *OR*; data leads; _n_ ++ _n_ lt n; set one point=_n_; leadage=age; set one nobs=n end=end; if end then leadage=.; run; By using the above techniques, you can jump a bit higher and even look values of two, three or any numbers of observations in advance by advancing the value of...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

STUDY 'DAY' CLCULATION (ONE-LINER)

2011-01-22T13:21:00.003-05:00

Recently I stumbled upon a SUGI-Paper SAS 1-Liners by Stephen Hunt. I liked the way Stephen developed the 1-liner for STUDY DAY calculation. Direct link: One of the most common calculation used across all types of programming is determining a relative 'day' based on 2 date fields. In clinical trials the initial 'Study Day' is generally considered to begin at either randamization or dosing, thus assessments made prior to this starting point require a slight variation in the calculating in order to preserve the typical 'no day 0' concept. SUGI proceedings10/054-2010.pdf When it comes to calculating such, some programmers opt to both with evaluating whether a visit date occurred on or /after randomization. if visdt > randdt >.z then stydy=visdt-randdt; else stydt=visdt-randdt+1; However, this is unnecessary, sine the 1-liner will suffice: if visdt >.z & randdt >.z then stydy=visdt-randdt+(visdt>=randdt); Thanks...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

Easy way to UPCASE variable names of SAS dataset

2010-12-05T17:39:00.011-05:00

option VALIDVARNAME=UPCASE; Use trhe above option statement to upcase the variable name of the SAS dataset irrespective of type of variable in the dataset (character or numeric). The following example shows how the option sattement VALIDVARNAME=UPCASE works. proc contents data=sashelp.class out=test; run; Note: Propcase variable names. *Upcasing the variables; option validvarname=upcase; proc sort data=sashelp.class out=test; run; Because of the option statement. Ex: 'Age' becomes 'AGE' and 'Height' becomes 'HEIGHT' etc. See the SAS Language Reference dictionary to get more details. Another way to do this is to use a macro and I call it as UPCASE macro. proc sort data=sashelp.class out=test; by name; run; %macro upcase (lib,dsn); *Create a macro variable with the total number of variable count; data _null_; set sashelp.vtable(where=(libname="&LIB" and...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

MDY Function

2010-11-04T17:42:00.003-04:00

The MDY function converts MONTH, DAY, and YEAR values to a SAS date value. For example, MDY(10,19,1999) returns the SAS date value '19OCT99'D. Syntax: MDY(month,day,year) Arguments month : specifies a numeric expression that represents an integer from 1 through 12. day :specifies a numeric expression that represents an integer from 1 through 31. year :specifies a two-digit or four-digit integer that represents the year. The YEARCUTOFF= system option defines the year value for two-digit dates. If you know month, day, and year values, it’s very easy to derive date variable. You just need to use MDY function (of course, month, day, and year should be numeric). However, if the data is character then the conversion to numeric should occur first and then the conversion to the date value. Example: *When month, day, and year has numeric values; data...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

Overview on CDISC Implementation

2010-10-04T08:36:00.002-04:00

CDISC Advantages CDISC has developed a set of data standards to enhance data collection, management, analysis, and reporting efficiencies, improve safety monitoring, and streamline the review and approval process for investigational treatments. Under the ICH’s electronic Common Technical Document (eCTD) guidance, CDISC Study Data Tabulation Model (SDTM) is the preferred standard for content format and structure of clinical data for all clinical studies. Based on proposed federal regulations, the FDA will mandate that all clinical trial submissions be in electronic format and that the content comply with data standards guidance. Veristat helps our clients by not only implementing these standards on a project or program, but also by providing our clients with an understanding of the CDISC standards. source:veristatinc.com source: http://cro.businessdecision.com/ ('’)

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

How to convert the datetime character string to SAS datetime value? (ANYDTDTM and MDYAMPM formats)

2010-09-16T10:35:00.010-04:00

When we have a string like this "9/01/2010 11:52:54 AM" and would like to translate the string to a numeric SAS date time variable, most of the times we use SCAN function to extract the information to get the DATETIME format. This is definitely a tedious job. SAS formats (MDYAMPM, ANTDTDTM) comes to rescue us. Here is how it works. data test; length date $25; date="9/01/2010 11:52:54 AM"; *Convert the character string to SAS datetime value; datetimevar =input(date,mdyampm25.2); datetimevar1 =input(date,anydtdtm20.); *Apply format to the SAS date time value; format datetimevar datetimevar1 datetime19.; run; Result: 01SEP2010:11:52:54 *ANYDTDTM and MDYAMPM informats work together when the datetime value has AM PM specified or day, month, and year components are not ambiguous. The MDYAMPMw. format writes datetime values with separators in the form mm/dd/yy hh:mm AM PM, and requires a space between the date and the time. The ANYDTDTM w. format writes...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

SAS Keyboard Shortcuts

2010-09-12T08:04:00.002-04:00

Here are the few shortcuts you need to know to speed up the code writing. These work in both EPG (Enterprise Guide) and SAS Enhanced Editor. Shortcuts and their descriptions: Remember that the keyboard shortcuts listed here are default. Selection Operations:1) Comment the section with line comments (/): press CTL + / 2) Undo the comment: press CTL + SHIFT + / 3) Convert selected text to lowercase: press CTL + SHIFT + L 4) Convert selected text to uppercase: press CTL + SHIFT + U Shortcuts (pre-defined) CTRL+Shift+L or +U (only for the enhanced editor), which convert all selected text into lowercase or uppercase respectively. These become very handy when we insert the text by copy+paste. 5) Indent selected section: press TAB 6) Un-indent selected section: press SHIFT + TAB 7) To move curser to the matching DO/END statement: press ALT + [ or ALT + { or ALT+]...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

How to create Global or Local macro variables: (%Global / %Local)

2010-09-10T09:34:00.002-04:00

Global Macro Variables:The global macro variable gets created during the initialization of a SAS session and its get deleted at the end of the session. Global macro variable cane be created with 􀀀 a %LET statement (used outside a macro definition) 􀀀 a DATA step that contains a SYMPUT routine 􀀀 a DATA step that contains a SYMPUTX routine (New with SAS 9) 􀀀 a SELECT statement that contains an INTO clause in PROC SQL 􀀀 a %GLOBAL statement. Global macro variables include the following: *all automatic macro variables except SYSPBUFF. See Automatic Macro Variables for more information about SYSPBUFF and other automatic macro variables. *macro variables created outside of any macro. *macro variables created in %GLOBAL statements. See Creating Global Macro Variables for more information about the %GLOBAL statement. *most macro variables created by the CALL SYMPUT routine. See Special Cases of Scope with the CALL SYMPUT Routine for more information about...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

How to get the details of formats from the format libraries:

2010-08-26T15:56:00.003-04:00

If you are like me wanted to get the details of formats stored in the format library, here are two easy ways ….Proc catalog or Proc format Proc Catalog: Proc catalog will give us the details about name and type (numeric or character) of formats Syntax: proc catalog catalog = frmtdir.formats; contents; run; *FORMATS, is the name of the folder where all permanent formats are stored inside the library FRMTDIR. *If you want to get the details of temporary formats use WORK in place of frmtdir.formats; Proc Format: Use either SELECT or EXCLUDE statements to choose the formats for which you want to get the details. Syntax: proc format library = frmtdir.formats; select locfmt; run; This code will provide the details of LOCFMT in the FRMTDIR library. Below code gives complte information about what's stored in your format catalogs. libname frmtdir 'c:\saswork'; proc format library=frmtdir.formats cntlout = formats; run; Look at the...

[[ This is a content summary only. Visit my website for full links, other content, and more! ]]