Hi,   I have a macro that imports all Excel files in a given directory. The name of each Excel file contains information about year that I want to later use. However, in different files, the position of the year is different, e.g. datafile2016.xlsx, datafile2016_upd.xlsx etc. I want to create a macro variable that extracts the year from each name of the file. My idea is to use nested substring and index functions: year=substr(&name, x, 4), where x is the position of the "20" substring (first two digits of the year) that I find by using: index(&name, "20") and &name contains whole name of the file.   Since I'm doing this in the macro, I use %sysfunc with each function, which gives me the following code:     %let year=%sysfunc(%substr(&name, %sysfunc(inputn(%sysfunc(index(&name, "20")), 2.)), 4));   However, this produces the following error: ERROR: Function name missing in %SYSFUNC or %QSYSFUNC macro function reference.   What am I doing wrong? ... View more

Hi,   I have a number of Excel files, named Company1_2016Q1.xlsx, Company1_2016Q2.xlsx, Company2_2016Q1.xlsx, Company2_2016Q2.xlsx etc. They are stored in one folder with multiple subfolders.     I have a following macro to import all Excel files from a catalog to SAS (I've found it somwhere on the Web, I didn't write it on my own). Could you help me modify the macro in two ways: 1. I would like to loop not only through the folder but also through subsoflders and sub-subfolders (if they exist) 2. I have some problem with manipulating the imported datasest - please take a look at the comments in the code below.   %macro read_files (katalog); %let rc=%sysfunc(filename(rawdata,&katalog)); %let did=%sysfunc(dopen(&rawdata)); %let dnum=%sysfunc(dnum(&did)); %do i=1 %to &dnum; %let name_file=%sysfunc(dread(&did,&i)); %let rozszerz=%sysfunc(scan(&name_file,2,.)); %if &rozszerz=xlsx %then %do; %let name_file_short=%sysfunc(scan(&name_file,1,.)); PROC IMPORT OUT=WORK.&name_file_short DATAFILE="&katalog\&name_file" DBMS=xlsx REPLACE; RUN; /* HERE IS THE PROBLEM: - I want to create two variables in the dataset on the basis of the dataset name but the substr doesn't work*/ data WORK.&name_file_short; set WORK.&name_file_short; comp=%substr(&name_file_short,1,8); date=%substr(&name_file_short,10,6); run; %end; %end; %mend; %read_files(C:\Data); ... View more

Dear all,   say that I want to graph data for some European countries on a map. Each country belongs to one of the two groups. I can do something like this: data have; input id $ group value; datalines; DE 1 100 FR 1 200 ES 2 80 PT 2 70 ; run; proc gmap data=have map=mapsgfk.europe all; by group; id id; choro value/ missing range midpoints=(50, 70, 90, 120, 150); run; This produces two separate maps, one for each gropup. Is it possible to plot everything on one map, distingushing groups by colour? ... View more

Hi, I have a dataset with annual sales of selected companies. I want to account for merges&acquisitions that took place and aggregate histrorical (pre-merge) data for both companies involved in a merge.   For example, I have a following dataset with 5 companies A-E. There were two merges: 1. in year 3, company B took over company A (merge No. 1) 2. in year 4, company C took over company D (merge No. 2)   data have; input year company $1. takeover :$1. sales; datalines; 1 A . 100 1 B . 120 1 C . 130 1 D . 140 1 E . 150 2 A . 105 2 B . 125 2 C . 135 2 D . 145 2 E . 155 3 B A 240 3 C . 140 3 D . 150 3 E . 160 4 B . 245 4 C D 300 4 E . 170 ; run; proc sgplot data=have; series x=year y=sales/group=company; run; I want to get rid of the structural breaks in data for companies B and C. To do this, I want to sum sales of both companies invloved in each merge before it took place. In other words, I want to: 1. Sum the sales of A and B in each year 1 and 2 2. Sum the sales of C and D in each year 1,2 and 3   Can you advice me how to do it in SAS?   I was thinking of creating another variable merge_number that would equal to: - 1 for companies A and B in year 1 and 2 (to indicate the companies involved in merge No. 1) - 2 for companies C and D in year 1,2 and 3 (to indicate the companies involved in merge No. 2) - 0 otherwise Then I could use: proc sql; create table want as select merge_number, sum(sales) as new_sales from have group by merge_number; quit; The problem is that I don't know how to create a variable merge_number. Could you help? Or maybe there is some other (simpler) way of summing the sales? ... View more