SAS Archives - BI Practice

SAS Enterprise Guide Error

I got the following error when opening the SAS Enterprise Guide 7.1 (64 bit).

‘.’, hexadecimal value 0x00, is an invalid character. Line 1, position 1.

I found the following related post on the SAS community website, https://communities.sas.com/t5/SAS-Enterprise-Guide/Error-when-launching-SAS-EG-7-1/td-p/403943 with reference to the SAS knowledge base https://sas.service-now.com/csm?id=kb_article_view&sysparm_article=KB0036279. Here is the solution proposed in the knowledge base.

Workaround

To resolve the issue, follow these steps:

Close all sessions of SAS Enterprise Guide.
Navigate to C:\users\user-ID\AppData\Roaming\SAS\EnterpriseGuide.
Rename the X.X folder to X.X_OLD, where X.X is the release number of SAS Enterprise Guide.
Reopen SAS Enterprise Guide. You should see that a new X.X folder has been created, and the issue should be resolved.

The solution worked as of November 2024.

ODS html error: path not a directory

Code:

ods listing close;
ods html file="X:\Work\UG_AWARD\Validate\2223awards_oldcode.xls" style=journal;
proc tabulate data=hist.awards2223_sas missing f=dollar11. noseps;
class prog_fac level source level2 category awardname;
var awardamt;
tables level,source*level2*category all,awardamt=' ' / rts=40 indent=3 misstext='$0';
tables level,source*level2*category all,(prog_fac='Home Faculty' all)*awardamt=' ' / rts=40 indent=3 misstext='$0';
*tables level,source*level2*category*(awardname all) all,(prog_fac='Home Faculty' all)*awardamt=' ' / rts=40 indent=3 misstext='$0';
keylabel sum=' ';
run;

Error:

ERROR: A component of C:\Users\username\AppData\Local\Temp\SAS Temporary
       Files\computer_name\X:\Work\UG_AWARD\Validate\2223awards_oldcode.xls is not a directory.
ERROR: No body file. HTML output will not be created.

Reference:

SAS Support Usage Note 61280: The ODS HTML statement generates errors in the SAS® 9.4 TS1M5 windowing environment when the FILE= option includes a fully qualified path

Fix:

Tools>Options>Preferences…>Results
Uncheck “Use WORK folder”

Cause: The problem occurs because the “Use WORK folder” option is set as the default in the SAS registry and preferences. This causes the WORK folder to be prefixed to the path that is specified with the FILE= option. Separating the File and Path options can also fix the problem

SAS: Proc Tabulate Nested Row Variable and Missing Option

In previous 3 Proc Tabulate related posts, I have focused on table coloring, percentages, and setting order for the class variable .

In the table coloring post, I have provided example of stacking statistics for multiple row variables one after the other. Sometimes, we the variables has a nested relationship, we can use * to identify the hierarch of the variable, eg Faculty*program so the statistics by program are ordered by Faculty and then program.

proc tabulate data =table (where=(exclude = "NO" )) missing;
class visa fullpart gender program faculty year;
var heads fftes;
table faculty="Faculty" all visa="Visa Status" all fullpart="F/P" all  
gender="Gender" all faculty="Faculty"*program="Program" all,
(year="year" all)*(heads="Heads"*SUM="" heads="% Total"*pctsum fftes="FFTEs"*SUM="" fftes="% Total"*pctsum 
< faculty all visa all fullpart all  gender all faculty*program > =""  );
run;

The above code basically stacking the sum and percentage of heads and FFTEs by year (column) for the row variables in the order of Faculty, Visa, fullpart (full-time or part-time), gender, and Program (nested under Faculty).

Please pay attention to the “missing” option in the Proc tabulate. If there is any missing value in the class variables, the whole observation is removed from the Proc tabulate result, even for the class variable that doesn’t have any missing values, because Proc tabulate force the total statistics for all the class variables to be the same. It is crucial to include the “missing” option, so “” has a value to be included in the Proc Tabulate results.

Here is a good reference document for Proc Tabulate.

Reference: https://www.iuj.ac.jp/faculty/kucc625/sas/tabulate.html

proc tabulate data= sashelp.cars;
class origin type make drivetrain;
var msrp;
table (origin="" all) * (make="" all), (type="" all )*(DriveTrain="" )*(MSRP=""*MEAN N);
run;

Output:
The table produced will show a hierarchical summary:

Columns: Each combination of type (e.g., Sedan, SUV) and drivetrain (e.g., 4WD), summarizing msrp with mean and count.
Rows: Each combination of origin (e.g., Asia, Europe, USA) and make (e.g., Acura, Audi, BMW), along with totals (all).

SAS: Delete and Copy Specific format from Catalog

Delete $year format from sasfmt.commonfmt catelog.

Use year.formatc to indicate character format.
Use .format to indicate numeric format.
“/ entrytype = format” can be omitted.
For Proc Catalog procedure, need Quit to close it.
Also no need to put $ sign before the format name Year.

proc catalog catalog =sasfmt.commonfmt ;
delete year.formatc / entrytype=format;
quit;

Assume there is a $year format in the work library. Copy the $year format to the sasfmt.commonfmt.

The best practice is developing a local permanent format catalog for common usage, in this case the sasfmt.commonfmt. If I need to create new format for the SAS work and the format is reusable, then I will copy the format from work library to the sasfmt.commonfmt catalog so I don’t have to recreate the format in the future.

Use Proc Catalog procedure
catalog= for the source catalog
out= for the target catalog
Use Copy statement for the copying formats
Use Select statement to select specific format to be copied. If Select statement is omitted, then all the formats in the source catalog will be copied to the target catalog.

Proc catalog catalog=work.formats;
copy out=SASFMT.COMMONFMT;
select year.formatc;
quit;

Check the $Year format in the sasfmt.commonfmt catalog.

Use Proc Format procedure
Use fmtlib option to display the details of format.
lib= for the format catalog
Use Select statement to identify specific formats to be displayed. This becomes useful when the catalog contains a large number of formats.

proc format fmtlib lib=sasfmt.commonfmt ;
select $year; run;

SAS: Create SAS Table from List of Strings

Normally I need to compile a unique list of text strings from SAS table and run report for each item in the list through a %do loop. See details in the previous post. In this case, I need to convert the list of strings back to SAS table using space as delimiter.

Solution

%let lst = apple orange grape carrot banana watermelon peach;
%macro createtbl;
data lst; 
length fruit $15.;
%let i = 1;
%do %while (%scan(&lst., &i., ' ') ne );
  fruit = "%scan(&lst., &i. , ' ')"; 
  output;
%let i = %eval(&i. +1);
%end; 
run;
%mend createtbl;
%createtbl;

Notes

Have to use macro with %macro and %mend because %do, %end, and %eval are not valid in open code. Macros with %macro and %mend are closed marcos.
Use single quotation ‘ ‘ for space and use double quotation ” ” for string. The other way will not work. In the above example, if I change the equation for fruit to ‘%scan(&lst., &i. , ” “)’, then the string %scan(&lst., &i., ” “) becomes the values in the fruit field without interpreting the macro variable in the equation and applying the scan function.
Need %let i = %eval(&i. +1) with the %do % while statement

Wrong Code Demo and Error Diagnosis

data lst; 
length fruit $15.;
do i =1 to 7;
  fruit = "%scan(&lst., i , ' ')"; 
  output;
end;
run;

Error Message

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric
operand is required. The condition was: i
ERROR: Argument 2 to macro function %SCAN is not a number.

Diagnosis

The second argument i is not a number. To solve this, have to use closed macro and %eval, so the value of i +1 can be calculated.

Other Methods that don’t work

Create empty table first and then add values by row.
Don’t know how to add values by row without using macro.

/* NOT USEFUL*/
data lst;
attrib 
    fruit length=$15 format=$15. label="fruit";
stop;
run;

VALIDVARNAM and VALIDMENNAME options

I have been using SAS for number of years, so I thought I have encountered enough cases for problem solving to cover the routine analysis work. This time I was trying to read some large SAS files and the SAS9.4 wouldn’t take it. I add the folder to the SAS library, but the SAS file in that folder doesn’t show up. Then I realized the SAS file name contains spaces and starts with numbers which violate the rules for SAS name. One of my colleague mentioned because he can view and open the SAS file through SAS enterprise guide, he think the file is fine. So I was wondering why SAS9.4 can’t open/view the SAS file but SAS enterprise guide can. It turns out SAS enterprise guide runs the options statements at the backend so it can take noncompatible filename and variable name.

OPTIONS VALIDVARNAME = ANY;
/* This option allows SAS9.4 to read variable names that contains space.  The default is V7.  */
/* V7 rules for SAS variable name:
- up to 32 char
- first char must begin with a letter or the underscore
- trailing blanks are ignored
- cannot contain blanks or special characters */ 

OPTIONS VALIDMEMNAME = EXTEND;
/* This allows SAS9.4 to view data set with name contains blanks and some special characters.  The default is COMPATIBLE. */

Once you run the options of VALIDVARNAME and VALIDMENNAME as shown above in SAS9.4. The SAS dataset will show up in the explorer window and you can click to see the column information. You still need to change the SAS dataset name for the data step to run properly.

SAS: Multiple Set statements to combine and summerize datasets

A. Code

Gather data

%let var = field1 field2 field3 field4 field5;
%let yr = 17;
%let nextyr = 18;
data data&yr.;
length group $1.; 
set Jun&yr. (keep=&var.)
jul&yr. (keep=&var.)
nov&yr. (keep=&var.)
feb&nextyr. (keep=&var.)
mar&nextyr. (keep=&var.);
where field1= "YES";
group = substr(field2, 1, 1);
count = field3;
run;

Summarize data

%macro sum (varname, cntvar, group, where, out);
%put &varname.;
%put &cntvar.;
%put &where.;
proc summary nway missing data =data&yr. (where= (field4=&where.));
class field1 &group.;
var &cntvar.;
output out=&out. (drop= field1 _freq_ _type_ ) sum (&cntvar.)=&varname.;
run;
%mend sum;
%sum ( var6, count, group, 'CAT1' , t1);
%sum ( var6, count, , 'CAT1' , sum1);
%sum ( var7, count, group, 'CAT2' , t2);
%sum ( var7, count, , 'CAT2' , sum2);
%sum ( var10, count, group, 'CAT1' and field5 = 'NOV' , t3);
%sum ( var10, count, , 'CAT1' and field5 = 'NOV', sum3);
%sum ( var11, count, group, 'CAT2' and field5 = 'NOV' , t4);
%sum ( var11, count, , 'CAT2' and field = 'NOV' , sum4);

Combine data

data data_new;
set t1 sum1 ;
set t2 sum2;
set t3 sum3;
set t4 sum4;
if group = '' then group = 'X';
run;

B. Output

combining T1, T2, T3, T4, Sum1, Sum2, Sum3, Sum4 by group alignment

Git: GitHub Desktop

A. Download

https://desktop.github.com/

B. Steps

set up username and password in github.com
in github desktop, create a new repository

Provide name of the repository and local path.

Commit files to branch

Make the initial commit. The commit is in the master branch.

Publish repository to GitHub

Review the commit in History tab

ignore files setup
- in the same directory of the files being committed, set up a .gitignore text file.
- *.* and !*.sas : ignore all files in the directory except for .sas files and files in the subfolders.
- /foldername: ignore subfolders

SAS: Tagset.Excelxp Double loop with multiple sheets output depend on dynamic parameter table

A. Purpose

The use of this SAS program is to produce excel files for each product, and within each excel product file, generate multiple sheets with different conditions, sheets names, titles.

B. Code

Set up parameter table

options symbolgen;
data parmlst;
infile datalines delimiter = '!';
length  parm1 $60. parm2 $30. parm3 $40.;
input parm1 $ parm2 $  parm3 $ ;
call symput('parm1_'||left(_n_), parm1);
call symput('parm2_'||left(_n_), parm2);
call symput('parm3_'||left(_n_), parm3);
datalines;
engineSize le 3.5  ! EngineSize le 3.5! (Engine Size = $40,000)
;
run;

Set up tagsets excel output macro

/* [S=] is [S=<parent>] */
%macro exlout (varname=, con=);
%let  i=1;
%do %while (%scan(&con., &i, ' ') ne );
%let dsn=%scan(&con., &i, ' ');
data &dsn.;
set sashelp.cars;
if &varname. = "&dsn."; 
count = 1;
run;
ods tagsets.Excelxp path= "X:\SAS\" file="Car_Profile_&varname._&dsn..xls" style=journal
options (embedded_titles='yes' embedded_footnotes='yes'  absolute_column_width='10,10,10,10' auto_height="no" );
%do j=1 %to 2;
%put &j;
ods tagsets.excelxp options (sheet_name="&&parm2_&j." sheet_interval='none');
title1 justify = left  height = 22pt bold "Car Profile (&varname. = &dsn.) by Specifics";
title2 justify = left  height = 20pt bold "Car Segment: &&parm3_&j. ";
proc tabulate data = &dsn.  S=[background=palg];
where &&parm1_&j.; 
class Type Make Origin engineSize Cylinders / S=[background=palg];
classlev Type Make Origin engineSize Cylinders / S=[background=palg];
var Horsepower MPG_City MPG_Highway Count/ S=[background=palg];
table Type all="Total"*[S=]
Make all="Total"*[S=]
Origin all="Total"*[S=]
EngineSize="EngineSize" all="Total"*[S=]
Cylinders all="Total"*[S=],
(Horsepower*Mean="" MPG_City="MPG City"*Mean="" MPG_Highway="MPG City"*Mean="" Count="N"*SUM="" )
/ box = [S=[background=palg]] ;
keyword all / S=[background=lilg];
run;
quit;
%end;
ods tagsets.excelxp close;
%let i = %eval (&i +1);
%end;
ods tagsets.excelxp close;
run;
%mend exlout;

run macro

%exlout (varname=Origin, con=USA Europe);
%exlout (varname=Type, con=Sedan SUV);

C. Output

D. Key Coding Elements

loop: %let i=1; %do %while (%scan(&con. &i, ‘ ‘) ne );
loop: %do j= 1 %to 2;
Colouring: S=[background=palg]; palg and lilg are different shades of green.
macro variable assignment: call symput (‘parm1_’||left(_n_), parm1);
Customization of excel sheets: data parmlst;

SAS: Proc Tabulate Table Colouring

/*Original table*/
proc tabulate data =table1 ;
class Agegroup gender acadyear;
var heads;
table agegroup="Age Group" all gender="Gender" all ,
(acadyear="")*(heads="Heads"*SUM="" heads="% Total"*pctsum < agegroup all gender all > =""  );
run;

/* Change body to light green */
proc tabulate data =table1 S=[background=lightgreen] ;
class Agegroup gender acadyear;
var heads;
table agegroup="Age Group" all gender="Gender" all ,
(acadyear="")*(heads="Heads"*SUM="" heads="% Total"*pctsum <agegroup all gender all > =""  );
run;

Add S=[background=lightgreen] in the Proc tabulate statement.

/* Change Class Variable Colour */
proc tabulate data =table1 S=[background=lightgreen] ;
class Agegroup gender acadyear / S=[background=lightgreen];
var heads;
table agegroup="Age Group" all gender="Gender" all ,
(acadyear="")*(heads="Heads"*SUM="" heads="% Total"*pctsum < agegroup all gender all > ="" );
run;

Add / S=[background=lightgreen] in the Class statement.

/* Change var variable column heading colour */
proc tabulate data =table1 S=[background=lightgreen] ;
class Agegroup gender acadyear / S=[background=lightgreen];
var heads / S=[background=lightgreen];
table agegroup="Age Group" all gender="Gender" all ,
(acadyear="")*(heads="Heads"*SUM="" heads="% Total"*pctsum < agegroup all gender all > =""  );
run;

Add / S=[background=lightgreen] in the Var statement.

/* Change Box colour */
proc tabulate data =table1 S=[background=lightgreen] ;
class Agegroup gender acadyear / S=[background=lightgreen];
var heads / S=[background=lightgreen] ;
table agegroup="Age Group" all  gender="Gender" all ,
(acadyear="")*(heads="Heads"*SUM="" heads="% Total"*pctsum < agegroup all gender all > ="" )
/ box =[S=[background=lightgreen]];
run;

Add / / box =[S=[background=lightgreen]] in the Table statement.

/* Change Class categories colour */
proc tabulate data =table1 S=[background=lightgreen] ;
class Agegroup gender acadyear / S=[background=lightgreen];
classlev Agegroup gender acadyear /  S=[background=lightgreen];
var heads / S=[background=lightgreen] ;
table agegroup="Age Group" all  gender="Gender" all ,
(acadyear="")*(heads="Heads"*SUM="" heads="% Total"*pctsum < agegroup all gender all > =""  )
/ box =[S=[background=lightgreen]];
run;quit;

Add class variables and / S=[background=lightgreen] to the Classlev statement.

/* Change Colour of 'All' field */ 
proc tabulate data =table1 S=[background=lightgreen] ;
class Agegroup gender acadyear / S=[background=lightgreen];
classlev Agegroup gender acadyear /  S=[background=lightgreen];
var heads / S=[background=lightgreen] ;
table agegroup="Age Group" all  gender="Gender" all ,
(acadyear="")*(heads="Heads"*SUM="" heads="% Total"*pctsum < agegroup all gender all > =""  )
/ box =[S=[background=lightgreen]];
keyword all / S=[background=green];
run;

Add all / S=[background=green] to the Keyword statement.

/* Rename 'All' to 'Total'. Change 'Total' row colour Across the table. */
/* Method 1 */
proc tabulate data =table1 S=[background=lightgreen] ;
class Agegroup gender acadyear / S=[background=lightgreen];
classlev Agegroup gender acadyear /  S=[background=lightgreen];
var heads / S=[background=lightgreen] ;
table agegroup="Age Group" all="Total"*[s=]  gender="Gender" all="Total"*[s= < parent >]  ,
(acadyear="")*(heads="Heads"*SUM="" heads="% Total"*pctsum < agegroup all gender all > =""  )
/ box =[S=[background=lightgreen]];
keyword all / S=[background=green];
run;
/* Method 2 */
proc tabulate data =table1 S=[background=lightgreen] ;
class Agegroup gender acadyear / S=[background=lightgreen];
classlev Agegroup gender acadyear /  S=[background=lightgreen];
var heads / S=[background=lightgreen] ;
table agegroup="Age Group" all="Total"*[S=[background=green]]  gender="Gender" all="Total"*[S=[background=green]]  ,
(acadyear="")*(heads="Heads"*SUM="" heads="% Total"*pctsum < agegroup all gender all > =""  )
/ box =[S=[background=lightgreen]];
keyword all / S=[background=green];
run;

Add *[S=[background=green]] to the all labels in the Table statement.

/* Add All column Total. Override colour of All column */
proc tabulate data =table1 S=[background=lightgreen] ;
class Agegroup gender acadyear / S=[background=lightgreen];
classlev Agegroup gender acadyear /  S=[background=lightgreen];
var heads / S=[background=lightgreen] ;
table agegroup="Age Group" all="Total"*[S=[background=green]]  gender="Gender" all="Total"*[S=[background=green]]  ,
(acadyear="" all={label="All Years" S=[background=lightgreen]} )*(heads="Heads"*SUM="" heads="% Total"*pctsum < agegroup all gender all > ="" )
/ box =[S=[background=lightgreen]];
keyword all / S=[background=green];
run;