Hello everyone,   I am trying to select multiple variables with the same prefix but a different numbered suffix in an SQL procedure within SAS Base. I should mention that I need this because the number of variables may change over time.   For example I want this output:   proc sql; create table WANT as select VARA1, VARA2, VARA3, VARA4, VARA5, VARB1, VARB2, VARB3, VARB4, VARB5 from HAVE; quit;   I would like my code to be structured like this to select VARA1 to VARA5 and VARB1 to VARB5, is that possible?:   proc sql; create table WANT as select VARA[*], VARB[*] from HAVE; quit; Thank you so much, ... View more

Hello everyone,   I have an SQL procedure in which I want to aggregate data with a group by procedure. I use a macro variable list because the level of aggregation needed might change. However, there is always one variable that I want to exclude from that list at that step and I don't know how to proceed.   What I have right now:   %LET CATEG = %str(VAR1, VAR2, VAR3, EXCL); %LET CATEG2 = %str(VAR1, VAR2, VAR3); proc sql noprint; create table WANT as select &CATEG., sum(Nobs_last) as CAT_Nobs_last, sum(Nobs) as CAT_Nobs from HAVE group by &CATEG2. order by &CATEG.; quit; What I would like to have:   %LET CATEG = %str(VAR1, VAR2, VAR3, EXCL); proc sql noprint; create table WANT as select &CATEG., sum(Nobs_last) as CAT_Nobs_last, sum(Nobs) as CAT_Nobs from HAVE group by &CATEG. [with an function that let me exclude the var EXCL] order by &CATEG.; quit; Thank you very much for your time, Cheers!     ... View more

Hello,   quick question, I want to count the number of observations in a table where the date is before another date. The format of my date column is YYMMDDN8. For example, if I want to count the number of observations that are before 20180430 I would have something like this:     proc sql; select count(*) from my.table where datepart(datecolumn) < 20180430 ; quit; Thanks for your help ... View more

Hello,   I have two big tables, table T0 and table T1 that represents observations for two different months. I want to create three new tables:   -one that has unique observations id for T0; -one that has same observations id for T0 and T1; -one that has unique observations id for T1.   Unique id is based on two columns: id1 & id2. I would prefer using proc sql for this as well. Is there a way for me to sort this without using left join and without merging id1 and id2 in a new variable?   Thank you very much 🙂 ... View more

Hello,   I have one dataset with 12992 observations. I have (half) 6496 different IDs, so 6496 pairs of observations. What I want to do is to compare date intervals within each pair to make sure they don't overlap eachother for the same ID. The dates variables are DATE9. format and informat.   So basically 3 variables: ID, DATE_BEGIN, DATE_END. I am not sure how to proceed with this. Thank you ... View more

Hello,   I am trying to multiply a matrix and a vector with proc iml, but I never used it,.   Basically, I already have the two datasets that I need, one MATRIX and one VECTOR.   I simply want the resulting vector of the multiplication of MATRIX*VECTOR in a new dataset with one variable named RESULT.       ... View more

Hello, I have a table with about 15M obs and I wanna make sure that one variable doesn't have too many missing values. I would like the code to stop after a treshold of about 5k missing values, how do I do that? The process takes about 5,5 min. right now if it reads all  the observations. Thank you!   proc sql noprint stimer; SELECT count(*) FROM have(keep= variable) WHERE variable is null ; quit; ... View more