SAS
CDISC
SDTM
PRXCHANGE
intnx
compbl
--TERM
CMISS
%sysfunc
countw
%scan
%str
prxmatch
length
--DTC
datepart
input
delete
libname
filename
%substr
%length
retain
PROC SORT sortseq
merge
array
do
put
COMPBL
%do
%sysfunc
countw
How to use column Codelists[Codelist], Codelists[Code Text] and Codelists[CDISC_Submission_Value] from the spec file C-200-001_sdtm - Copy.xlsx
to create a SAS format named $CL_DEMS0101F
where the label and start variables take values from the Codelists[CDISC_Submission_Value] and Codelists[Code Text] and the format name is created by modifying the Codelists[Codelist]:
PRXMATCH()
to check the last character of a variable is a number PROC FORMAT
to export the created formats, informatsProblem Sorting a character variable that contains number results in data sorted textually. In the following code, the sortkey IDVARVAL is character in type. Sorted data undesirably place 10, 11, 12,…, 19 between 1 and 2.
PROC SORT sortseq=linguistic(numeric_collation=on);
to sort data numericaly without changing the sort keys
WHERE
to first filter out missing values and then use IF last.var
statement to locate the last non-missing value of a variable within the BY
group, creating a temp dataset. Try to specify all the subsetting conditions in the WHERE
statement. The results can be different if the conditions are specified in the IF
statement.MERGE
to join the temp dataset back to the working datasetPRXCHANGE Function
PRXCHANGE( perl-regular-expression | regular-expression-id, times, source)
Required Arguments
perl-regular-expression specifies a character constant, variable, or expression with a value that is a Perl regular expression. s/pattern-to-look-for/pattern-to-replace/
regular-expression-id specifies a numeric variable with a value that is a pattern identifier that is returned from the PRXPARSE function. Restriction If you use this argument, you must also use the PRXPARSE function.
times is a numeric constant, variable, or expression that specifies the number of times to search for a match and replace a matching pattern. Tip: If the value of times is –1, then matching patterns continue to be replaced until the end of source is reached.
source specifies a character constant, variable, or expression that you want to search.
COMPRESS Function
COMPRESS(variable, character-expression[, character-list-expression] [, modifier(s)])
Arguments
character-expression specifies any valid expression that evaluates to a character expression and from which specified characters will be removed. Requirement: Enclose a literal string of characters in single quotation marks. Data type: CHAR, NCHAR, NVARCHAR, VARCHAR. See DS2 Expressions in SAS DS2 Programmer’s Guide
character-list-expression specifies a variable or any valid expression that initializes a list of characters. By default, the characters in this list are removed from character-expression. Requirement: Enclose a literal string of characters in single quotation marks. Data type: CHAR, NCHAR, NVARCHAR, VARCHAR. See DS2 Expressions in SAS DS2 Programmer’s Guide.
modifier specifies a character constant, variable, or expression in which each non-blank character modifies the action of the COMPRESS function. Blanks are ignored. The following characters can be used as modifiers:
PRXCHANGE()
COMPRESS(x,,
kad)
Example 1: Compressing Blanks
Example 2: Compressing Uppercase Letters
Example 6: Compressing Space Characters by Using a Modifier
Example 7: Keeping Characters in the List by Using a Modifier
%substr()
PRXMATCH()
length()
BY
RETAIN
compress()
PROC IMPORT
LIBNAME libref XLSX "excel-file-path"
assigns a short name, libref, to an Excel file path. SET libref.sheetname
reads the sheetname into SAS.FILENAME filref 'file-path'
assigns a short fileref to a filepath. PROC IMPORT
LIBNAME xport
PROC COPY
PROC SORT
PROC TRANSPOSE
MERGE
PROC DATASETS delete
ARRAY
do
dim()
%do
%sysfunc()
countw()
%scan()
%STR()
%end
Write one set of IF-ELSE statement per VieDoc numeric variable to apply format to them to create SAS character variablesCMISS()
COALESCEC()
compbl()
scan()
length()
proc transpose
The goal is to split CO.COVAL into CO.COVAL, CO.COVAL1, CO.COVAL2.compbl()
scan()
length()
proc transpose
The goal is to split DS.DSTERM into CO.DS.DSTERM, DS.DSTERM1, and DS.DSTERM2. The input data is WORK.DSSTDTC_EPOCH_2. The variable to split is DSTERM. The newly created columns COL1 storing first 200 characters, COL2 storing second 200 characters and COL3 storing third 200 characters. A possible mistake is to create DSTERM setting its length to 200, which COL2 and COL3 won't be generatedI have investigated why my QSDTM.QC_SE (440 obs) has one more obs than SDTM.SE (439 obs). The commentary in my program shows they were compared equal before. The obs that is not found in SDTM.SE is highlighted:
I have checked how Mel derived TAETORD in her se.sas as well as the spec for C-100. Mel inner joined SDTM.DM and SDTM.SE by USUBJID to bring ETCD into a working dataset. Then TAETORD is sourced from SDTM.TA by a left join of the dataset with SDTM.TA by ETCD=ETCD. That is quite different from my program that derives TAETORD in different subsets where each subset has 1 TAETORD value and stacks them as one dataset. Maybe my subset with TAETORD=3 is not correctly programmed on my side. Can you recall what variables are involved in the Condition to create TAETORD=3 as highlighted below? That is from the SE_Meta sheet.
Here is how I create TAETORD=3. First, SDTM.DM is left joined with SDTM.TA by ARMCD=ARMCD:
then a subset with TAETORD=3 is created by the where= clause as highlighted:
I am not sure if the subsetting condition has done 100% of the condition in the spec. Can you take a look?
I have attached the spec file, Mel's se.sas and my qc_se.sas
\\sas\repos\Surface_C-200-001\source\meta\C-200-001_sdtm.xlsx
and make a copy to \\sas\repos\Surface_C-200-001\spec\sdtm\C-200-001_sdtm.xlsx
. Also make a copy to https://greenlightclinical.sharepoint.com/:f:/r/teams/Stats/Projects/Noymed_sponsor_folder/Surface_C-200-001/Stats/1103_Analysis/110301_Data Definitions for A Datasets/SDTM/Specs/C-200-001_sdtm.xlsx
so that NoyMed has the latest.