SAS Archives - Page 8 of 11

Raspberry Pi (2): Terminal Commands and Navigation

Reference: https://pythonprogramming.net/terminal-navigation-raspberry-pi-tutorials/?completed=/remote-access-raspberry-pi-tutorials/

Code – Use

pwd – print working directory defacult is /home/pi, also is ~
cd – change directory: cd /home ; cd ~ ; cd / tab (help to fill possible command) ; cd .. (go back one level of directory); cd ../../ (go back two levels of directories)
ls – list contents of directory
mkdir – make directory: mkdir exemple (make example folder)

Test Python

nano test.py
print (‘hi’)
ctrl+x; Y
python test.py

Download from Internet

wget https://www.pythonprogramming.net/static/images/categories/3D-Matplotlib.png
sudo apt-get install git
sudo apt-get autoremove
git clone https://github.com/vinta/awesome-python

Remove file/directory

rm 3D-Matplotlib.png (case sensitive)
rmdir new
rmdir awesome-python
rm -r awesome-python (force removing)

ls -a ; ls -all (show all contents, including hidden contents)
ls -l (contents in list format)
ls -al (combine the above attributes)
flag: – for short hand and — for full word
ls –help

reboot and shut down raspberry pi

sudo reboot
sudo shutdown -h now
sudo shutdown -r now

ODS Listing

ODS Tips

Output to text file

[code lang=text]
ODS LISTING file='C:\Users\david\Documents\My SAS Files(32)\9.3\ods\sample.lst';
OPTIONS NONUMBER NODATE;
PROC PRINT DATA=SASHELP.cars(OBS=100);
VAR make model type origin drivetrain msrp invoice enginesize cylinders horsepower mpg_city mpg_highway;
RUN;
OPTIONS DATE;
PROC FREQ DATA=SASHELP.cars;
TABLES make type origin drivetrain enginesize cylinders ;
RUN;
proc means data=sashelp.cars mean median min max skew;
class type origin;
var msrp enginesize horsepower mpg_city mpg_highway weight wheelbase length;
run;
ODS LISTING CLOSE;
[/code]

.lst file can be opened in SAS by dragging the file to the program edit window.
click file in the file explorer window will launch sas enterprise guide
ods listing can be used for record keeping and to check the validity of the sas programming.

Access VBA Report Printing to pdf Problem and “Formatting Page” Problem

Task

The task is to generate pdf documents in the file folder from Access database using the parameters generated by a sql distinct select statement. The parameters will be passed to the Access report preview as the filter value and used to form the file name of the pdf.

Problem 1

Keep getting “Formatting Page” on the status bar and the execution of the VBA seems stuck in that status.

Solution 1

Check properties of all the headers to make sure “Keep Together” (under Format tab) is equal to “No”

Cause

Access is trying to calculate how to best fit the contents together whick takes time.

Solution 2

Remove the “Pages” function in the footer

Cause

“Pages” function will force Access to determine the last page and the report can’t be previewed until formatting are applied to all the pages of the report and the last page is calculated.

Problem 2

Access was not able to create pdf document and stopped at certain parameter intake.

Solution 1

Replace the “/” character in the value of the variable
var = Replace(rs(“Column_Name”), “/”, “_”)

Cause

The parameters that were passed to pdf filename include characters like “/” and “:”, which are reserved by MS for other purpose and can’t not be used as part of the MS filename.

###Solution 2###

check the length of string that was used a parameter for the filename and shorten the string in case it is too long
If len(rs(“Column_Name”)) > 35 then var = left(rs(“Column_Name), 35)

Cause

Microsoft has a limit of 255 characters on filename.

Source:

PCReview.co.uk

Reading Data from fixed position txt file

Read data from fixed position txt file to SAS.

This type of file normally don’t have the variable name as the first observation because the length of the variable name might not be consistent with the length of the variable. The starting position for each variable is the same for all the records in the txt file.

Below in the admit table in fixed position txt file.

2458 Murray, W      M 27 1  72 168 HIGH 85.20
2462 Almers, C      F 34 .  66 152 HIGH 124.80
2501 Bonaventure, T F 31 .  61 123 LOW  149.75
2523 Johnson, R     M 43 31 63 137 MOD  149.75
2539 LaMance, K     M 51 4  71 158 LOW  124.80
2544 Jones, M       M 29 6  76 193 HIGH 124.80

Require . for missing value
use $ in input statement to identify character variable
use X-X to identify the starting position and ending position for the character variables. This also determines the length of the character variable.
use @x to identify the starting position for the numeric variable.
use x. to identify the length of the numeric variable. eg: @23 age 2. indicates to read from position 23 the age variable with a length of 2.

Method1: not identify the position for the numeric variable.

data admit;
infile 'C:\Users\irisan\Documents\My SAS Files(32)\9.3\Tutorial\admit_fixed.txt' ;
input ID $ 1-4 name $ 6-19 sex $ 21 age date height weight actlevel $ 36-39 fee;
run;

Method2: identify the position for each variable.

data admit;
infile 'C:\Users\irisan\Documents\My SAS Files(32)\9.3\Tutorial\admit_fixed.txt' ;
input
ID $ 1-4
name $ 6-19
sex $ 21
@23 age 2.  /* length for age is 2 */
@26 date 
@29 height
@32 weight
actlevel $ 36-39
@41 fee ;
run;

Read only certain observations that meet the criteria for selected variables from the txt file, using 2 input statements and if statement. Second input statement only contains the required variables, so not all the variables are read into the target SAS dataset.

data admit;
infile 'C:\Users\irisan\Documents\My SAS Files(32)\9.3\Tutorial\admit_fixed.txt' ;
input actlevel $ 36-39 @23 age  @;
if  age < 35 and actlevel EQ 'HIGH' then delete;
input
name $ 6-19
sex $ 21
@29 height
@32 weight
@41 fee ;
run;

SAS: Organizing data through Proc Format

Proc Format can be used to group categorical variables or numeric values for reporting. The following examples show the coversion of sex, age, and id variables into desired format. Method 1 uses put and input statements and creates new variables, while method 2 use format statement to just format the existing variables.

Need $ in the format name for the existing character variable. eg. $gender
Need to put . after the format name when applying the format. eg. gender = put(sex, $gender.); format age agerange. sex $gender.;

/**** Proc Format ****/
proc format;
value
$gender
‘M’ = ‘Male’
‘F’ = ‘Female’
;
value
agerange
1 – 30 = ‘le 30’
30<-40 = '31 to 40'
40<-50 = '41 to 50'
50<-60 = '51 to 60'
60<-100 = 'gt 60'
other = 'Missing'
;
run;
options fmtsearch = (work.formats);

Method1:

data admit;
set sasuser.admit;
agecatgory = put(age, agerange.);
gender = put (sex, $gender.);
numericid = input(id, 4.);
run;

Method2:

data admit;
set sasuser.admit;
format age agerange. sex $gender.;
run;

Character to Numeric, using INVALUE.
Example: convert character gpa to numeric gpa.

/*** calculate the ESL grade points that needs to be backed out from the GPA ***/
/** format grade to index **/
proc format ; 
invalue   /* UES INVALUE, NOT VALUE */
$grade
'A+' = 9
'A'= 8
'B+'= 7
'B'= 6
'C+' = 5
'C' = 4
'D+' = 3
'D' = 2
'E' = 1 
'F' = 0
'P' = 0
;
run;
proc options  option=fmtsearch;run;
data backgpa;
set pes;
array crs{3} esl1000 esl1010 esl1015  ;
array ncr{3} esl1000ncr esl1010ncr esl1015ncr  ;
array cr{3} esl1000cr esl1010cr esl1015cr  ;
array back{3} esl1000bo esl1010bo esl1015bo;
do i =1 to dim(crs);
if crs{i} ne '' and ncr{i} ne 'NCR' then  back{i} =  input(crs{i}, $Grade.) * input(cr{i}, 8.);
end;
drop course1-course20 gr1-gr20 credit1-credit20 ncr1-ncr20 i;
run;

SAS: Reading Data from tab delimited txt file

Read tab delimited txt file to sas.

data admit;
length name $14.;
infile ‘C:\Users\Documents\My SAS Files(32)\9.3\Tutorial\admit.txt’ dlm=’09’x firstobs =2 ;
input ID $4. name $ sex $1. age date height weight actlevel $4. fee;
run;

missing value in the txt file should be either . or blank space.
use dlm=’09’x to read the tab delimited file
SAS default character variable length is 8. In order to read the name variable, need to add length statement before the infile statement, otherwise the name field will be truncated at 8.
add firstobs =2 if the first line in the txt file is the variable names.
use lrecl= 32760 if the observation is very long (longer than the default 256).

Read only certain observations that meet the criteria from the tab delimited file to sas.

Method1:

data admit;
length name $14.;
infile 'C:\Users\Documents\My SAS Files(32)\9.3\Tutorial\admit.txt' dlm='09'x firstobs =2;
input ID $4. name $ sex $1. @;
if sex = 'F' then delete;
input age date height weight actlevel $4. fee;
run;

Method2:

data admit;
length name $14.;
infile 'C:\Users\irisan\Documents\My SAS Files(32)\9.3\Tutorial\admit.txt' dlm='09'x firstobs =2;
input ID $4. name $ sex $1. @ ;
if sex = 'M' then do;
input  age date height weight actlevel $4. fee;end;
else delete;
run;

use 2 input statements to select ‘M’ admits.
in the first input statement, use one trailing @ to tell SAS to hold the record for if statement. In method 1, the ‘F’ records will not be passed on to the second input statement.
for tab delimited data, it is not possible to only input selected variables from the source file. All the variables need to be input in the variable sequence of source file from left to right.

SAS: Proc Report Design feature with Percentage and Sub-total

There are two procedures commonly used for reporting purpose in SAS, PROC Report and PROC Tabulate. In this post, I will focus more of PROC Report because it is more convenient to produce column percentage by group and the sub-total. The references of column in the computation of percentages are different between a cross-tab report and the regular one dimensional report.

1. Cross-tab report design

Source table layout (Varn will be any other categorical variable that not contribute to the calculation of the percentage)

Variables	Type
Year	Ordinal
Var1	Categorical/Nominal
Var2	Ordinal
Var3	Continous

Desired Report Output

			Year
			Year1		Year2		Year3
Var1	Varn	Var2	sum of Var3	% of sum by Var1	sum of Var3	% of sum by Var1	sum of Var3	% of sum by Var1
xxxxx	xxxxx	xxxxx	xxx	xx%	xxx	xx%	xxx	xx%
		xxxxx	xxx	xx%	xxx	xx%	xxx	xx%
		xxxxx	xxx	xx%	xxx	xx%	xxx	xx%
sub-total			xxx	100%	xxx	100%	xxx	100%
xxxxx	xxxxx	xxxxx	xxx	xx%	xxx	xx%	xxx	xx%
		xxxxx	xxx	xx%	xxx	xx%	xxx	xx%
		xxxxx	xxx	xx%	xxx	xx%	xxx	xx%
sub-total			xxx	100%	xxx	100%	xxx	100%
xxxxx	xxxxx	xxxxx	xxx	xx%	xxx	xx%	xxx	xx%
		xxxxx	xxx	xx%	xxx	xx%	xxx	xx%
		xxxxx	xxx	xx%	xxx	xx%	xxx	xx%
sub-total			xxx	100%	xxx	100%	xxx	100%

Code

Proc report data = sample nowd ;
column var1 varn var2 year,(var3 ptvar3);
define var1 / group;
define varn / group;
define var2 /group;
define  year / across 'Year';
define var3 / sum f=7.2 'SUM';
define ptvar3 /computed f=percent8.2 '%';
/* Sum total var3 by year */
Break after var1  / summarize;
compute before var1;
den0 = _c4_;
den1 = _c6_;
den2 = _c8_;
endcomp;
/* Calculate percentage */
compute ptvar3;
_c5_ = _c4_ / den0;
_c7_ = _c6_ / den1;
_c9_ = _c8_ / den2;
endcomp;
run;

2. One dimensional report

Source table layout

Variables	Type
Var1	Categorical/Nominal
Var2	Ordinal
Var3	Continous

Desired Report Output

Var1	Varn	Var2	sum of Var3	% of sum by Var1
xxxxx	xxxxx	xxxxx	xxx	xx%
		xxxxx	xxx	xx%
		xxxxx	xxx	xx%
sub-total			xxx	100%
xxxxx	xxxxx	xxxxx	xxx	xx%
		xxxxx	xxx	xx%
		xxxxx	xxx	xx%
sub-total			xxx	100%
xxxxx	xxxxx	xxxxx	xxx	xx%
		xxxxx	xxx	xx%
		xxxxx	xxx	xx%
sub-total			xxx	100%

Code

Proc report data = sample  nowd ;
column var1 varn var2 var3 ptvar3;
define var1 / group;
define varn / group;
define var2 /group;
define var3 / sum f=7.2 'Var3';
define ptvar3 /computed f=percent8.2 '%';
Break after var1  / summarize;
compute before var1;
var3den = var3.sum;
endcomp;
/* Calculate percentage */
compute ptvar3;
ptvar3 = var3.sum / var3den;
endcomp;
run;

Note:

for cross-tab report, in column statement use “year,”. There is a ‘,’ after the variable.
if there is more than one variable in the value section of the cross-tab report, use ‘( )’ to include all the value variables needed in the column statement. eg. ‘year, (var3 ptvar3)’
use break statement to provide sub-totals for the report;
for computing the percentage by group, denX variables are used to provide sum value of the group and _cX_ variables are used to hold all results for the column value. The column number X in _cX_ needs to be matched with the exact column number in the output.
for computing percentage for one dimensional report, refer to the variable name directly (eg. ptvar3 = var3.sum/ var3den) in the formula. Refer to the column number will not work in this case.

reference:

http://support.sas.com/kb/49/390.html

http://support.sas.com/kb/43/091.html

SAS: Join or Merge Tables

Note: Be careful to use merge if the the byvar are not unique in both tables, especially when the vars are numeric and can be aggregated .

Compare of SQL method and merge method

Inner Join

SELECT var
FROM table1 as a
INNER JOIN table2 as b
ON a.var=b.var;

merge table1 (in=a) table2 (in=b);
by var;
if a and b;

Left Join

SELECT var
FROM table1 as a
LEFT JOIN as b
ON a.var=b.var; 

merge table1 (in=a) table2;
by var;
if a;

Left Join exclude Inner Join

SELECT var
FROM table1 as a
LEFT JOIN table as b
ON a.var=b.var;
WHERE b.var IS NULL;

merge table1 (in = a) table2 (in=b);
by var;
if a and not b;

Right Join

SELECT var
FROM table1 as a
RIGHT JOIN table2 as b
ON a.var=b.var;

merge table1 (in=a) table2 (in=b);
by var;
if b;

Right Join exclude Inner Join

SELECT var
FROM table1 as a
RIGHT JOIN table as b
ON a.var=b.var

WHERE a.var IS NULL;

merge table1 (in = a) table2 (in=b);
by var;
if b and not a;

Full Join

SELECT var
FROM table1 as a
FULL OUTER JOIN table2 as b
ON a.var=b.var;

merge table1 (in=a) table2 (in=b);
by var;
if a or b;

merge table1 (in = a) table2 (in=b);
by var;

Full Join Inner Join

SELECT var
FROM table1 as a
FULL OUTER JOIN table2 as b
ON a.var=b.var;

merge table1 (in=a) table2 (in=b);

by var;
if a ne b; /* only work on 2 table merge*/

Macro: Check variable length, name, type of multiple tables with similar layout

/* set up list for the tables requied to scan for the variables */

%let tlst = a b c d e f g;

/* Method 1: use parameter */

/* Generate vairable list for each table */

%macro varlen (sec=);
%let i = 1;
%do %while (%scan(&sec., &i, ‘ ‘) ne );
%let tbl= %scan(&sec., &i, ‘ ‘);
proc contents data = section&tbl._ug out=ug&tbl. (keep= MEMNAME name type length) noprint;
proc contents data = section&tbl._gr out=gr&tbl. (keep= MEMNAME name type length) noprint;
%let i = %eval (&i +1);
%end;
run;
%mend varlen;
%varlen (sec = &tlst.);

/* Method 2: use macro variable list directly */

/* Append varaible lists to one table */
%macro comp ;
%let i = 1;
%do %while (%scan(&tlst., &i, ‘ ‘) ne );
%let tbl= %scan(&tlst., &i, ‘ ‘);
proc append base = uglen data = ug&tbl.;
run;
%let i = %eval (&i +1);
%end;
%let i = 1;
%do %while (%scan(&tlst., &i, ‘ ‘) ne );
%let tbl= %scan(&tlst., &i, ‘ ‘);
proc append base = grlen data = gr&tbl.;
run;
%let i = %eval (&i +1);
%end;
proc sort data = uglen;
by name memname;
proc sort data = grlen;
by name memname;
run;
%mend comp;
%comp ;

SAS: Input Raw data with different length of character variable

data map;
infile datalines delimiter=',';
length faculty $2 progname $90. facdesc $25.;
input  faculty $ progname $ facdesc $;
datalines;
AB, AB-Digital Media, , Faculty AB
AB, AB-Interdisc. Fine Arts, Faculty AB
CD,SC-Biology,Science, Faculty CD
;
run;

NOTE:

Can’t use Input statement to set length for the variable. Input statement only identify the character variables by putting $ after the variable name.
Use ‘,’ to separate the value of the variable in the datalines; no need to put ‘;’ to end each dataline, just end the whole datalines section with one ‘;’.
Put Length statement before the Input statement to set the desired variable length for the data needed input.

BENEFIT:

No need to use “” for character values that contain spaces.
No need to align the variables in datalines.