SAS: Export to Excel with Label Option and 9.3/9.4 Difference

It is common to export SAS data tables or outputs to Excel spreadsheets.  The default Excel output will only contain field names in the first row of the spreadsheet.  The ‘label’ option will allow the SAS label names to be the column names on the first row of the exported table.

Method 1:

PROC EXPORT DATA=table1.
OUTFILE= “C:\Work\SAS\table1.xlsx”
label DBMS=EXCEL REPLACE;
SHEET=”table1″;
NEWFILE=YES;
RUN;

method 2:

libname excelout “X:\Work\excelfile.xlsx”;

data excelout.sheet1 (dblabel=yes) ;
set tablename;

run;

libname excelout clear;

9.4 SAS Difference

PROC EXPORT

• Experience slowness in running PROC IMPORT and PROC EXPORT
• Find reference: https://blogs.sas.com/content/sasdummy/2012/05/01/64-bit-gotchas/;
• Change: DBMS= EXCEL to DBMS=EXCELCS

PROC EXPORT DATA= SASHELP.CARS 
OUTFILE= "C:\Excel\car.xlsx" 
            DBMS=EXCELCS REPLACE;
     SHEET="car"; 
RUN;

• Bug: a blank tab (_SAS_empty_) with A1 = “_empty_(CHECK_OTHER_SHEET) was generated in the xlsx file.

Solution: https://communities.sas.com/t5/SAS-Programming/Proc-Export-creating-a-blank-SAS-empty-sheet-in-the-exported/td-p/483623

• Change DBMS=EXCEL to DBMS=XLSX and the blank tab will disappear.

PROC EXPORT DATA= SASHELP.CARS 
OUTFILE= "C:\Excel\car.xlsx" 
            DBMS=XLSX REPLACE;
     SHEET="car"; 
RUN;

LIBNAME

• Add xlsx in the libname statement;

Libname exl xlsx "C:\Excel\car.xlsx";
data exl.cars;
set SASHELP.CARS ;
run;
libname exl clear;

SAS: Convert Variable between Numeric and Character Format

A. Use Put or Input
Convert between numeric and character variable.

• character to numeric (input)

old_char = "2018";
new_num = input(old_char, 8.);
new_num = 2018;

• character to numeric (input) to character (put)

old_char = "2018";
new_char = put (input (substr(old_char , 1, 4 ), 8.) -1 , 4.);
new_char = "2017";

/* or */
new_char = put (old_char*1-1 , 4.);
new_char = "2017";

• numeric to character (put), and with leading Zero.

old_num = 2018;
new_char = put(old_num, 4.); *new_char = "2018";
new_char1 = put(old_num, z8.); *new_char1 = "00002018";

• numeric to character (put) to numeric (input)

old_num = 2018;
new_num = input(substr(put(old_num, 4.),3, 2) , 8.);
new_num = 18;

Use the following functions to check whether any digit or alphabetic character is in the character variable. The results will be the position of the first digit/alphabetic character in the field.

check1 = anydigit(var); *return position of first digit, 0 if not found in the string;
check2 = anyalpha(var); *return position of first alphabetic character, 0 if not found in the string;
check3 = notalpha(var); *return position of first non digit, 0 if not found in the string;
check4 = notdigit(var); *return position of first non alphabetic character, 0 if not found in the string;

• Please note there should be no space in between % and statement eg. macro, let, mend, put, eval etc.
• To apply the macro variable, use &varname. format.

CODE:

%macro setyr;
%let yr = 14;
%let nextyr =%eval(&yr. +1);  /* 15 */
%let next2yr=%eval(&yr. +2);  /* 16 */
%let fisyr = “20%substr(&yr., 1,2)/%substr(&nextyr., 1,2)”;   /* “2014/2015” */
%let fisnextyr = “20%substr(&nextyr., 1,2)/%substr(&next2yr., 1, 2)”;   /* “2015/2016” */
%let year = “20&yr.”; /* “2014” */
%put &yr;
%put &nextyr;
%put &next2yr;
%put &fisyr;
%put &fisnextyr;
%put &year;
%mend setyr;
%setyr;

OUTPUT:
14
15
16
“2014/15”
“2015/16”
“2014”

B. Use Vvalue fuction
Vvalue() returns the formatted value that is associate with the variable.

data want;
data have;
new_charvar1 = vvalue(formatted_numvar1);  *formatted means the variable has been applied with format;
new_numvar2= vvalue(formated_charvar2);
run;

SAS: proc freq, tables statement

Proc Freq can produce output  data sets that you can use for other SAS programming.  One way is to specify the out= option in a TABLES statement and the other way is to have an OUTPUT statement.  See the below SAS knowledge base for details.

http://support.sas.com/documentation/cdl/en/statug/63347/HTML/default/viewer.htm#statug_freq_sect020.htm

SAS ODBC Driver Installation

I want to access the sas data table outside sas software and in Microsoft Access where I can edit the records more conveniently.

The version of sas I have is 9.3 and I googled the sas ODBC driver and came across the following link.

http://support.sas.com/downloads/browse.htm?cat=40#tab:2:

Initially, I installed the “Aug 2014” version, but it is for SAS 9.4 and didn’t work.

I uninstalled the sas ODBC for 9.4 and then installed the “July 2011” version which didn’t work either because I have Win 7.

Then I had to uninstalled ODBC for 9.3 and download the “Dec 2011” version.

Hope this time it works but it doesn’t. 😦

Got this error message.

When launching setup.exe for ODBC installation. Got the following error message.

While having no success in installing SAS ODBC 9.3,  I found some good reference resource on the website.

http://www.sascommunity.org/planet/blog/category/sql/

With some investigation, I found the odbcad32.exe hiding in a winsxs folder.

C:\Windows\winsxs\x86_microsoft-windows-m..-odbc-administrator_31bf3856ad364e35_6.1.7600.16385_none_44263d819f0aa19e

This might came from one of the windows service patch installed previously, but it is not the same ODBC administrator when I run from the “Control Panel” > “Administrative Tools”, which point to a different odbcad32.exe under C:\Windows\System32.

Apparently, the SAS ODBC 9.4 has already been recognized in the “ODBC Data Source Administrator”.  Under the “System DSN” tab, click “Add”.

Find the “SAS” driver at the bottom of the list and click “Finish”.

In the “SAS ODBC Driver Configuration” window, there are 3 tabs, “General”, “Servers” and “Libraries”. Notice the Server name is defined as “__5001” which is the default “Local (Single User)” and if you change the name to something else, it will cause error in connection. Click “Configure…” on the “Servers” tab.

On the “Local Options” setting, check if the Path for sas.exe is correct and don’t change the default setting for “Startup parameters”.  Click “OK” to go back.

Click “Library” tab and under the “Library Setting” give a Name for the library which holds the SAS data file you want to access through ODBC. In the “Host File”, provide the directory of the folder that contains the SAS data files.  You can create many libraries for different SAS folders. Click “Add” to add the library and then “OK” to go back.

This completes the configuration of the “SAS ODBC Driver”.

 

 

I. A.

Renew SAS Software

Come to renew my SAS license, I was given a txt file SAS93_xxxxxx_xxxxxxxx_win_X64_Wrkstn.txt.

Click “Start” > “Program”

A dialog box pops up where you can input the txt file name with the directory.

The renew license information has to match the operating system, if not, the new license will not be able to apply and change the site validation data.

For example, my computer is a ‘w32_7pro’ and the renew license’s OSNAME specifies ‘wx64-wks’, and I got the following error message.

It will generate a setimit.log (c:\program Files\sashome\x86\SASfoundation)  with the detail information.

If the license file is right and you browse to the folder where it is saved.

Click “Next” and a dialog box will show the SAS installation data being verified with all the dates renewed to the new dates.

Then you need to identify the folder where SAS is installed. On my computer it is c:\Program Files\SASHome\x86\SASfoundation\9.3., then click “Renew”.

 

 

You will get a pop up that the setinit is successfully installed.

I.A.

SAS output to date stamped Excel file

Sometimes people were asking for data and you just provided it to them. If it is an annual completed static data, you don’t have to do the date stamp to keep a record on when you retrieve the data from the system.  However, if it is an operational ongoing data with day-to-day changes and also the data is not officially published for the year,  you might want to keep a record of the date for reference.

The SAS code here basically provides a date format that you want to show on the Excel file name.  The format code itself is quite complicated, but you don’t have to remember it at all. Just copy and paste. There are many variations to it.  Then use macro functions %unquote and %sysfunc to get date and time as part of the file name.

proc format;
picture mydtfmt
low-high = ‘%Y_%0m_%0d_%0I%0M%p’ (datatype=datetime);
run;

PROC EXPORT DATA= Section
OUTFILE= “C:\Documents\My SAS Files(32)\9.3\Exce\section_%unquote(%sysfunc(datetime(),mydtfmt.)).xlsx”
DBMS=EXCEL REPLACE;
SHEET=”section”;
RUN;

Output:

In Windows Explorer,