In the following data step, table 1 and table 2 have the same data structure. Setting multiple tables in the data step will stack the tables. First, the where statement will make the program more efficient by applying the conditions to all the datasets in the Set statement. If statements create subsets and apply action(s) to specific subsets.
Data temp;
set table1 table2;
where var1 = ‘condition1’ and var2 = ‘condition2’ and var3 NE ‘condition3’;
if var4 = a and var5 = ‘3’ then do; output; end;
if var4 = b and var5 IN (‘1′,’2’) then do; output;end;
run;
Author: myyorku2002
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.

- 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;
Alternative 1: LIBNAME method
- Add xlsx in the libname statement;
- dblabel = option not work for the xlsx engine.
Libname exl xlsx "C:\Excel\car.xlsx"; data exl.cars; set SASHELP.CARS ; run; libname exl clear;
Alternative 2: ODS EXCEL method
- ods excel; ods excel close;
- file =” “;
- ods excel options (sheet_name = ” “);
- proc print noobs label data=;run;
ods excel file= "C:\Excel\&fac._research.xlsx"; ods excel options (sheet_name = "&fac."); proc print noobs label data =research; where faculty = "&fac."; run; ods excel close;
SAS: Identfy and Remove Duplicate Records
Step 1: Check duplicate records by one field or a combination of fields.
proc freq data=temp noprint;
table var1 /out =dupl (keep =var1 count where = (count >1));
run;
proc freq data=temp noprint;
table var1*var2 /out =dupl (keep =var1 var2 count where = (count >1));
run;
Examine the output to see what circumstances can cause duplicate records.
Step 2: Remove duplicate records.
proc sort data=temp NODUPKEY;
by var1 ;
run;
proc sort data=temp NODUPKEY;
by var1 var2 ;
run;
Alternative method to output unique records and duplicated records in two separate datasets.
proc sort data =temp out=temp1;
by var1 var2;
run;
data unique dup;
set temp1;
by var1 var2;
if first.var2 and last.var2 then output unique;
else output dup;
run;
SAS:Output SAS Results to Excel Pivot Table
Purpose:
- To create a pivot table from sas data sets. Apply specific configuration to the pivot table.
Download the tagset from SAS support website.
- “http://support.sas.com/rnd/base/ods/odsmarkup/tableeditor/tableeditor.tpl”
Save the tagset to local directory
- “C:\Users\xxxxxx\Documents\My SAS Files(32)\9.3\TableEditor\tableeditor.tpl”
Code Example:
filename temp “C:\Users\xxxxxx\Documents\My SAS Files(32)\9.3\TableEditor\tableeditor.tpl”;
%include temp;
ods tagsets.Tableeditor file=”C:\Users\xxxxxx\Documents\My SAS Files(32)\9.3\AAPR\Excel\temp\temp.html”
options(BUTTON_TEXT=”Create PivotTable”
AUTO_EXCEL=”yes”
UPDATE_TARGET=”C:\\EXAMPLE.XLSX”
OPEN_EXCEL=”no”
PIVOT_SHEET_NAME= “Academic Program Report_UG”
PIVOTPAGE=”faculty”
PIVOTROW=”progname,category, level2, level3″
PIVOTCOL=”year”
PIVOTDATA=”value”
PIVOT_GRANDTOTAL=”no”
PIVOT_SUBTOTAL=”no”
PIVOTDATA_FMT=”#,###”
PIVOT_FORMAT=”medium14″
DELETE_SHEETS=”Sheet1, Sheet2, Sheet3, Table_1″
QUIT=”yes”
);
Proc print data = gr;
var faculty progname level2 level3 year value category;
run;
ods tagsets.tableeditor close;
Code Structure:
- set up tableedit targetset directory
- call ods statement
- config pivot table in options
- run proc print statement
- close ods
Notes for Options
- Auto_Excel = “yes” (open Excel automatically)
- Update_target=”C:\\example.xlsx” ( need to create the excelfile example in the directory first. Notice C:\\ in the directory)
- Open_Excel= “no” (running excel at the backgroup)
- Pivotpage= “faculty” (identify field for report filter)
- Pivot_grandtotal=”no” (hide the grandtotal in the pivot table default view)
- Pivot_subtotal= “no” (hide the sub-total in the pivot table default view)
- Quit= “yes” (exit excel)
Problems Identified
- format in the option setting not working
- print procedure doesn’t take long (22 seconds) for 20,000 records table, but ods process take longer time to generate the pivot table. There is no stop signal to show that the process has been completed.
- The excel file doesn’t seem to be updated until you open the file and the prompt would ask if you would like to save the change and if you click yes the updated the pivot table will show.
SAS SQL: Merge dataset with multiple conditions
Assume all the macro variables are defined. There are two lists A and B. A is list of year n students and B is a list student id for year n+1 students in the same program. Step 1 is to create table that includes all the students with study level 1 or 2 in A but not in B (1 year later). This step identifies the student population of study level 1 and 2 who are lost to the program one year later. Notice A and B are used as table aliases to simplify the statement. Also the syntax for left join needs to be followed by the on statement to specify the column field to join the two table.
proc sql;
create table nov&yr.not as
select A.* from nov&yr. as A
left join nov&nextyr.id as B
on A.sisid = B.id
where B.id is null and A.std_lvl in (’01’,’02’)
;
quit;
Second step is to identify the students who changed their major to Psychology from results of step 1. Notice I omit As when I assign the aliases.
Proc sql;
create table nov&yr.chg as
select A.*, B.sisall, B.newm1 from nov&yr.not A, nov&nextyr.all B
where A.sisid = B.sisall
and A.major1 ne B.newm1
and B.newm1 eq ‘PSYC’
;
quit;
SAS: Set start and stop for macro variables
Sometimes I need to repeat the same exercise for multiple times, for example faculty want retention rate not just for one year but multiple year, it is easy to use %macro macroname (parameter1, parameter2) in conjunction with %do varname = &value1 &to &value2 to customize the year range in the do loop statement per clients request.
eg;
%macro step (strtyear, stopyear);
%do yr= &strtyear %to &stopyear;
….continue coding
%end;
%mend step;
%step (11, 14);
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: Variables/Fields with same Prefix
I have a SAS file that stores students course selection (up to 20 fields) and grades for each course (up to 20 fields) for a certain semester. To set the sas working file, I use the follow data step. Both course variables and grades variables have the common prefix “crs” and “grade”. It is more efficient to list the 20 course variables using “crs1-crs20” than list each and every one of them.
%let varlist = crs1-crs20 grade1-grade20 studentid major;
Data tem1;
Set coursefile_fw16 (keep = &varlist.);
run;
SAS: Acquire new database(1)
Prelude
I always find interesting when I have a business case or project where the business requirements involves technical development even in the smallest way.
I’m working at a reporting unit of the institution. We used to have an application maintained for this function unit for their reporting and query needs. As the business grows, the function unit decided to purchase a new third party software to capture its business transactions and reporting needs. My department was not involved in the business decision.
The third party application is web based. Because the users want to analyze the data, the vendor provided a simple query interface through the application so the users can extract the data that meets certain criteria. First of all, the query interface was poorly designed where tables can be viewed awkwardly and the relationship between the tables were not presented in an explicit way. Secondly, the query grid has many limitations regarding the type of the criteria setting and has no capacity to incorporate calculated field to the query criteria. The vendor also provide a sql query tool through the interface, but since the users are not technical savvy and the query interface assumes users have comprehensive knowledge of the underlying database structure and relationships between the tables, the sql query interface wasn’t utilized as intended.
Later my department was asked to produce the statistic report of that function unit, we couldn’t do it because the old database is not maintained and does not have the updated data. We also found out that the third party software can not be easily integrate to meet the institution’s reporting needs. In order to re-establish the reporting function for the new databases, I asked the function unit to contact the vendor to provide me the access to the software as well as establish the back-end access to the data tables through the help of our IT people. Later, I got access to the back-end tables through Mysql ODBC.
I was using MS Access as an intermediate tool to view the tables because I wasn’t provide with any information on the third party software, not on the table and fields structure, not on the business process and flow or the relationship between the tables. Basically, it is self-direct investigation. From the more than 200 tables, I narrow down about 30 tables I would need to produce reports based on an iterative discussion with the function unit on what kind of report and data they want. As a temporary solution, I used Access to understand the database and develop the queries and reports for users to verify. The next task will be set up the database and develop reports on SAS platform.
Misc: Open EML files without launch mail system
One of my co-worker was sent about 30 EML files and each has attachment that needs to be saved to the hard drive. If you click the eml file, the mail system will launch and then you need to type in the user name and password to open each eml file. I was looking into ways can save time to access the attachment and make the process more efficient. I came across this software EML File Viewer on the developer’s website http://www.freeviewer.org/eml/ that does exactly the work I was looking for. The view can search the eml files on your drive and identify and preview the attachment for each in separate window. There is a free version you can download to try out.