Thoughts of a Crime Show Junkie: Inadmissible Evidence
Recent Library Articles
Recently in the SAS Community Library: SAS' @RhondaWilliams reveals how SAS Law Enforcement Intelligence helps law enforcement agencies expedite detailed data entry using the Evidence Creation feature.
Hi,
From this table:
DATA HAVE;
INPUT CODE TYPE$ CATEGORY$;
CARDS;
15 A Y6D
15 B K45
08 A LS7
08 B G27
63 A A09
63 A Q98
63 B Z98
05 A P09
10 A W87
;
RUN;
I would like to obtain the following result:
Thanks
... View more
I am trying to perform SG Annotation with BY Variables. In this trivial example, I want one plot with a red arrow for the plot of Male data; and one plot for the females with a green arrow, depending on the value of SEX in SASHELP.CLASS
Here's my code that doesn't work (stealing from the example here).
%sganno
data sgannodata;
set sashelp.class(obs=2);
if sex='M' then do;
%sgarrow(x1=51, x2=30, y1=30, y2=30, linecolor="red")
end;
else if sex='F' then do;
%sgarrow(x1=51, x2=30, y1=30, y2=35, linecolor="green")
end;
keep sex;
run;
proc sort data=sashelp.class out=class;
by descending sex ;
run;
proc sgplot data=class sganno=sgannodata;
scatter x=age y=weight;
by descending sex;
run;
Here's the incorrect output (the correct output would be the red arrow on the males and the green arrow on the females, not both arrows on both plots)
... View more
I have a process where I am:
reading 67M rows from SQL Server to SAS; transforming the data using SAS; writing 67M rows from SAS to SQL Server.
no rows are dropped or created in the transformation processing. it's just fixing some incorrect values in historic data.
the source and target tables have identical schema
the target table is a heap (no indexes, keys, constraints)
The read operation is taking approx. 5 minutes; the write operation is taking approx. 5 hours. So, yeah 😬😱😠
Here are some excerpts from the log:
Libname statement:
27 %libname_sqlsvr(libref=PROD,server=MYSERVER,port=,database=MYDB,schema=prod)
=================================================================================
LIBREF: PROD
CONNECT: NOPROMPT="Driver={SQL Server Native Client
10.0};Server=MYSERVER;Database=MYDB;Trusted_Connection=yes;"
INTERNAL OPTIONS: schema=prod bulkload=yes dbcommit=100000 direct_exe=delete preserve_names=yes
USER OPTIONS:
=================================================================================
ODBC: AUTOCOMMIT is NO for connection 3
MPRINT(LIBNAME_SQLSVR): libname PROD odbc NOPROMPT="Driver={SQL Server Native Client
10.0};Server=MYSERVER;Database=MYDB;Trusted_Connection=yes;" schema=prod bulkload=yes dbcommit=100000
direct_exe=delete preserve_names=yes ;
NOTE: Libref PROD was successfully assigned as follows:
Engine: ODBC
Physical Name:
Read data from SQL Server to SAS:
27 * Copy data from SQL Server ;
ODBC: AUTOCOMMIT is NO for connection 6
ODBC_28: Prepared: on connection 6
SELECT * FROM "prod"."RLDX_DEMOGRAPHIC_ORIG"
28 data workspde.RLDX_DEMOGRAPHIC;
29 set prod.RLDX_DEMOGRAPHIC_ORIG;
30 run;
ODBC_29: Executed: on connection 6
Prepared statement ODBC_28
NOTE: There were 67,766,170 observations read from the data set PROD.RLDX_DEMOGRAPHIC_ORIG.
NOTE: The data set WORKSPDE.RLDX_DEMOGRAPHIC has 67,766,170 observations and 34 variables.
NOTE: Compressing data set WORKSPDE.RLDX_DEMOGRAPHIC decreased size by 75.73 percent.
NOTE: DATA statement used (Total process time):
real time 5:05.05
user cpu time 6:15.26
system cpu time 57.09 seconds
memory 1401.65k
OS Memory 22828.00k
Timestamp 25/03/2020 07:17:50 AM
Write data from SAS to SQL Server:
27 proc append
ODBC_24: Prepared: on connection 6
SELECT * FROM "prod"."RLDX_DEMOGRAPHIC" WHERE 0=1
ODBC: AUTOCOMMIT is NO for connection 7
ODBC: COMMIT performed on connection 7.
ODBC_25: Prepared: on connection 7
SELECT * FROM "prod"."RLDX_DEMOGRAPHIC"
28 base=prod.RLDX_DEMOGRAPHIC (
29 bulkload=yes bl_options=tablock
30 )
31 data=workspde.RLDX_DEMOGRAPHIC6 (
32 keep=
33 rldx_record_key
34 rldx_audit_key
35 recnum
36 stay_number
37 episode_sequence_number
38 hospital_type
39 source
40 hoscode
41 tfrhosp
42 transfrom
43 mrn
44 surname
45 givnames
46 sex
47 dob
48 age
49 address
50 locality
51 pcode
52 cob
53 admdate
54 sepdate
55 dthdate
56 notifdate
57 patientnum
58 supi
59 moh_auid
60 AHS_hosp
61 dc_extract_date
62 cob_sacc
63 state_of_usual_residence
64 rldx_valid_from
65 rldx_valid_to
66 rldx_current_ind
67 );
68 run;
NOTE: Appending WORKSPDE.RLDX_DEMOGRAPHIC6 to PROD.RLDX_DEMOGRAPHIC.
NOTE: There were 67,766,170 observations read from the data set WORKSPDE.RLDX_DEMOGRAPHIC6.
NOTE: 67766170 observations added.
NOTE: The data set PROD.RLDX_DEMOGRAPHIC has . observations and 34 variables.
NOTE: PROCEDURE APPEND used (Total process time):
real time 5:34:15.35
user cpu time 18:04.33
system cpu time 37.90 seconds
memory 634.87k
OS Memory 21804.00k
Timestamp 25/03/2020 03:34:13 AM
The write operation is a TRUNCATE TABLE (not shown) and PROC APPEND approach.
I note this previous thread: https://communities.sas.com/t5/General-SAS-Programming/Get-data-into-sql-server-more-efficiently/td-p/95560
I will continue playing with the libname and/or dataset options, but at 5 hours a pop for performance metrics, I thought I'd post to see if someone has further ideas. For example, is it possible that bulkloading needs to be tweaked on SQL Server itself (not that I'd know how to diagnose that, and I'm not the DBA).
... View more
It would be nice to be able to copy text from an individual cell in the list table. This is possible in a crosstab but it would be nice to mimic the excel function of copying from a regular table/cell for the list table.
... View more