Home

PaigeMiller

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)

ScottBass · ‎03-24-2020

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).

KKIND

proc genmod data=NIS.nis_2019_septicemia_ipexcmicat; class pay1recat (ref = "3") amonth (ref = "1") aweekend (ref = "0") died (ref = "0") elective (ref = "0") female (ref = "0") zipinc_qrtl (ref = "4") hosp_bedsize (ref = "3") hosp_locteach (ref = "1") hosp_region (ref = "1") aprdrg_risk_mortality (ref = "0") aprdrg_severity (ref = "0") pl_nchsrecat (ref = "1") racerecat (ref = "4") cmr_index_mortalitycat (ref = "1") / param=ref; model los = pay1recat age pay1recat*age amonth pay1recat*amonth aweekend died pay1recat*died elective pay1recat*elective female pay1recat*female i10_ndx pay1recat*i10_ndx i10_npr pay1recat*i10_npr zipinc_qrtl pay1recat*zipinc_qrtl hosp_bedsize pay1recat*hosp_bedsize hosp_locteach pay1recat*hosp_locteach hosp_region pay1recat*hosp_region aprdrg_risk_mortality pay1recat*aprdrg_risk_mortality aprdrg_severity pay1recat*aprdrg_severity pl_nchsrecat pay1recat*pl_nchsrecat racerecat pay1recat*racerecat cmr_index_mortalitycat pay1recat*cmr_index_mortalitycat totchg pay1recat*totchg / dist=negbin link=log; run; Log WARNING: The negative of the Hessian is not positive definite. The convergence is questionable. WARNING: The procedure is continuing but the validity of the model fit is questionable. WARNING: The specified model did not converge. WARNING: Negative of Hessian not positive definite.

vmurray

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.

zihdonv19

I tried ("cou" is 1,2,3,4,5.....; "var" is continuous variable with decimal points): proc sql; create table mt as select cou, pctl(75, var) as var_p75 from check_1 group by cou; quit; But it returns me with multiple rows per cou.