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, I have a fiction dataset that I've created like this data countries;
length country $20 country_1 $50;
input country $ country_1 $;
datalines;
AE United Arab Emirates
AR Argentina
AU Australia
Africa Africa
Asia Asia
CA Canada
CH Schweiz
CN China
DE Germany
ET Etiopia
EU Europe
FR France
GB United Kingdom
GM Gambia
KR Republic of Korea
KZ Kazakstan
Latin_America_31 Latin America-31
Middle_East Middle East
NO Norway
NP Nepal
NZ New Zealand
Non_EU_Europe Non-EU Europe
PE Peru
PH Philippines
RU Russia
TR Turkey
UA Ukraine
World World
;
run;
data countries_extended;
set countries;
do i = 1 to ceil(ranuni(0) * 100); /* Upprepa varje land slumpmässigt upp till 100 gånger */
output;
end;
drop i;
run; and I have SAS 9.4 - which mean it can't take union all, and such. How do I create a variable a variable hierarchy, beacuse now it is mixed in col=country. I've read that you can self join in this case, but the output was bad.. Which I tried to do, but it didn't went well. Can someone please help me out?
... 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
I am working on a Multivariate multilevel model where The 3 outcome variables RCBPre_Rating RCTPre_Rating RCSPre_Rating are continuous but changes in all three need to be considered simultanously as participants can indicate more or less of each RC type and this combination explains what they believe the complex root causes of an Ableism scenario are (RC1 is bias, RC2 is a lack of training, and RC3 is systemic barriers). The data set is double stacked where items (vignettes) are nested within Participants. There are item-level variables of ableism type (AT, 4 categories) (OB, 4 levels), and 2 item-level covariates of Trial oder. There are participant-level variables of teacher type (TT, 3 categories), and years of experience (YOE), and the participant-level covariates such as other demographic variables ( INLevel INDisSchool INLOE PerDis FamDis EthnicitySel Race Gender Ableismselfassess). I have tried two different ways to get the model to run, one using mutilple model lines in the proc mixed statement, and one using one model line with all three outcome variables, neither way is working! please help me! Syntax for each way is shown below. First way: proc mixed data=RQ2 method=ml; class ResponseID Vignette AT OB Teachertype INLevel INdisschool INLOE PerDis FamDis EthnicitySel Race Gender ADCTO Vtrialorder; /* Model statement for RC1 */ model RCBPre_Rating = OB|AT Teachertype|YOE Teachertype|YOE INLevel INDisSchool INLOE PerDis FamDis EthnicitySel Race Gender Ableismselfassess ADCTO Vtrialorder / solution ddfm=bw; /* Model statement for RC2 */ model RCTPre_Rating = OB|AT Teachertype|YOE Teachertype|YOE INLevel INDisSchool INLOE PerDis FamDis EthnicitySel Race Gender Ableismselfassess ADCTO Vtrialorder / solution ddfm=bw; /* Model statement for RC3 */ model RCSPre_Rating = OB|AT Teachertype|YOE Teachertype|YOE INLevel INDisSchool INLOE PerDis FamDis EthnicitySel Race Gender Ableismselfassess ADCTO Vtrialorder / solution ddfm=bw; /* Random effects */ random intercept / subject=ResponseID type=un; /* Random intercept for participants */ random intercept / subject=vignette type=un; /* Random intercept for vignettes */ /* Random slopes for participant-level effects */ random ADCTO / subject=vignette; Vtrialorder / subject=vignette; random OB / subject=vignette; random AT / subject=vignette; /* Allow covariance structure to account for multivariate response */ repeated / subject=ResponseID*vignette type=un group=ResponseID; run; Second way: proc mixed data=RQ2 covtest NOCLPRINT Method=REML; class ResponseID Vignette AT OB Teachertype INLevel INdisschool INLOE PerDis FamDis EthnicitySel Race Gender ADCTO Vtrialorder; /* Model statement for RC1 */ model RCBPre_Rating RCTPre_Rating RCSPre_Rating = OB|AT Teachertype|YOE Teachertype|YOE INLevel INDisSchool INLOE PerDis FamDis EthnicitySel Race Gender Ableismselfassess ADCTO Vtrialorder / solution DDFM=Satterthwaite; /* Random effects */ random intercept / subject=ResponseID type=un; /* Random intercept for participants */ random intercept / subject=Vignette type=un; /* Random intercept for vignettes */ /* Random slopes for participant-level effects */ random ADCTO / subject=ResponseID; random Vtrialorder / subject=ResponseID; random OB / subject=Vignette; random AT / subject=Vignette; /* Allow covariance structure to account for multivariate response */ repeated / subject=ResponseID*vignette type=un group=ResponseID; parms / ols; run;
... View more