Output a dataset from proc contents!

proc contents data = check

out = check1(keep=name format label length name type varnum);

run;

Many of us use the procedure: proc contents but only few make use of the resulting dataset. The output dataset of proc contents helps the user visualize data items (in the form of a dataset). For example one can see the name, format, label, length, name, type, varnum etc.

Template for merging with SUPP's

%macro mer(seq, select);
data lb1;
set lb;
seq=&seq;
run;

data supp;
set supplb;
if strip(qnam)=&select;
seq=input(idvarval,best.);
run;

proc sort data=lb1; by usubjid seq;
proc sort data=supp; by usubjid seq;
data test;
merge lb1(in=a) supp(in=b);
by usubjid seq;
if a & b;
run;

proc sort data = test out = test1
dupout = dups nodupkey;
by studyid rdomain usubjid idvar idvarval qnam qlabel qval qorig qeval;
run;
%mend;

%mer(lbseq, 'CS');

Conversion of character date & time to numeric

The following macro dtcn helps in conversion of character date and time to numeric date and time.

%macro dtcn(indt,dtmn);
length dt1 $10 tm1 $6 dt1n $20;
if index(&indt,'T') ne 0 then do;
dt1 = scan(&indt,1,'T');
tm1 = scan(&indt,2,'T');
if dt1 ne '' and tm1 ne '' then dt1n = put(input(dt1,yymmdd10.),date9.)' 'trim(left(tm1));
if dt1 ne '' and tm1 ne '' then &dtmn = input(dt1n,datetime15.);
else if dt1 ne '' and tm1 eq '' then &dtmn =input(put(input(dt1,yymmdd10.),date9.),date9.);
end;
else &dtmn = .;
%mend;

data date;
xxstdtc='2009-01-02T17:30';
run;

data date1;
format xxstdtn datetime15.;
set date;
%dtcn(xxstdtc, xxstdtn);
run;

Find out sum

There are two possible ways to find out the sum of variable x.

data dummy;
input pt $1 seq x;
datalines;
1 1 6
1 2 1
1 3 .
2 1 4
3 1 .
4 1 .
4 2 2
5 1 3
6 1 .
7 1 3
;
run;
proc sort data=dummy; by pt seq; run;

1. One can either transpose the value of variable x (by identifier seq) & find the sum.

proc transpose data=dummy out=dummy1 prefix=_;
by pt;
id seq;
var x;
run;

data sumt;
set dummy1;
val=sum(_1,_2,_3);
run;

2. Or can use the following code to calculate it.

The code given below calculates the sum of variable x without transposing data:

data sum;
set dummy;
by pt;
retain val;
if first.pt then do;
*set the value to missing when it reads first.pt;
val = .;
*& finds where x is non-missing to fill the column val with first.pt value;
if x ne . then val = x;
end;
*does an: addition of two value & retain the same;
else if x ne . & val ne . then val = val+x;
*incase when first.pt is missing & other records of the same pt are available: then fills the column val with value of x;
else if x ne . & val eq . then val = x;
run;

data sum1;
set sum;
by pt;
if last.pt;
keep pt x val;
run;

A Fwd that I enjoyed reading...

A Priest, a Doctor and a CRO employee die and go to heaven,
they hear God's voice and it says:

“My dear sons I am really very sorry but the heaven is full and I can accommodate only one of you, so to choose the right person, one by one tell me what you have done in your lifetime.”

The Priest goes up first and says:

"Well God I'm a priest, I am your humble servant and have spent all my life working to spread your message."

The Doctor goes up next and says:

"Well I'm a doctor and I have helped thousands of people recover from their illnesses"

The CRO employee goes up says:

"Well I worked in ***** Clinical Research India Pvt Ltd and........", Before the CRO employee could say anything further, the heaven's gate opened and God came with tears in his eyes and said to the CRO employee… Say no more my son come with me, you have already been through HELL...

How to read variable names?

The system dataset SASHELP.VCOLUMN is a SAS view. This view is where the attributes (like: variable names, labels and formats) of a dataset are stored as the records of a dataset. This is like an output dataset from the CONTENTS procedure.

For example, in the code below: If the DM dataset is executed, the corresponding attributes of this dataset gets saved in a view called sashelp.vcolumn (which can later be set for analysis).

*** RUN DM DATASET ***;

data dm;
set dm;
run;

*** SET SASHELP.VCOLUMN TO READ VARIABLE NAMES ***;

data test;
set sashelp.vcolumn(where=(libname="WORK" and memname in ("DM"))) end=last;
if find (label, "Date"); *capture date variables;
run;

More: http://www2.sas.com/proceedings/sugi28/150-28.pdf

Checklists for validation

1) Log Check:

• Is the program log clean (with no errors, warnings, uninitialized values, character to numeric conversions and vice versa?)

2) Cosmetic Check:

• Does the output match mock up?
• Are all words spelled correctly?
• Does the number [1] in header match footnote number?
• Does each footnote end with a period except reference?
• Does the footnote refer to corresponding listing? (For Tables)
• Are all variables aligned: Numeric – Right & decimal aligned, Character – Left aligned?
• Is there any truncation for character variables?
• Are ordering / display of subtitle consistent across all listings?
• Does 5 subjects in QC program (a random pick) match output?

3) Logic Check:

• Are the TLFs subset for right population?
• Does the number of records displayed match with QC program?
• Does the output content in each column relate to the annotated specification?
• Does the output make logical sense for the given data?
• Is there any duplicates in the given data?

Use of #BYVAL(variable-name)

How to customize our title display??? How do we insert a text in the title???

This option #byval helps the user insert text at the position it is placed in the title statement.

For example in the code below:

The use of #byval option with the variable TRTGRP in the BY statement (within a proc step) makes its dynamic value get displayed in the title. As result of which we get different treatment groups displayed in the title part.

title1 "Listing of Patient Population";
title2 "Treatment Group=#byval(trtgrp)";
proc report data=dummy nowd headline headskip spacing=1 missing;
by trtgrp;
column(subj rand saft ittp eval);

define subj/ order_ width = 20 left spacing = 0 "Subject" ;
define rand/ order_ width = 20 center "Randomized";
define saft/ display width = 20 center "Safety" ;
define ittp/ display width = 20 center "ITT";
define eval/ display width = 20 center "Evaluable";

break after subj / skip;
run;

More: http://www.lexjansen.com/pharmasug/2006/technicaltechniques/tt01.pdf

Labeling with proc transpose

data new;
input subject $ value $ id $ label $;
cards;
111 03 A One
112 03 A One
111 04 B Two
113 03 B Two
114 04 C Three
;
run;

proc sort data=new;
by subject;
run;

proc transpose data=new out=new1;
by subject;
id id;
var value;
idlabel label;
run;

The ID statement identifies a variable whose values will supply the SAS names for variables in the transposed data.

is "FLOW" not working?

Then try this code:

var = compress(temp,compress(temp,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890!@#$%^&*()_+?<>{}]["".,/= '));

This code compresses the invisible characters that are present in the variable named temp and it helps in perfect alignment of text with FLOW function.

Assigning GRADES

data assign;
set draft;
*temperature in celsius;
temp=temperature;

*INCREASE IN TEMPERATURE;
if (temp^= . & temp>= 38) then do;
if 38 <= temp<= 39 then grade= 'GRADE1'; else if 39 < grade=" 'GRADE2';"> 40 then grade= 'GRADE3';
end;
*DECREASE IN TEMPERATURE;
else if (temp^= . & temp<= 35) then do;
if 35 >= temp> 32 then grade= 'GRADE2';
else if 32 >= temp> 28 then grade= 'GRADE3';
else if temp<= 28 then grade= 'GRADE4'; end;

run;

Visit in Window???

data window;
set draft;
by usubjid visitnum;

if visitday ne . & visitday lt 0 then day= 0;
else day= visitday;
*bringing the preceding day using lag function;
preday=lag(day);
*identifying observation through lag of usubjid;
lagusid = lag(usubjid);
*(day-preceding day) gives the day difference;
if usubjid = lagusid then do;
if day ne . & preday ne . then daydiff=(day-preday);
end;

*VISIT WINDOW FOR SCREENING;
if day ne . & visitnum in (1) then do;
if day le 0 then viwind = 'Yes';
else viwind = 'No';
end;
*VISIT WINDOW FOR VISIT 1 2 & 3;
if daydiff ne . & visitnum in (2,3,4) then do;
if 5 le daydiff le 7 then viwind = 'Yes';
else viwind = 'No';
end;
*VISIT WINDOW FOR END OF STUDY;
if daydiff ne . & visitnum in (5) then do;
if 0 le daydiff le 1 then viwind = 'Yes';
else viwind = 'No';
end;
*VISIT WINDOW FOR FOLLOW-UP;
if daydiff ne . & visitnum in (6) then do;
if daydiff le 25 then viwind = 'Yes';
else viwind = 'No';
end;

run;

Capturing the data into a macro

-

For the display of (N=xx) in a summary table, we have to capture the data into a macro. There are two different methods for capturing the data into a macro. One is using PROC SQL & the other one is using CALL SYMPUT.

-

***** GETTING "N" FOR EACH TRT GROUP *****;

-

1) PROC SQL:

-

data total;

set main(where=(saf=1));

trtgrp = input(treat,best.);

output;

trtgrp = 3;

output;

run;

-

proc sql noprint;

select count(distinct usubjid) into : trt1 through : trt3 from total group by trtgrp;

quit;

%put &trt1 &trt2 &trt3;

-

2) SYMPUT:

-

proc freq data = total noprint;

tables trtgrp / out = deno(drop = percent) list missing;

run;

-

data _null_;

set deno;

if trtgrp=1 then call symput('trt1',trim(left(put(count,3.))));

if trtgrp=2 then call symput('trt2',trim(left(put(count,3.))));

if trtgrp=3 then call symput('trt3',trim(left(put(count,3.))));

run;

-

DUPOUT=option

-
The following data set contains duplicate observations for Patients (PT:- 01 & 02) :
-
PT NO
01 12
01 3
01 56
02 2
02 12
03 12
-
How does the dupout option work on this data?????
-
proc sort data = test out = dedup
-------------------------dupout = dups nodupkey;
--by pt;
run;

It works in a similar manner to nodupkey. Here the "duplicates are detected by the nodupkey option" & are directed into an output dataset with DUPOUT!

The dedup is an output data set that contains only the original observations.
PT NO
01 12
02 2
03 12
-
Where as the dups is an output data set that contains all duplicate observations.
PT NO
01 3
01 56
02 12
-

Use of SUBSTR with INDEX function

-
The syntax for substr function is:
SUBSTR(string, starting position, <length>)
-
It is easy to determine the starting position or the length of the string (if it contains DATE).
-
For example:
day=substr(date,1,2);

but the same substr function is not applicable if the string contains NAME. The reason is that the length of each name varies from one observation to the other. Hence the use of delimiters (like: "." ",") can be used to locate the position within the string.
-
Below is an example for the use of substr with index function:
-
*** INDEX IS USED TO FIND WHERE TO BEGIN & END A SUBSTRING ***;
-
data fullname;
length fname lname $20;
set nameds;
fname= substr(name, 1, index(name, ',') - 1);
lname = trim(left(substr(name, index(name, ',') + 1)));
run;
-
Output:
name----------------------- fname---- lname
SOMA, SUNDARAM---- SOMA---- SUNDARAM
SHIVA, SHEKARAN---- SHIVA---- SHEKARAN
-

Y FREQ ??? use MEANS...

-

No dummy dataset... No data doubling...

“Making of Table” has become much simpler using PROC MEANS procedure! PROC MEANS when used in combination with MULTILABEL format and PRELOADFMT option gives all possible values of a formatted variable.

For example: If there are two different treatment groups (1 & 2) in a table and if the third column has to display a total count of both the treatment groups (1+2), then the MULTILABEL format is used.

-

proc format;

value $tmt (multilabel)

'1'='trt1'

'2'='trt2'

'1','2' = 'Total';

run;

-

This option allows the user to define:

• overlapping ranges across labels and to
• assign another label to the same value

This format is very efficient when used in conjunction with the means procedure TO PRODUCE THE TOTAL COUNT. Not only the multilabel format but also the use of PRELOADFMT with COMPLETETYPES enhances the efficiency of a program in CREATING ALL POSSIBLE COMBINATIONS OF A VARIABLE.

Now let’s consider three different categories (‘MILD’, ‘MODERATE’ & 'SEVERE') for a variable AEREL and if the dataset does not contain one of the category (‘MODERATE’) and if this same category has to be displayed in the table, then the PRELOADFMT option is used [as it gives instruction to load the FORMAT for the missing category ‘MODERATE’].

-
proc format;

value $rel

'MILD' = 'Mild'

‘MODERATE’ = 'Moderate'

'SEVERE' = 'Severe'

' ' = 'Missing';

run;

-
*USE OF PROC MEANS WITH MULTILABEL & PRELOADFMT;

-
proc means data=test completetypes nway chartype missing noprint;
by visit;
class treat aerel/preloadfmt missing mlf order=formatted;
format treat $tmt. aerel $rel.;
output out=cnt1 n=ccnt;
run;
-

More: http://www.lexjansen.com/pharmasug/2007/cc/cc10.pdf

-

Display the "CLASSIFICATION", Not the "CODE"!

-

The Anatomical Therapeutic Chemical Classification System (ATCCS) is used for the classification of drugs. It classifies drugs at 5 different levels; based on the organ or system on which they act and/or their therapeutic and chemical characteristics.

For example, the ATCCS for drug SUMATRIPTAN is given below:

ATC1 TERM: NERVOUS SYSTEM, N
ATC1 TERM CODE: N

ATC2 TERM: ANALGESICS, N02
ATC2 TERM CODE: N02

ATC3 TERM: ANTIMIGRAINE PREPARATIONS, N02C
ATC3 TERM CODE:N02C

ATC4 TERM: SELECTIVE SEROTONIN (5HT1) AGONISTS, N02CC
ATC4 TERM CODE: N02CC

ATC5 TERM: SUMATRIPTAN, N02CC01
ATC5 TERM CODE: N02CC01


This "ATC LEVEL 2 TERM" may be captured in CONCOMITANT MEDICATION DATASET, which when captured should display only the CLASSIFICATION: "ANALGESICS" and not the CODE: "N02". Hence the code is trimed off from the chemical classification.

-

***** USE OF REVERSE FUNCTION TO TRIM OFF CODE *****
-
data cmed;
set med;
x=trim(left(reverse(atc2t)));
y=substr(x,6,150);
z=reverse(y);
atc2term=strip(z);
run;

-

A code to split numbers!

-

The dummy dataset code has numbers separated by a hash or space. The code that follows splits those numbers and output each number into a unique observation.

-

***** DUMMY DATASET WITH #, " " *****

-

data TEST;
length pt $20 txt $40;
pt ='1';
txt= '#12, #3, #56 34-98';
output;
pt ='2';
txt= '#2 #12';
output;
pt ='3';
txt= '#12';
output;
run;

-
***** SPLIT & OUTPUT NUMBERS *****
-
data TEST1;
set TEST;
stat:
do i = 1 to length(txt);
-
if substr(txt,i,1) = '#' then do;
-
if substr(txt,i+2,1) in (',','') then do;
cno = substr(txt,i+1,1); *3: for single digit;
txt = substr(txt,i+2);
end;
else if substr(txt,i+3,1) in (',','') then do;
cno = substr(txt,i+1,2); *12: for two digit;
txt = substr(txt,i+3);
end;
-
output;
if length(trim(left(txt))) > 2 then goto stat;
leave;
end;
-
end;
run;

-

***** SAME CODE FOR DATA WITHOUT # *****

-
data TEST1;
set TEST;
do i = 1 to length(txt);

-
if substr(txt,i+1,1) in (',','') then do;
cno = trim(left(substr(txt,i,1)));
i=i+2;
end;
else if substr(txt,i+2,1) in (',','') then do;
cno = trim(left(substr(txt,i,2)));
i=i+3;
end;

-
if cno ne '' then output;
end;
run;

-

Retain for Repeated Visit

-
***** RETAIN THE VALUE FOR REPEATED VISIT *****
-
data test;
set vis;
by pt visitnum vstdt;
retain x;
if first.visitnum then x = visitnum;
else x=x+.1;*add .1 for repeated visit;
drop visitnum;
run;
-

Using wildcard to reduce code

-

Using a wildcard (:) in the variable lists would reduce the code to a great extent.
-
Colon can be used as a wildcard in variable lists. In the example given below, (keep=v:) keeps all variable names that begins with letter v.
-
data x(keep=v:) ;
v1=1 ;
v2=10 ;
v3=100 ;
v4=1000 ;
v5=10000 ;
w1=1;
w2=10;
run;

More: http://support.sas.com/publishing/pubcat/chaps/55513.pdf
-