topic Re: How to perform a same operation all sheets of a excel file? in SAS Programming

How to perform a same operation all sheets of a excel file?

ervinodsingh — Fri, 28 Apr 2017 10:14:09 GMT

Hi i have read data from all sheets of an excel file. But i want to perform the same operation on the data of every sheet of excel file and want to save the results in a different dataset name. My code is given below, i have used "ods trace....." and dataset to list datasets available in IN library. I want to read each data from IN library and perform the same operation.

libname in xlsx '/folders/myfolders/q_data _final1.xlsx';
proc copy inlib=in outlib=work;
run;     

ods trace on;
proc datasets library=IN memtype=data;

Re: How to perform a same operation all sheets of a excel file?

RW9 — Fri, 28 Apr 2017 10:32:46 GMT

Something like:

data _null_;
  set sashelp.vtable (where=(libname="WORK"));
  call execute('data work.p_'||strip(memname)||'; set work.'||strip(memname)||'; id=1; run;');
run;

What the above code does is for every dataset in WORK, generate a datastep which looks like:

data work.p_<name of dataset>;

set work.<name of dataste>;

id=1;

run;

Note the p_ in the first row, that is the new dataset created from the old, and id variable is added. You just need to change id=1; to be your logic (of which you have not provided test data in the form of a datastep, what the output should look like, or what your logic is - so can't help there).

Re: How to perform a same operation all sheets of a excel file?

ervinodsingh — Fri, 28 Apr 2017 10:44:47 GMT

Your trick worked for me but how I will introduce the logic. Suppose I want to subtract a var1 from var2 and write that data to a new file. I want to do this for all datasets belonging to different sheets. Thankyou

Re: How to perform a same operation all sheets of a excel file?

Tom — Fri, 28 Apr 2017 10:54:45 GMT

You need to get the list of dataset names to drive generating the code for each.

You could use PROC CONTENTS to get the names.

libname in xlsx '/folders/myfolders/q_data _final1.xlsx';
proc contents data=in._all_ noprint out=contents; 
run;
data dslist ;
  set contents;
  by memname ;
  if first.memname ;
  keep libname memname ;
run;

You could keep the list in that data step or generate a macro variable with a space delimited list of member names or a series of macro variables that each have one name.

There are lots of ways to generate code once you have the list.

You could make a macro that take the member name as input

%macro process1(memname);
   .... &memname ....
%mend process1;

and then generate a series of calls to the macro.

%process1(sheet1);
%process1(sheet2);
....

Or you could code the macro to take a series of names,

%macro process_list(memlist);
%local i memname ;
%do i=1 %to %sysfunc(countw(&memlist));
  %let memname=%scan(&memlist,&i);
   .... &memname ....
%end;
%mend process_list;

or take a libref and generate the series of names for itself.

%macro process_libt(libname);
proc contents data=&libname.._all_ noprint out=contents; run;
...
%mend process_lib;

You could use a data step to generate CALL EXECUTE() statements.

data _null_;
  set dslist;
  call execute(cats('%nrstr(%process1)(',memname,')');
run;

You could use a data step to write SAS code to a file and then %INCLUDE the generated code.

filename code temp;
data _null_;
  set dslist;
  file code;
  put '%process1(' memname ')' ;
run;
%include code /source2;

You could use PROC SQL to select code into a macro variable and then expand the macro variable.

proc sql noprint;
  select distinct cats('%process1(',memname,')')
    into :calls separated by ';'
    from contents
  ;
quit;
&calls;

Re: How to perform a same operation all sheets of a excel file?

RW9 — Fri, 28 Apr 2017 11:23:51 GMT

You may want to refer to my post:

"You just need to change id=1; to be your logic (of which you have not provided test data in the form of a datastep, what the output should look like, or what your logic is - so can't help there)."

In the data _null_; change the id=1 bit to what your logic is. Since you haven't provided an test data, required output, or logic, I can't tell you exactly how that will look.

Re: How to perform a same operation all sheets of a excel file?

ervinodsingh — Fri, 28 Apr 2017 12:34:36 GMT

Please find my data pic and excel sheet, i had read my data as work.jan , work.feb, ..... so on. suppose i want to add new column which has diffrence of column H and G in each excel sheet (12 sheets, please see below ), Further i want write that data intoto new data variable like work.p_jan, work._feb....so on. Thanku

Re: How to perform a same operation all sheets of a excel file?

RW9 — Fri, 28 Apr 2017 12:39:29 GMT

This is not of use to me, I cannot code against a picture, and downloading files from the net is a no-no. Look at other posts here for how to post data, or refer to:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

If you want to have a new column with H-G then update the code I provided to (this assumes the columns read in are actually named h and g) - I have laid out the code so it is clearer for you:

data _null_;
  set sashelp.vtable (where=(libname="WORK"));
  call execute('data work.p_'||strip(memname)||'; 
                  set work.'||strip(memname)||'; 
                  result=h-g; 
                run;');
run;

Re: How to perform a same operation all sheets of a excel file?

ervinodsingh — Tue, 02 May 2017 10:06:18 GMT

Thanks, RW9 your trick worked well for me. I just replaced 'h' and 'g' with the column name of my data.

Re: How to perform a same operation all sheets of a excel file?

ervinodsingh — Thu, 04 May 2017 07:22:33 GMT

Sorry for disturbing you again, I want to know that i have got my results in work.p_jan, work.p_feb...... by the code you provided. Now i want to put "result" column of all these datasets generated into one data. i have tried to modify your code

data _null_;
  set sashelp.vtable (where=(libname="IN"));
  call execute('data work.results;  /* vtable have library names and corresponding memenames */ 
 		  keep $ '||strip(memname)||'
                  set work.p_'||strip(memname)||' ; 
                  '||strip(memname)||'= result
                  
                run;');
run;

I have tried to put results to work.results which is supposed to have 12 columns each for a month from the "work.p_ month" datasets.

Re: How to perform a same operation all sheets of a excel file?

RW9 — Thu, 04 May 2017 08:31:18 GMT

Post a new topic. Start by giving test in the form of a datastep and what you want the output to look like. Your question has now changed. Why are you wanting to create seprate datasets and then combine them back again, thats just a waste of disk space. Forumlate a new topic with exact inputs and outputs to get a good answer.