General Code

Number row based on OBS

id=_n_;

To rename a variable on the SET step

This will change the name of variable called old to new

set mydataset (rename=(old=new));

To remove all variables that start with EDC on the SET step

set mydataset (drop=edc:);

Delete a dataset

proc datasets library=work;
   delete test;
run;

Dynamic Libname

/** This is for building a path to you sas data*/
/** The result will be datapathroot\studyname\datapathending*/
%let datapathroot = '\\putservernamehere\CDMAdmin\eDM Connection\';
%let datapathending = '\';
%let studyname = '304801-CS6';

data null;
CALL SYMPUT ('librarypath',&datapathroot||trim(&studyname)||&datapathending);
run;

libname new "&librarypath";

Proc SQL

merging 2 tables with proc sql

proc sql;
   create table x as
   select a.visit, b.day
   from this a, that b
   where a.visit = b.visit;
quit;

put country from b table on every row of a

proc sql;
   create table mrgcountry as
   select b.country, a.*
   from patmaster a, allcountry b
   where a.patnum = b.patnum';
quit;

get the maximum of a value

proc sql;
   create table maxquantity as
   select OrderID, MAX(Quantity)
   from OrderDetails
   group by OrderID;
quit;

Text/Number Conversion

num to text

text=left(put(number,best6.))
or
strip(substr(number,1));

text to number

number = input(char,best6.));

String Manipulation

take all spaces out of a variable called label

compress(label);

Get the position of a substring. Useful to check if a string contains certain characters

If Position > 0 then the string contains those characters. Here we are finding if ABCD is in the variable compound-study

position = prxmatch(prxparse('/ABCD/'), UPCASE(compund-Study));

Date and Time

  • time5.
  • 900=15 minutes
  • 3600=1 hour
  • datatime16 to date: datatime/86400

Put text data ‘12DEC2011’ into number:

tmpdate = input('12DEC2011', date9.);

also

tmpdate = input('2011-12-12',yymmdd10.);

Next month:

intnx('MONTH',Today(),1);

Last month:

intnx('MONTH',Today(),-1);
  • WORDDATE3. means JUN
  • WORDDATE9. means JUNE

This gets the last day of morep/1/yearrep;

        if date = . then ndate = intnx('month',mdy(morep,1,yearrep),1)-1 ;
        else ndate = date;
        date = ndate;

First day of this year:

data test;
	date = intnx('month',mdy(1,1,Year(today())),0);
	format date yymmdd10.;
	run;

INTNX date Function examples

format beginning middle end sameday date9.;   
beginning=intnx('month', '05FEB2010'd, 1, 'b'); 
middle=intnx('month', '05FEB2010'd, 1, 'm'); 
end=intnx('month', '05FEB2010'd, 1, 'e'); 
sameday=intnx('month', '05FEB2010'd, 1, 's');

The result values will be:

  • beginning = 01MAR2010 (1st day of the next month)
  • middle= 16MAR2010 (middle day (16 of 31) of the next month)
  • end= 31MAR2010 (last day (31) of the next month)
  • sameday = 05MAR2010 (the same day (5) of the next month)

You might find it helpful to use the INTCK function along with the INTNX.

Concatenate a date and time

Since your date and time are numeric, another option is to use DHMS function.

newdtm=dhms(date, 0,0,time); 

XLSX

Import XLSX

PROC IMPORT OUT= WORK.masterimpsitenum
            DATAFILE= "\\na2.isis.local\skingery\Site list by site number.xlsx"
            DBMS=XLSX REPLACE ;
GETNAMES=YES;
MIXED=NO;
RUN;

Note: If your file is xls, change to DBMS=XLS REPLACE You can get a specific sheet by including, after the DBMS= line,

sheet="Site list by site number";

Export XLSX

proc export 
  data=prepexcelforexport
  dbms=xlsx 
  outfile="\\na2.isis.local\skingery\convertme\all_active_and_planned_vists.xlsx"
  replace;
run;

Export XLSX with Libname

data null;
	CALL SYMPUT ('exceloutput',&path||"laball.xlsx");
run;

libname out EXCEL "&exceloutput";
proc datasets lib=out;
	delete laball;
run;
data out.laball;
	set laball;
run;

libname out CLEAR;

This exports with labels

libname out EXCEL '\\na2.isis.local\skingery\convertme\SMNTotals.xlsx' DBSASLABEL=compat    ;
proc datasets lib=out;
	delete totals;
run;
data out.totals (dblabel=YES);
	set totals ;
run;

libname out CLEAR;

Macros

Delete a global variable called allsitesin

%SYMDEL allsitesin;

Macro for finding variable type

Bookmarks