How to customize page numbers in RTF output

Usage Note 24439: In SAS 9.1, are there easier ways to customize page numbers in RTF output?

direct link here http://support.sas.com/kb/24/439.html

Yes, beginning with SAS 9.1, page numbers can be customized in the RTF destination by using an escape character and the {thispage} function, {lastpage} function, {pageof} function, or all three:

ods escapechar='^';
ods listing close;
ods rtf file='c:\tests\test.rtf';
data test;
do i=1 to 50;
output;
end;
run;


proc print data=test noobs;
title 'Page ^{thispage} of ^{lastpage}';
footnote '^{pageof}';
run;


ods listing;
ods rtf close;

How to calculate number of years and number of days between 2 dates;

How to calculate number of years and number of days between 2 dates;

Exploring the yrdif and datdif functions in SAS as well as INTCK function:
There are several ways to calculate the number of years between two dates and out of all the methods, YRDIF function results the most accurate value.

Syntax:

ageinyrs = YRDIF(birthdate, enddate, 'act/act');
ag_indays = DATDIF(birthdate, enddate, 'act/act');

“act/act” will gives us the actual interval between the two dates.

The YRDIF function returns the calculated years between two SAS date values. The returned value will be a precise calculation, including multiple decimal places. Whereas with INTCK function will just give the rounded value like 10, 11 and not like 10.2 and 11.5.

Syntax:
Using YRDIF function:
To know the interval between two dates in Years:

data _null_;
sdate="12mar1998"d;
edate="12jun2008"d;
years=yrdif(sdate,edate,'act/act');
put years;
run;

Output: 10.2535 yrs

Using DTDIF function:
To know the interval between two dates in Days:

data _null_;
sdate="12mar1998"d;
edate="12jun2008"d;
days=datdif(sdate,edate,'act/act');
put days;
run;

output: 3745 days


Using the INTCK function:

data _null_;
sdate="12mar1998"d;
edate="12jun2008"d;
years=intck('year',sdate,edate);put years;
run;

output:10 years

The calculation for the number of years from INTCK function is different from that generated by YRDIF. This is because the INTCK function bases the interval from the start of the respective intervals.

The INTCK function returns the integer count of the number of intervals in years, months or days between two dates.

Ex:
To know the interval between 2 dates in days:

data _null_;
sdate="12mar1998"d;
edate="12jun2008"d;
days=intck(‘days’,sdate,edate);
put days;
run;

result: 3745 days


To know the interval between 2 dates in months:

data _null_;
sdate="12mar1998"d;edate="12jun2008"d;
months=intck('months',sdate,edate);
put months;
run;

result: 123 months

How to create a comma separated file (.csv) of a SAS dataset?

IN SAS programming, we often require outputting the dataset in different formats like EXCEL and CSV etc and here are the five different ways to export the SAS dataset into .csv file.

Example:

data new ;
infile datalines dsd dlm=' ' missover;
input a b c d;

datalines;
3 5 1 1
4 1 . .
5 8 3 2
6 0 4 4
;
run;

By putting MISSOVER in the infile statement we are telling SAS to do not look for the data in the next lane if it runs out of the data, instead keep missing values for any remaining variables.

DSD and DLM options should be included always in the infile statement, if we include the
dlm=’ ‘ in the infile statement then SAS will put one digit for each variable even though we haven’t assigned any length to variable.

DSD option will tell SAS to consider a missing value if 2 delimiters are present side by side in any observation.

When we ran the above program in SAS, we create a SAS dataset name ‘NEW’ in the work directory and if we want to create a .csv file of dataset ‘NEW’ here are the 5 different ways to do it:

1)Here is the simplest method and least known method to create the .CSV file of a dataset; Using the DEXPORT statement.

Here is the syntax:

dm "dexport new 'H:\abccompany\client\Programs\Analysis\project1\class.csv' ";

When we submit the above code, SAS will automatically create a .CSV file in specified location path.


2) .CSV file can also be created using the PROC EXPORT procedure:
Here is the syntax:

proc export data=new
outfile=" H:\abccompany\client\Programs\Analysis\project1\class.csv ";
run;

3) By using the ODS and the Proc print we will be able to create a .CSV file of SAS dataset.

Here is the way to do it;

ods csv file= ‘H:\abccompany\client\Programs\Analysis\project1\class.csv ';

proc print data = new noobs;
run;
ods trace on;
ods csv close;

By keeping ODS trace on; statement we are telling SAS no to print the results in the output window, because, we are only creating the .csv file.

NOOBS option is required here, because PROC PRINT by default will create a new variable called 'OBS' and since we don't require it, we should include the NOOBS option.

4) File statement can also be useful in creating the .csv file of a SAS dataset.
Here is the way to do it.

data _null_;
file " H:\abccompany\client\Programs\Analysis\project1\class.csv ";

set new;
put (_all_) (',');
run;

5)b) Another way using the file statement:

filename csv ‘H:\abccompany\client\Programs\Analysis\project1\class.csv';

data _null_;
set new ;
file csv dlm=',';
put ( _all_ ) (+0);
run;

If we don’t mention the PUT( _all_), SAS will not keep all the variables in the .CSV file and even if we include put (_all_) the log will say:

ERROR 79-322: Expecting a (.

ERROR 76-322: Syntax error, statement will be ignored.

Because SAS is expecting a second parentheses here to follow the first one immediately and if we just only put the closed parenthesis to get rid of the previous error,
put (_all_) ( ) ;

We will get another error, because SAS we haven’t set any specifications for the PUT statement in the 2nd parenthesis.

So we can use a do-nothing pointer control (+0) to be error free.

Import CSV files and create SAS data sets on the fly!

How to Import Excel files into SAS

Reading from Excel Spreadsheets:
Microsoft Excel spreadsheets can be read from SAS in several ways. Two of these will be demonstrated here. First, PROC IMPORT allows direct access to Excel files through SAS/Access to PC File Formats or access to Comma-Separated (CSV) files through Base SAS. The second method uses the Excel LIBNAME engine.

PROC IMPORT
The IMPORT procedure reads from external sources and creates a SAS data set. Two sources are Excel spreadsheets and CSV files. A particular SAS/Access product may be required for certain sources, however. In our example, SAS/Access to PC File Formats is required to read an Excel file, but a CSV file can be accessed with Base SAS.

General Syntax for PROC IMPORT:

PROC IMPORT DATAFILE="c:\sas\ego.csv" OUT=jeeshim.egov DBMS=CSV REPLACE;For Excel you use the DATAFILE=”filename” option to specify the Excel file to be read. (The TABLE=”tablename” option would be applicable if you were reading from a database such as Microsoft Access.)

GETNAMES=YES;
DATAROW=2;
RUN;


The OUT= option specifies the SAS data set to be created. The DBMS= option identifies the type of file to be read. In this case, you will use either EXCEL or CSV to read an Excel spreadsheet or CSV file, respectively. Finally, the REPLACE option determines whether to replace the data set that is created, if it already exists.


read more at Reading from Excel Spreadsheets

Learning SAS

THE SPECIAL “??” FORMAT MODIFIER

The following excerpt is from SAS OnlineDoc documentation:

? or ??

Direct link: http://www.nesug.org/Proceedings/nesug01/at/at1013.pdf

The optional question mark (?) and double question mark (??) format modifiers suppress the printing of both the error messages and the input lines when invalid data values are read. The ? modifier suppresses the invalid data message. The ?? modifier also suppresses the invalid data message and, in addition, prevents the automatic variable _ERROR_ from being set to 1 when invalid data are read.

Below is an example of using ?? to determine whether a variable contains non-numeric values or not:

data _null_;
x = “12345678”;
if (input(x, ?? 8.) eq .) then
put ‘non-numeric’;
else put ‘numeric’;
run;

Running SAS would return “Numeric” in the above example. If we used X=”123a5678”, SAS would return “Non-Numeric”. Note that the input format in the above example is “8.” So only the first 8 bytes of the character string are checked.

Thus, X=123456789a would return “Numeric” as it would only be checking the first 8 bytes of the string.

 

 

I found an  interesting tip about suppressing the invalid data note and/or error message when reading in formatted data while surfing on the net.... (Source : Queensland Users Exploring SAS Technology)

 

Tips & Techniques - #3

Q: Is there a way to suppress the invalid data note and/or error message when reading in formatted data?

A:

􀂄Use the Format Modifiers ? or ?? on the input statement (or input function)

􀂄? –suppresses printing the invalid data note when SAS encounters invalid data values.

􀂄??––suppresses printing the messages and the input lines when SAS encounters invalid data values. The automatic variable _ERROR_ is not set to 1 for the invalid observation.

􀂄For example:

input x ?? 10 10-12;


Is the same as:


input x ? 10 10-12;


_error_=0;

PROC SQL basics, tips and techniques and sample code programs

Proc SQL:


Power of SAS SQL:

• SQL looks at datasets differently from SAS
– SAS looks at a dataset one record at a time, using an implied loop that moves from the first record to the last
– SQL looks at all the records, as a single object
• Because of this difference SQL can easily do few things that are more difficult to do in SAS
• There are a number of built in functions in SQL that can be used in a select statement
• Because of how SQL handles a dataset, these functions work over the entire dataset
• Functions:

– Count: Counts Values
– Sum: Sums Values
– Max: Identifies the largest value
– Min: Identifies the smallest value
– Mean: Averages the values

Read more at www.cognigencorp.com/perspective/tipsNtricks.pub/1/PROC%20SQL%20Talk_12_.ppt –

SAS SQL

Introduction to Proc SQL

AN INTRODUCTION TO PROC SQL®

PROC SQL: When and How to Use It?

Proc SQL – A Primer for SAS Programmers

Understanding PROC SQL

Creating Macro Variables with Proc SQL

DATA Step vs. PROC SQL: What’s a neophyte to do?


Effective Use of SQL in SAS Programming

Variable List Short-Cuts in PROC SQL

Ten Best PROC SQL Tips and Techniques

Undocumented and Hard-to-Find PROC SQL C2 AE_Features

Variable List Short-Cuts in PROC SQL

Exploring the World of PROC SQL® Joins

Using PROC SQL to Create Ad Hoc Reports

CREATING MACRO VARIABLES VIA PROC SQL

PROC SQL for DATA Step Die-hards

Calculating Changes and Differences Using PROC SQL —With Clinical Data Examples

Validating Data Via PROC SQL

Length of Numeric variables GT 8 in SAS| StudySAS BLOG

Q&A: numeric variables length more than 8? We all know that the default length of the numeric variables in SAS is 8 and if suppose I want to store a number lets say (12345678910, which has a length 11 to numeric variable) to variable total, what should I do?


What if the numeric variable digits are more than 12 digits and i want to store them all without any E values?

ANS)

The default length of numeric variables in SAS is 8 and all the numbers that we see in the sas datasets are called as floating numbers(floating point binary) and not a regular sequence numbers form 1 to 10. When we are using SAS/Windows as our operating system and then the minimum length for any numeric variable should be 3(not 1 as we get confused all the time). So if a variable contas less than 3 digits means it is stored with less space.

The reason is, since a numeric variable will need a power and and the sign(+ or -), if SAS want to store a numberit defenitely needs a minimum of 3 bytes.

Depending upon the operating system we are using for SAS, the range for numeric variables can be 2 to 8 or 3 to 8.

Since the type of floating-point values is upto 16 decimal digits. we can store numbers upto 16(1234567891012234) total 16 digits for a numeric variable, but that can be possible if we use a format statement.

Ex:
data dsn;
a=1234567891012234;
format a best16.;run;

 If we open the output, we can see all the 16 digits were stored for the variable a exactly in the dataset dsn.

If the the value of numeric variable is upto 12 disgits we don't require to specify any formats, if it is more than 12 digits we have to mention specify the format statement. without it we will see error in the system log.