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.
The function split_part(col_name,delimiter, index) worked well with select statement in the HUE editor when connected to impala. The same function throws an error when running in SAS EG. Below is the log. I found a workaround and delivered the file. However, I want to know if anyone here knows a way to submit the code via SAS without being losing the ability to use Impala functions that SAS Access doesn’t support. Log: 66 proc sql noprint; 67 connect to impala as XXX (dsn=XXX user=XXXpw = "XXX"); 68 create table temp(compress=yes) as 69 select * 70 from connection to XXX ( 71 select 72 split_part(col, '~', 1) as col1 73 from table1 a , table2 c , table3 b 74 where condition); NOTE: Compression was disabled for data set XXX because compression overhead would increase the size of the data set. ERROR: CLI cursor extended fetch error: [Cloudera][Support] (40550) Invalid character value for cast specification. : [Cloudera][Support] (40550) Invalid character value for cast specification. : [Cloudera][Support] (40550) Invalid character value for cast specification. : [Cloudera][Support] (40550) Invalid character value for cast specification. : [Cloudera][Support] (40550) Invalid character value for cast specification. : [Cloudera][Support] (40550) Invalid character value for cast specification. : [Cloudera][Support] (40550) Invalid charact NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 80 disconnect from XXX; NOTE: Statement not executed due to NOEXEC option. 81 quit;
... View more
Hi, Can anybody help me with formatting the datetime correctly in my program? I am mostly getting blank columns or getting the date as 01Jan1960:9:25:00 Etc. Here's the code I am using data x; set x; y_new = input(y, andtdtm.); format y_new datetime22.; run; And here's how some values in the datetime column look like when imported into SAS: 45348.3506829051 45349.8328009028 45350.7706134028 Thank you! Best regards, Abhishek
... View more
data vs ; input patient cpevent$ vstest$ vsren vsorresu$; cards ; 100 base pr 78 min 100 scrn hr 72 /min 100 week1 sbp 70 mmhg 100 week7 dbp 110 mm 100 week21 weight 75 kg
100 fwp height 120 kg 100 base pr 78 min 100 scrn hr 79 /mn 100 week1 sbp 70 mmhg 100 week7 dbp 110 mm 100 week21 weight 80 pounds
100 fwp height 75 kg ;
Condition below
If VSTEST having same VSREN and VSORRESU for 4 consecutive visits then highlight those with flag as
"dulipcate record"
Based on above condition and data , create a flag
Please anyone help me .....
... View more
The fact that I even have to post this tells u what kind of day I'm having 😞 If the HLT_ALZDEM variable doesn't have a valid value of 1 or 2 (yes/no), then the variables that are constructed based on HLT_ALZDEM should be given a value of missing. However, they sometimes are given a value of 0 instead.
Below is a portion of the output file, sorted for rows where HLT_ALZDEM is missing. Since missing is clearly not 1 or 2, all ADRD_group and non_ADRD_group values should also be missing. But they're not. Obs 9 and 10 are identical except for these 2 variables., when Obs 9 is missing and Obs 10 is 0 for both.
The only possible explanation I can think of is that there are non-printable characters for some values of HLT_ALZDEM. But even if there are, those non-printable characters are not 1 or 2. Frequencies for HLT_ALZDEM only show values of 1, 2, and missing.
IF cohort_flag = 1 AND ADM_MA_FLAG_YR = 3
THEN MA_group = 1; ELSE MA_group = 0;
IF cohort_flag = 0 THEN MA_group = .;
IF cohort_flag = 1 AND ADM_FFS_FLAG_YR = 3
THEN TM_group = 1; ELSE TM_group = 0;
IF cohort_flag = 0 THEN TM_group = .;
IF cohort_flag = 1 AND HLT_ALZDEM = 1
THEN ADRD_group = 1; ELSE ADRD_group = 0;
IF cohort_flag = 0 THEN ADRD_group = .;
IF HLT_ALZDEM ^in (1,2) THEN ADRD_group = .;
IF cohort_flag = 1 AND HLT_ALZDEM = 2
THEN non_ADRD_group = 1; ELSE non_ADRD_group = 0;
IF cohort_flag = 0 THEN non_ADRD_group = .;
IF HLT_ALZDEM ^in (1,2) THEN non_ADRD_group = .;
Obs
HLT_ALZDEM
cohort_flag
MA_group
TM_group
ADRD_group
non_ADRD_group
1
0
2
1
0
1
0
0
3
1
1
0
4
0
5
0
6
1
0
1
7
1
1
0
8
1
1
0
9
1
1
0
10
1
1
0
0
0
11
1
1
0
12
1
0
1
13
1
0
1
14
1
0
1
15
1
0
1
16
1
0
1
0
0
... View more
I need to define column width to standardize the look of the report. However, when I set the column width it automatically expands the row height for each cell. I tried setting the cellheight in various places in the code but I can't seem to constrain it. How is this done?
Here's an example using the sashelp.cars dataset that gives me the same results.
ODS results off;
ODS listing close;
ODS TAGSETS.EXCELXP
file="C:\test.xml"
STYLE=Printer
OPTIONS (
Sheet_Name = "NEW"
Orientation = 'landscape'
FitToPage = 'no'
Pages_FitWidth = '1'
Pages_FitHeight = '100'
embedded_titles = 'yes'
);
PROC REPORT DATA=sashelp.cars
style(header)=[fontfamily=helvetica fontsize=8pt textalign=l]
style(column)=[fontfamily=helvetica fontsize=8pt textalign=l TAGATTR='format:text'];
columns make model type origin msrp drivetrain horsepower mpg;
DEFINE make / STYLE(column)={width=2cm};
DEFINE model / STYLE(column)={width=2cm};
DEFINE type / STYLE(column)={width=2cm};
DEFINE origin / STYLE(column)={width=2cm};
DEFINE msrp / STYLE(column)={width=15cm};
DEFINE drivetrain / STYLE(column)={width=2cm};
DEFINE horsepower / STYLE(column)={width=2.5cm};
DEFINE mpg / STYLE(column)={width=2cm};
RUN;
ods tagsets.excelxp close;
... View more