SAS Proc Import

SAS Day 13: Proc Import

In clinical trials, we often have Special Interest Excel Sheet from Medical Writer that we need to use Proc Import to convert excel or csv data format into SAS dataset. There were times I can convert the file smoothly, there were other times I ran into all kinds of problems while converting the excel files into SAS dataset, such as format issue, special characters, and DBMS base issue.  After all the experiments, I have summarized the basic methods for Proc Import and Proc Export.

TayebMEZAHDIA / Pixabay

1.Proc Import

• Excel(.xls) file Remove Special Characters to _
  Note: by setting validvarname=v7, we only allow Characters, Numbers, and _

options validvarname=v7 missing=''; 
proc import out=nl 
     datafile="/C/projects/ft/routine/data/external/Special Interest.xls"
     dbms=xls/xlsx replace;
     *optional getnames=yes;
     *optional sheet="a";
     *optional guessword=50;
run;

• Excel(2007) file including Special Characters
  Note: by setting Validvarname=any, we allow any Special Characters

options validvarname=any;
proc import out = multi 
  datafile = "test.xlsx" 
  dbms = excel  replace;    
  *optional getnames = Yes; 
  *optional range="Sheet1$A1:A17" ;
run;

2. Proc Export

Although a few times I have used Proc Export, I guess it is always nice to keep it in handy. 

Note: if we need to present the label, we can use the option *label in the outfile.

proc export data=mydata 
     outfile='c:dissertation/mydata.xlsx' /*label*/ dbms = xlsx replace;
run;

Happy SAS Coding! 🤯