I have multiple entries with the same ID variable. I'm using proc summary to keep the max of each of them. The code example is below. For the variables in the macro variable fvar specified in the var statement, the max is applied, but what happens to the variables in the id statement (macro variable &other. The variables in other are character variables with qualitative data. If there is a missing value and a non missing I would like to keep the non missing, but what happens when both are non missing, which one is kept?     proc summary data=bva1 nway; class ID; var &fvar.; id &other; output out=bva2 max=; ... View more

Hi,    this is my code: %let va=id assets rev; %let la=lid lassets lrev; %let ca=cid cassets crev; proc sort data=dataset; by id year; data dataset; set dataset; array va{*} &va; array la{*}; do j=1 to dim(va); la{j}=lag(va{j}); end; data dataset; set dataset; array la{*} &la; do j=1 to dim(la); if lbvdidc ne bvdidc then la{j}=.; end; data dataset; set dataset; array va{*} &va; array la{*} &va; array ca{*} &va; do j=1 to dim(va); ca{j} = log(va{j}/la{j}); end; the log says: ERROR: All variables in array list must be the same type, i.e., all numeric or character. ERROR: All variables in array list must be the same type, i.e., all numeric or character. ERROR: The array la has been defined with zero elements. ERROR: Too many array subscripts specified for array la. id is a character variable; assets and rev are numeric.   How do I fix this to allow for both?   My objective is to calculate the following: ln(X/Xt-1) for variables assets and rev. I want to do this by id.   ... View more

Hi, I want to calculate the median by YEAR and COUNTRY, for two variables and then use this median in the next step to create a new variable. I'm using sql to do this, but something is off. Below is my code: proc sql noprint; select YEAR, CNTRY, XREV, median(XREV) as medr from dataset; group by YEAR, CNTRY order by YEAR, CNTRY; proc sql noprint; select YEAR, CNTRY, XASSTS, median(XASSTS) as meda from dataset; group by YEAR, CNTRY order by YEAR, CNTRY; data want; set have; outr=ln(XREV/medr); outa=ln(XASSTS/meda); This is part of the log file: NOTE: The query requires remerging summary statistics back with the original data. 1133 ! group by YEAR, CNTRY order by YEAR, CNTRY; _____ 180 ERROR 180-322: Statement is not valid or it is used out of proper order. Can anyone give me some help here?         ... View more

my code (that works):   data home.test2; set bv18u; array values (*) COGS--EBITDA; do i = 1 to dim(values); if nmonths=1 then values(i) = 12/1*values(i); else if Number_of_months=2 then values(i) = 12/2*values(i); else if nmonths=3 then values(i) = 12/3*values(i); else if nmonths=4 then values(i) = 12/4*values(i); else if nmonths=5 then values(i) = 12/5*values(i); else if nmonths=6 then values(i) = 12/6*values(i); else if nmonths=7 then values(i) = 12/7*values(i); else if nmonths=8 then values(i) = 12/8*values(i); else if nmonths=9 then values(i) = 12/9*values(i); else if nmonths=10 then values(i) = 12/10*values(i); else if nmonths=11 then values(i) = 12/11*values(i); else if nmonths=12 then values(i) = 12/12*values(i); else if nmonths=13 then values(i) = 12/13*values(i); else if nmonths=14 then values(i) = 12/14*values(i); else if nmonths=15 then values(i) = 12/15*values(i); else if nmonths=16 then values(i) = 12/16*values(i); else if nmonths=17 then values(i) = 12/17*values(i); else if nmonths=18 then values(i) = 12/18*values(i); else if nmonths=19 then values(i) = 12/19*values(i); else if nmonths=20 then values(i) = 12/20*values(i); else if nmonths=21 then values(i) = 12/21*values(i); else if nmonths=22 then values(i) = 12/22*values(i); else if nmonths=23 then values(i) = 12/23*values(i); else if nmonths=24 then values(i) = 12/24*values(i); end;   to simplify this code I tried doing (that doesn't work): data home.test1; set bv18u; array values (*) COGS--EBITDA; do i = 1 to dim(values); do j=1 to 24; if nmonths=j then 12/j*values(i); end; end; and I got an error. log: 733 data bvd.test1; set bv18u; array values (*) COGS--EBITDA; do i = 1 to dim(values); 734 do j=1 to 24; if nmonths=j then 12/j*values(i); end; end; __ 180 ERROR 180-322: Statement is not valid or it is used out of proper order.   ... View more

This is my code:   proc import datafile='/data/bvd/export.xlsx' out=b1 dbms=xlsx replace; %macro ex(); %do i=2008 %to 2022; proc contents data=b1 out=list(keep=name) noprint; data list1; set list(keep=name); if name in ('Name','Country','CISOC','ID','Status','Legal', 'Entity','Code','Type'); keep name; data list_&i; set list; if substr(name, length(name)-3) = "&i" then output; keep name; data l_&i; set list1 list_&i; proc sql noprint; select name into :l2 separated by ' ' from l_&i; quit; data final_&i(keep=l_&i); set b1; proc sql noprint; select catx("=", name, substr(name, 1, length(name)-5)) into :rename_list separated by " " from sashelp.vcolumn where libname='HOME' and memname="FINAL_&i" and upper(trim(name)) like "%_&i"; quit; proc datasets library=work nodetails nolist; modify final_&i; rename &rename_list; run; quit; data home.final; set final_&i; %end; %mend; %ex(); Log: NOTE: The import data set has 1421 observations and 819 variables. NOTE: WORK.B1 data set was successfully created. NOTE: PROCEDURE IMPORT used (Total process time): real time 7.18 seconds cpu time 7.16 seconds MPRINT(EX): proc contents data=b1 out=list(keep=name) noprint; NOTE: The data set WORK.LIST has 819 observations and 1 variables. NOTE: PROCEDURE CONTENTS used (Total process time): real time 0.02 seconds cpu time 0.03 seconds MPRINT(EX): data list1; MPRINT(EX): set list(keep=name); MPRINT(EX): if name in ('Name','Country','CISOC','ID','Status','Legal','Entity','Code','Type'); MPRINT(EX): keep name; NOTE: There were 819 observations read from the data set WORK.LIST. NOTE: The data set WORK.LIST1 has 9 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds MPRINT(EX): data list_2008; MPRINT(EX): set list; MPRINT(EX): if substr(name, length(name)-3) = "2008" then output; MPRINT(EX): keep name; NOTE: There were 819 observations read from the data set WORK.LIST. NOTE: The data set WORK.LIST_2008 has 54 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds MPRINT(EX): data l_2008; MPRINT(EX): set list1 list_2008; NOTE: There were 9 observations read from the data set WORK.LIST1. NOTE: There were 54 observations read from the data set WORK.LIST_2008. NOTE: The data set WORK.L_2008 has 63 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds MPRINT(EX): proc sql noprint; MPRINT(EX): select name into :l_2008 separated by ' ' from l_2008; MPRINT(EX): quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds MPRINT(EX): data final_2008(keep=l_2008); MPRINT(EX): set b1; WARNING: The variable l_2008 in the DROP, KEEP, or RENAME list has never been referenced. NOTE: There were 1421 observations read from the data set WORK.b1. NOTE: The data set WORK.FINAL_2008 has 1421 observations and 0 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds MPRINT(EX): proc sql noprint; WARNING: Apparent invocation of macro _2008 not resolved. WARNING: Apparent invocation of macro _2008 not resolved. MPRINT(EX): select catx("=", name, substr(name, 1, length(name)-5)) into :rename_list separated by " " from sashelp.vcolumn where libname='HOME' and memname="FINAL_2008" and upper(trim(name)) like "%_2008"; NOTE: No rows were selected. MPRINT(EX): quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.01 seconds MPRINT(EX): proc datasets library=work nodetails nolist; MPRINT(EX): modify final_2008; WARNING: Apparent symbolic reference RENAME_LIST not resolved. NOTE: Line generated by the invoked macro "EX". 799 rename &rename_list; run; _ 22 76 NOTE: Enter RUN; to continue or QUIT; to end the procedure. MPRINT(EX): rename &rename_list run; ERROR 22-322: Expecting a name. ERROR 76-322: Syntax error, statement will be ignored. NOTE: Statements not processed because of errors noted above. MPRINT(EX): quit; NOTE: The SAS System stopped processing this step because of errors. ... View more