BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ervinodsingh
Obsidian | Level 7

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;
1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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-dat...

 

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;

 

View solution in original post

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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).

ervinodsingh
Obsidian | Level 7

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

ervinodsingh
Obsidian | Level 7

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

 

Capture_lat.PNG

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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-dat...

 

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;

 

ervinodsingh
Obsidian | Level 7

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

ervinodsingh
Obsidian | Level 7

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. 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Tom
Super User Tom
Super User

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;

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1247 views
  • 1 like
  • 3 in conversation