I have a data set with multiple variables (columns). I'm trying to get the top 10 values of each variable. However, I would like the output to be in a new dataset with a new variable called: variable_names. The row shows the top 10 values from smallest to largest. The data below is just an example. I have more than 4 input variables. Here, I am just looking at the top 3. For a bigger data set, I may look at the top 10. data example; input a b c d; datalines; 100 120 123 140 12 23 43 42 12 12 23 23 5 7 5 2 80 88 98 2 3 4 101 4 ; %let nTop = 3; proc univariate data = new_data NExtrObs=&nTop; ods select ExtremeObs; run; The proc univariate gives a separate output for each. The UNIVARIATE Procedure Variable: a Extreme Observations Lowest Highest Value Obs Value Obs 3 6 12 3 5 4 80 5 12 3 100 1 I would like to create a new data set where output with column names: Variables and the top values. For example, Variables Top1 Top2 Top3 a 12 80 100 b 23 88 120 c 5 6 1
... View more
DATA ALT;
SET RCA.CLOSING_STATUS_TEST;
ARRAY COLS[*] _CHARACTER_;
ARRAY COLS_[*] _CHARACTER_;
DO I = 1 TO DIM(COLS);
IF MOD(I, 2) = 1 THEN
COLS_[(I+1)/2] = COLS[I];
END;
DROP I;
RUN;
Hi, I have been trying to select the alternate columns. If I am using this code then my third column's data shifts to second column and second's values are disappearing. I want to select alternate columns from my data set and my column names are in following: LOAN_NO, PRODUCT, FINAL_STATUS_05MAY24,DUE_N_TAG_05MAY24, FINAL_STATUS_06MAY24,DUE_N_TAG_06MAY24, FINAL_STATUS_07MAY24,DUE_N_TAG_07MAY24,... and so on. I want to create a table selecting columns:-
LOAN_NO, FINAL_STATUS_05MAY24, FINAL_STATUS_06MAY24, FINAL_STATUS_07MAY24... and so on. If there is any alternate approach for making the selection, please advise me or please help with the given array code.
... 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
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