SAS

SAS导入导出时编码问题汇总

获得SAS默认编码(其实是通过启动时加载配置文件决定的，nls），

"D:\Program Files\SASHome9.4\SASFoundation\9.4\sas.exe" -CONFIG "d:\Program Files\SASHome9.4\SASFoundation\9.4\nls\zh\sasv9.cfg"

1	"D:\Program Files\SASHome9.4\SASFoundation\9.4\sas.exe" -CONFIG "d:\Program Files\SASHome9.4\SASFoundation\9.4\nls\zh\sasv9.cfg"

启动后无法修改，如果尝试通过下面命令设置，会得到警告,

option encoding='utf-8';

1	option encoding='utf-8';

WARNING 30-12: SAS option ENCODING is valid only at startup of the SAS System. The SAS option is …

奇怪的中文字符导致多个开源sas7bdat解析程序崩溃

最近在用开源软件解析sas7bdat时，经常碰到这样的错误(haven，readstat），

ReadStat: Error parsing page 0, bytes 262144-524287
Error processing test.sas7bdat: Unable to convert string to the requested encoding (invalid byte sequence)

1 2	ReadStat: Error parsing page 0, bytes 262144-524287 Error processing test.sas7bdat: Unable to convert string to the requested encoding (invalid byte sequence)

或者(parso），

 --- exec-maven-plugin:1.2.1:exec (default-cli) @ sas ---
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
Exception in thread "main" org.h2.jdbc.JdbcSQLException: Exception calling user-defined function: "sasRead(conn1: url=jdbc:columnlist:connection user=SA, /Users/steven/sas/data/test.sas7bdat, null): String index out of range: 44"; SQL statement:
create table sas as SELECT * FROM SASREAD('/Users/steven/sas/data/test.sas7bdat', null) [90105-191]
 at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
 at org.h2.message.DbException.get(DbException.java:168)
 at org.h2.message.DbException.convertInvocation(DbException.java:312)
 at org.h2.engine.FunctionAlias$JavaMethod.getValue(FunctionAlias.java:493)
 at org.h2.expression.JavaFunction.getValueForColumnList(JavaFunction.java:126)
 at org.h2.table.FunctionTable.<init>(FunctionTable.java:66)
 at org.h2.command.Parser.readTableFilter(Parser.java:1237)
 at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1884)
 at org.h2.command.Parser.parseSelectSimple(Parser.java:2032)
 at org.h2.command.Parser.parseSelectSub(Parser.java:1878)
 at org.h2.command.Parser.parseSelectUnion(Parser.java:1699)
 at org.h2.command.Parser.parseSelect(Parser.java:1687)
 at org.h2.command.Parser.parseCreateTable(Parser.java:6007)
 at org.h2.command.Parser.parseCreate(Parser.java:4217)
 at org.h2.command.Parser.parsePrepared(Parser.java:360)
 at org.h2.command.Parser.parse(Parser.java:315)
 at org.h2.command.Parser.parse(Parser.java:287)
 at org.h2.command.Parser.prepareCommand(Parser.java:252)
 at org.h2.engine.Session.prepareLocal(Session.java:560)
 at org.h2.engine.Session.prepareCommand(Session.java:501)
 at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1188)
 at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:170)
 at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:158)
 at com.alitrack.h2.Function.main(Function.java:40)
Caused by: java.lang.StringIndexOutOfBoundsException: String index out of range: 44
 at java.lang.String.substring(String.java:1950)
 at com.epam.parso.impl.SasFileParser$ColumnNameSubheader.processSubheader(SasFileParser.java:1209)
 at com.epam.parso.impl.SasFileParser.processPageMetadata(SasFileParser.java:361)
 at com.epam.parso.impl.SasFileParser.processSasFilePageMeta(SasFileParser.java:331)
 at com.epam.parso.impl.SasFileParser.getMetadataFromSasFile(SasFileParser.java:231)
 at com.epam.parso.impl.SasFileParser.<init>(SasFileParser.java:208)
 at com.epam.parso.impl.SasFileParser.<init>(SasFileParser.java:44)
 at com.epam.parso.impl.SasFileParser$Builder.build(SasFileParser.java:996)
 at com.epam.parso.impl.SasFileReaderImpl.<init>(SasFileReaderImpl.java:63)
 at com.alitrack.h2.Function.sasRead(Function.java:51)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:483)
 at org.h2.engine.FunctionAlias$JavaMethod.getValue(FunctionAlias.java:481)
 ... 20 more

--- exec-maven-plugin:1.2.1:exec (default-cli) @ sas ---

SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".

SLF4J: Defaulting to no-operation (NOP) logger implementation

SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.

Exception in thread "main" org.h2.jdbc.JdbcSQLException: Exception calling user-defined function: "sasRead(conn1: url=jdbc:columnlist:connection user=SA, /Users/steven/sas/data/test.sas7bdat, null): String index out of range: 44"; SQL statement:

create table sas as SELECT * FROM SASREAD('/Users/steven/sas/data/test.sas7bdat', null) [90105-191]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)

at org.h2.message.DbException.get(DbException.java:168)

at org.h2.message.DbException.convertInvocation(DbException.java:312)

at org.h2.engine.FunctionAlias$JavaMethod.getValue(FunctionAlias.java:493)

at org.h2.expression.JavaFunction.getValueForColumnList(JavaFunction.java:126)

at org.h2.table.FunctionTable.<init>(FunctionTable.java:66)

at org.h2.command.Parser.readTableFilter(Parser.java:1237)

at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1884)

at org.h2.command.Parser.parseSelectSimple(Parser.java:2032)

at org.h2.command.Parser.parseSelectSub(Parser.java:1878)

at org.h2.command.Parser.parseSelectUnion(Parser.java:1699)

at org.h2.command.Parser.parseSelect(Parser.java:1687)

at org.h2.command.Parser.parseCreateTable(Parser.java:6007)

at org.h2.command.Parser.parseCreate(Parser.java:4217)

at org.h2.command.Parser.parsePrepared(Parser.java:360)

at org.h2.command.Parser.parse(Parser.java:315)

at org.h2.command.Parser.parse(Parser.java:287)

at org.h2.command.Parser.prepareCommand(Parser.java:252)

at org.h2.engine.Session.prepareLocal(Session.java:560)

at org.h2.engine.Session.prepareCommand(Session.java:501)

at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1188)

at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:170)

at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:158)

at com.alitrack.h2.Function.main(Function.java:40)

Caused by: java.lang.StringIndexOutOfBoundsException: String index out of range: 44

at java.lang.String.substring(String.java:1950)

at com.epam.parso.impl.SasFileParser$ColumnNameSubheader.processSubheader(SasFileParser.java:1209)

at com.epam.parso.impl.SasFileParser.processPageMetadata(SasFileParser.java:361)

at com.epam.parso.impl.SasFileParser.processSasFilePageMeta(SasFileParser.java:331)

at com.epam.parso.impl.SasFileParser.getMetadataFromSasFile(SasFileParser.java:231)

at com.epam.parso.impl.SasFileParser.<init>(SasFileParser.java:208)

at com.epam.parso.impl.SasFileParser.<init>(SasFileParser.java:44)

at com.epam.parso.impl.SasFileParser$Builder.build(SasFileParser.java:996)

at com.epam.parso.impl.SasFileReaderImpl.<init>(SasFileReaderImpl.java:63)

at com.alitrack.h2.Function.sasRead(Function.java:51)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:483)

at org.h2.engine.FunctionAlias$JavaMethod.getValue(FunctionAlias.java:481)

... 20 more

造成这样错误的sas7bdat由下面的代码生成，

libname lib "Z:\data";
options validvarname=any;

/*生成的sas文件，解析报错*/
data lib.fails;
柯妮丝麗=234;
run;

／*不报错，变量可以是别的中文字符
data lib.works;
测试=123;
run;
*／

libname lib "Z:\data";

options validvarname=any;

/*生成的sas文件，解析报错*/

data lib.fails;

柯妮丝麗=234;

run;

／*不报错，变量可以是别的中文字符

data lib.works;

测试=123;

run;

*／

继续尝试了sas7bdat.py，通过，

from sas7bdat import SAS7BDAT
f= SAS7BDAT('c:/soft/fails.sas7bdat',encoding='GBK') 
df = f.to_data_frame()
df
f.convert_file('c:/soft/fails.csv')

from sas7bdat import SAS7BDAT

f= SAS7BDAT('c:/soft/fails.sas7bdat',encoding='GBK')

df = f.to_data_frame()

f.convert_file('c:/soft/fails.csv')

P.S. 刚刚Mac下测试了最新的haven通过，

>library(haven)
>read_sas("/data/fails.sas7bdat",encoding = "GBK")
# A tibble: 1 x 1
  柯妮丝麗
     <dbl>
1      234

>library(haven)

>read_sas("/data/fails.sas7bdat",encoding = "GBK")

# A tibble: 1 x 1

柯妮丝麗

<dbl>

1 234

sas7bdat (R版本），可以解析，但不输出中文变量，而是用一组特殊字符（比如，X.bf..c2..c4..dd…fb..90）替代。

>library(sas7bdat)
>read.sas7bdat("/sas/data/fails.sas7bdat",debug = F)
X.bf..c2..c4..dd...fb..90.
1                        234

>library(sas7bdat)

>read.sas7bdat("/sas/data/fails.sas7bdat",debug = F)

X.bf..c2..c4..dd...fb..90.

1 234

MySQL下group_concat函数的SAS实现

MySQL下的字符串聚合函数group_concat是一个非常实用的字符串合并函数， sas下的宏实现，

%macro group_concat(input, output, id, string, length);

data &output (drop=string_old);
set &input (rename=(&string=string_old));
by &id;
retain &string;
length &string $ &length; * set large enough to accommodate the maximum number of records per ID;
if first.&id then &string = '';
brand = catx(',',trim(&string),string_old);
if last.&id then output;
run;

%mend group_concat;

%macro group_concat(input, output, id, string, length);

data &output (drop=string_old);

set &input (rename=(&string=string_old));

by &id;

retain &string;

length &string $ &length; * set large enough to accommodate the maximum number of records per ID;

if first.&id then &string = '';

brand = catx(',',trim(&string),string_old);

if last.&id then output;

run;

%mend group_concat;

基本表结构如下, 按字母排序的变量和属性列表 # 变量类型长度输出格式输入格式标签 2 brand 字符 40 $40. $40. brand 1 customer_id 字符 …

SAS不同版本数据库兼容问题

客户（使用SAS8.2）收到我提供的SAS数据库（SAS9.4），说无法打开，

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set LIB.HELLO may be incomplete.  When this step was stopped there were 0
         observations and 0 variables.

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set LIB.HELLO may be incomplete. When this step was stopped there were 0

observations and 0 variables.

网上寻找答案，说可以指定逻辑库的版本号，比如SAS8.2支持V7，

options validvarname=any;
libname lib9  v9 "F:\data\v9";
libname lib8  v8 "F:\data\v8";
libname lib7  v7 "F:\data\v7";
libname lib6  v6 "F:\data\v6";
libname lib       "F:\data";



data lib.rfm;
set lib9.rfm;
run;

data lib8.rfm;
set lib9.rfm;
run;

data lib7.rfm;
set lib9.rfm;
run;

data lib6.rfm;
set lib9.rfm;
run;

options validvarname=any;

libname lib9 v9 "F:\data\v9";

libname lib8 v8 "F:\data\v8";

libname lib7 v7 "F:\data\v7";

libname lib6 v6 "F:\data\v6";

libname lib "F:\data";

data lib.rfm;

set lib9.rfm;

run;

data lib8.rfm;

set lib9.rfm;

run;

data lib7.rfm;

set lib9.rfm;

run;

data lib6.rfm;

set lib9.rfm;

run;

使用v7生成的数据库，SAS8.2仍然不支持，我试着用SAS9.3来同样做了一遍，OK了。备注：测试SAS8.2，SAS9.3和SAS9.4均只支持V6的读，不支持V6的写。

SAS 连PostgreSQL的几种方法

SAS 9.4之前，需要ODBC的方式连接PostgreSQL（记得提前配置好DSN）,这些方法不仅仅实用于PostgreSQL，很多常用的数据库都可以通过ODBC或者直接连接的方式访问。方法一，ODBC里执行PostgreSQL的SQL语句

PROC SQL;
CONNECT TO ODBC(DSN="ml"); 

 /* con-Name : is the ODBC connection name. Connection should be pre established */
CREATE TABLE temp_sas AS
SELECT * FROM CONNECTION TO ODBC( SELECT clustername
, clustername::varchar(20) as clustername1,count(*) cnt FROM abc.sms group by 1,2 );
QUIT;

/* Inside the ODBC( )  you can write complex queries 
instead of single table query shown above*/
/*ODBC( )内可以写任何PostgreSQL支持的SQL语法，比如开窗函数,还可以方便支持schema*/

PROC SQL;

CONNECT TO ODBC(DSN="ml");

/* con-Name : is the ODBC connection name. Connection should be pre established */

CREATE TABLE temp_sas AS

SELECT * FROM CONNECTION TO ODBC( SELECT clustername

, clustername::varchar(20) as clustername1,count(*) cnt FROM abc.sms group by 1,2 );

QUIT;

/* Inside the ODBC( ) you can write complex queries

instead of single table query shown above*/

/*ODBC( )内可以写任何PostgreSQL支持的SQL语法，比如开窗函数,还可以方便支持schema*/

方法二，建逻辑库

libname psql odbc datasrc = 'ml' schema = abc PRESERVE_TAB_NAMES=yes;

proc contents data=psql.sms;run;

libname psql odbc datasrc = 'ml' schema = abc PRESERVE_TAB_NAMES=yes;

proc contents data=psql.sms;run;

方法三，不使用DSN，而是配置ODBC.ini 配置文件（未测试过，这个比较适合Linux环境）

[[ODBC|Data Sources]] 
databasename = the database description

[[database_name]]
Description         = PostgreSQL
Driver              = /usr/lib/x86_64-linux-gnu/odbc/psqlodbca.so
Trace               = No
TraceFile           = /tmp/psqlodbc.log
Database            = databasename
Servername          = servername
UserName            = username
Password            = password
Port                = 5432
ReadOnly            = Yes
RowVersioning       = No
ShowSystemTables    = No
ShowOidColumn       = No
FakeOidIndex        = No
ConnSettings        =

[[ODBC|Data Sources]]

databasename = the database description

[[database_name]]

Description = PostgreSQL

Driver = /usr/lib/x86_64-linux-gnu/odbc/psqlodbca.so

Trace = No

TraceFile = /tmp/psqlodbc.log

Database = databasename

Servername = servername

UserName = username

Password = password

Port = 5432

ReadOnly = Yes

RowVersioning = No

ShowSystemTables = No

ShowOidColumn = No

FakeOidIndex = No

ConnSettings =

方法四，SAS 9.4开始可以直接连接PostgreSQL等多种数据库了，使用更简单了，

%let server="192.168.0.99";
%let port=5432;
%let user=postgres;
%let pass=xx;
%let db=ml;
%let schema=abc;
%let autocommit=yes

* Connection string;
libname pgdb postgres server=&server. port=&port.
   user=&user. password=&pass. database=&db. autocommit=&autocommit. schema=&schema.;

proc contents data=pgdb.sms;run;

%let server="192.168.0.99";

%let port=5432;

%let user=postgres;

%let pass=xx;

%let db=ml;

%let schema=abc;

%let autocommit=yes

* Connection string;

libname pgdb postgres server=&server. port=&port.

user=&user. password=&pass. database=&db. autocommit=&autocommit. schema=&schema.;

proc contents data=pgdb.sms;run;

SAS导入PostgreSQL的注意事项， PostgreSQL鼓励使用Text（不指定长度的varchar），但SAS会因为它不指定长度，而使用默认的最大长度（1024），这样导致SAS库变得非常大，而访问非常缓慢,如下图所示，在PostgreSQL里，clustername是text，clustername1则是varchar(20)，所以记得重新定义下长度，这种情况下，ODBC里执行PostgreSQL的命令相对最好用。 P.S. 从SAS9.4开始，SAS/ACCESS 接口增加了更多的数据库支持， Amazon Redshift, Aster, …

实用SAS 代码片段

SAS表的Name是类似_COL0，_COL1，_COL2这样的字段，Label是中文名，直接导出到PostgreSQL的话，字段实用的是Name,

NOTE: SAS 变量标签、格式和长度并未写入 DBMS 表。

1	NOTE: SAS 变量标签、格式和长度并未写入 DBMS 表。

这时候需要做rename，代码，

libname sas"X:\job\xxx\data";
libname psql odbc datasrc = 'PostGreSQL30' schema = alitrack PRESERVE_TAB_NAMES=yes;

*sas.Member的表结构信息输出到sas.Member_label
proc contents data=sas.Member out=sas.Member_label noprint;
run;

*拼接rename字段
proc sql;
select 'rename '||name||'='||label||';' from sas.Member_label;
quit;

*rename
data sas.member_1;set sas.Member;
rename _COL0 =会员帐号 ; 
rename _COL1 =有效日从 ; 
rename _COL2 =日历年月 ; 
rename _COL3 =有效期至 ; 
rename _COL4 =客户 ; 
rename _COL5 =性别 ; 
rename _COL6 =出生日期 ; 
run;

*输出SAS表sas.member_1 到PostgreSQL 
data psql.member;set sas.member_1;run;

libname sas"X:\job\xxx\data";

libname psql odbc datasrc = 'PostGreSQL30' schema = alitrack PRESERVE_TAB_NAMES=yes;

*sas.Member的表结构信息输出到sas.Member_label

proc contents data=sas.Member out=sas.Member_label noprint;

run;

*拼接rename字段

proc sql;

select 'rename '||name||'='||label||';' from sas.Member_label;

quit;

*rename

data sas.member_1;set sas.Member;

rename _COL0 =会员帐号 ;

rename _COL1 =有效日从 ;

rename _COL2 =日历年月 ;

rename _COL3 =有效期至 ;

rename _COL4 =客户 ;

rename _COL5 =性别 ;

rename _COL6 =出生日期 ;

run;

*输出SAS表sas.member_1 到PostgreSQL

data psql.member;set sas.member_1;run;

Currently browsing category