Now, let's go back to the four major folders and look at the Programmes folder:
The Programmes folder contains the program we have for this project.
There are five sub-folders:
Now, click and expand "1. Setup" folder.
There is a SAS program named "Setup":
Open the program and copy and paste it in your SAS Studio.
This program will guide complete the setup.
Please follow the steps closely to import the files properly.
Step 1: Assign the home directory to the macro variable
In Step 1 of the program, you should see the code below:
options mprint mlogic ;
** Step 1: Change the root path below **;
** For SAS OnDemand for Academics users, change the root to your home directory (see instruction below) **;
** For Other SAS users, change the root to the folder where you unzip the downloaded folder **;
%let root = /home/your_user_name;
The code above assigns the home/root directory to the macro variable.
Your root directory is unique.
If you are using SAS OnDemand for Academics, please right click on the Files (Home) folder under Server Files and Folders, and click properties:
A window will pop up with the path to the home directory. Assign the path to the macro variable:
Run the SAS code to complete Step 1.
If you are using any other version of SAS, the root directory is wherever you unzipped folder:
Step 2: Create the folders on SAS Studio
In Step 2 of the program, we have the code below:
** Step 2: Creating new folders and libraries **;
options dlcreatedir;
libname ROOT "&root./clinical_project_part_1";
libname CDM "&root./clinical_project_part_1/CDM";
libname SDTM "&root./clinical_project_part_1/SDTM";The code above will create the folders on SAS Studio.
Run the code. You will see the clinical_project_part_1 folder being created, along with two sub-folders:
The two sub-folders are CDM and SDTM.
Step 3: Upload the data files to the CDM folder
In Step 3, there is no code to run. However, you need to upload the files to the CDM folder.
First, right click on the CDM folder and click Upload Files:
Now, select the seven Excel data files that part of the download earlier. They are located in the Data - CDM folder:
Upload them all to the CDM folder on the SAS server.
Step 4: Creating the CDM and SDTM data sets
The data files have now been uploaded to the SAS server.
We will create the required CDM and SDTM data sets.
Below is the code to create the data sets:
** Step 4: Creating SAS data sets **;
/*CDM datasets*/
%macro CDM (Domain= ) ;
PROC IMPORT DATAFILE= "&root./clinical_project_part_1/CDM/&Domain..xlsx"
DBMS=XLSX OUT= CDM.&domain ;
GETNAMES=YES;
RUN;
%mend CDM ;
%CDM (Domain = DEATH ) ;
%CDM (Domain = DM ) ;
%CDM (Domain = DS ) ;
%CDM (Domain = EX ) ;
%CDM (Domain = IE ) ;
%CDM (Domain = SPCPKB1 ) ;
/*SDTM datasets*/
%macro SDTM (Domain= ) ;
PROC IMPORT DATAFILE= "&root./clinical_project_part_1/CDM/&domain..xlsx"
DBMS=XLSX OUT= SDTM.&domain ;
GETNAMES=YES;
RUN;
%mend SDTM ;
%SDTM (Domain = TA ) ;Run the macro above. It will import the Excel sheets and create the SAS data sets.
Where are the SAS data sets?
Go down to Libraries folder and you will find two new libraries created:
- CDM, which contains six data sets.
- SDTM, which contains one data set.
Done! We now have all the required data sets required for this CDISC project!
Program Explanation
The program involves mostly macros, but it is not as complex as it seems.
Let's look at each line, one-by-one.
Initial Option Statement
The initial OPTIONS statement specifies the MPRINT and MLOGIC options.
options mprint mlogic;These options allow more information to be written to the SAS log when processing macros.
It is intended for debugging purposes.
Creating the ROOT macro variable
The ROOT macro variable is created using the %LET statement.
** Step 1: Change the root path below **;
** For SAS OnDemand for Academics users, change the root to your home directory (see instruction below) **;
** For Other SAS users, change the root to the folder where you unzip the downloaded folder **;
%let root = /home/your_user_name;The macro variable identifies the location of the root directory.
This macro variable is extremely important.
Once the root directory is identified, SAS will be able to import any data file from any of the sub-folders.
Note: if you are not using SAS Studio, you can change the root directory to the proper folder.
Creating Libraries
Since we have multiple folders for the CDM and SDTM data sets, we are going to create multiple libraries:
** Step 2: Creating new folders and libraries **;
options dlcreatedir;
libname ROOT "&root./clinical_project_part_1";
libname CDM "&root./clinical_project_part_1/CDM";
libname SDTM "&root./clinical_project_part_1/SDTM";The libraries are created using the LIBNAME statement.
It connects to the CDM and SDTM folders that we created earlier.
Importing CDM Data Files
The LIBNAME statement creates the libraries. However, the Excel files still need to be imported.
The rest of the program includes two macro programs that import the Excel data files into SAS data sets:
** Step 4: Creating sas data sets **;
/*CDM datasets*/
%macro CDM (Domain= ) ;
PROC IMPORT DATAFILE= "&root./clinical_project_part_1/CDM/&Domain..xlsx"
DBMS=XLSX OUT= CDM.&domain ;
GETNAMES=YES;
RUN;
%mend CDM ;
%CDM (Domain = DEATH ) ;
%CDM (Domain = DM ) ;
%CDM (Domain = DS ) ;
%CDM (Domain = EX ) ;
%CDM (Domain = IE ) ;
%CDM (Domain = SPCPKB1 ) ;The code here includes a macro program named CDM.
The CDM macro takes on one parameter named DOMAIN.
Inside the macro program is the proc import code.
This program allows you to systematically import the six Excel files (i.e., DEATH, DM, DS, EX, IE and SPCPKB1) into SAS.
Importing SDTM Data Files
The rest of the program includes another similar macro program.
It is used to import the SDTM data file named TA.
/*SDTM datasets*/
%macro SDTM (Domain= ) ;
PROC IMPORT DATAFILE= "&root./clinical_project_part_1/CDM/&domain..xlsx"
DBMS=XLSX OUT= SDTM.&domain ;
GETNAMES=YES;
RUN;
%mend SDTM ;
%SDTM (Domain = TA ) ;As mentioned earlier, the program imports the data files into SAS:
We now have the SAS data sets ready to create the DM SDTM data set!