Hello,   I am struggling with achieving something like this: VAR1 VAR2 COUNT_BY_GROUP A 10 2 A 20 2 B 243 1 C 4 3 C 21 3 C 4 3   Where the COUNT_BY_GROUP variable is the total number of elements of that group.   Here is the code that I'm currently using: data want; set have; by VAR1; retain COUNT_BY_GROUP; if first.VAR1 then COUNT_BY_GROUP = 0; COUNT_BY_GROUP +1; run; But it gives me a cumulative count. I want to save the total count of elements in a group.   Thank you in advance. ... View more

Hi, I have issues with running a macro in a do loop. I have the following macro which works on its own: %macro import_excel(path,year,month, sheetname); %put &path.\CRT&year.\&month.&year..xlsx; proc import out=CRT_&year._&month. datafile="&path.\CRT&year.\&month.&year..xlsx" dbms = xlsx replace; sheet="&sheetname."; data CRT_&year._&month.; set CRT_&year._&month.; /* If numeric imported as character, then convert to numeric*/ array chars {*} _character_; do _n_ = 1 to dim(chars); chars{_n_} = put(chars{_n_},BEST18.); end; year = "&year."; month = "&month."; run; %mend; Then,   I try to iterate over years and months like this: %import_excel(C:\Users\a0782369\Desktop\testluk, 2018,janvier,RawData); %import_excel(C:\Users\a0782369\Desktop\testluk, 2018,février,RawData); %import_excel(C:\Users\a0782369\Desktop\testluk, 2019,janvier,RawData); %import_excel(C:\Users\a0782369\Desktop\testluk, 2019,février,RawData); It does work without problems. However I am looking for a more elegant solution: data _NULL_; length m $20; do yrs = 2018 to 2019; call symput('i',yrs); do mth = 'janvier', 'février', 'mars', 'avril', 'mai', 'juin', 'juillet', 'août', 'septembre', 'octobre', 'novembre','décembre' ; call symput('m',mth); %import_excel(C:\Users\a0782369\Desktop\testluk, '&i.', '&m.' ,RawData)); end; end; run; However, it does not update the datafile="&path.\CRT&year.\&month.&year..xlsx" at all and nothing works afterwards.   Thank you. ... View more

Hello, I have a dataset with the following structure:   data have; input data_set variable_name$ DATA_A$ DATA_B$ DATA_C$ DATA_D$ ; cards; 1 A num . . . 1 B num . . . 1 C char . . . 1 D num . . . 2 A . num . . 2 B . num . . 2 C . char . . 2 D . num . . 3 A . . num . 3 B . . num . 3 C . . char . 3 D . . num . 4 A . . . num 4 B . . . num 4 C . . . char 4 D . . . num ;;;;; run;  I want to end up with this:   data want; input variable_name$ DATA_A$ DATA_B$ DATA_C$ DATA_D$ ; cards; A num num num num B num num num num C char char char char D num num num num num ;;;; run; Which means that I want to summarize or align my dataset to make it more compact and remove the blank spaces.   Thanks for you help ... View more

%let sysd= %sysfunc(day("&sysdate"d)); %let sysmonth= %sysfunc(month("&sysdate"d)); %let sysyear= %sysfunc(year("&sysdate"d)); %put &sysd &sysmonth &sysyear; *this is what I tried to do; *%let sysd= %sysfunc(inputn(&sysd,4.0)); *%put &sysd; data main4; set main3; startdate=mdy(NMOIPMRI,NJOUPMRI,NANNPMRI); enddate=mdy(&sysmonth,&sysd,&sysyear); vehi_age=datdif(startdate,enddate,'act/act'); run;   Hello, I am trying to count the number of days between today and a given date in the past. I struggle to get the proper variable type to input in the mdy() function with my variables &sysd, &sysmonth, &sysyear. Could you please help me? ... View more

Hello,   I have some issues with the format of my macro variables.    I have a program where I have to determine around 15 macro variables by hand and I am trying to automate the process.  One of such variables would be:   %let ddebdet='01jan2016'd; All of those 15 variables refer to different dates. I am trying to automate the process using the following code:     %let year=2018; %let month=11; data _null_; call symputx('ddebdet', put(intnx('day',mdy(12,31,&year-3),0)+1,date9.)); run; The idea is to adapt only two variables. Year and month, in this case and to generate  01jan2016 automatically. This is the output that I'm getting:   2275 %put &ddebdet; 01JAN2016 2276 2277 %let ddebdet='01jan2016'd; 2278 %put &ddebdet; '01jan2016'd 2279 2280 2281 %let year=2018; 2282 %let month=11; 2283 2284 data _null_; 2285 call symputx('ddebdet', put(intnx('day',mdy(12,31,&year-3),0)+1,date9.)); 2286 run; NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 2287 2288 2289 %put &ddebdet; 01JAN2016   As you can see there is a difference between    2278 %put &ddebdet; '01jan2016'd and     2289 %put &ddebdet; 01JAN2016 It is quite problematic because the rest of my code doesn't work with the auto generated solution. For example this    data det; set data (where=(&ddebdet<=datepart(dtismaj)<=&dfindet and ctyp='350')); run; Returns the following error: NOTE: Line generated by the macro variable "DDEBDET". 1230 01JAN2016 ------- 22 76 ERROR: Syntax error while parsing WHERE clause. ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, GE, GT, LE, LT, NE, OR, ^=, |, ||, ~=. ERROR 76-322: Syntax error, statement will be ignored. I don't really understand why I am having this issue. Thanks in advance. ... View more

Hello I am struggling with the function substr() and symputx().   * I am trying to generate this variable automatically; %let suiviassinc=dsi_asti.suiviassincnew1807; *this is my code; %let annee=2018; %let mois=11; data _null_; call symputx('a',substr("&annee",3,4)); call symputx('suiviassinc',"dsi_asti.suiviassincnew&a&mois"); run;   Upon running the code I am getting the following error: NOTE: Invalid third argument to function SUBSTR at line 397 column 22.   Can you please explain the issue here?       Thanks for your help ... View more

Hello, I want to develop a simple code to close all viewtables before running the rest of the program. Here is what I managed to find so far: dm 'next VIEWTABLE:; end;';   which only closes one table. Therefore I am looking for a way to run this code many times to close all viewtables. Here is what I have attempted so far: 1:Using a do loop in a data step (works only once then returns an error because the "end"s disturb the do block: data _null_; do i=1 to 20; dm 'next VIEWTABLE:; end;'; end; run; 2:Using a macro statement (doesn't work) in order to have one "end" and another "%end" without confusion for the do block:     %macro closevts; %do i=1 %to 20; dm 'next VIEWTABLE:; end;'; %end; %mend;   3: putting a do loop in a datastep inside the macro statement (doesn't do anything):   %macro closevts; %do i=1 %to 20; data _null_; dm 'next VIEWTABLE:; end;'; run; %end; %mend;   Anyone can help me with this please? Best, ... View more