SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Excel splitter

Reply
N/A
Posts: 0

Excel splitter

Hi,

I have an Excel file which has data for different dates. I need to import that excel and create seperate excel files for each date.

I want to get all the dates in that file and then check for each date in the excel and export it as a seperate excel.

Any help on this is much appreciated.
Super Contributor
Super Contributor
Posts: 3,174

Re: Excel splitter

Your opportunity here will depend on your SAS experience level - to start.

Investigate using PROC IMPORT to read your Excel file and create a SAS data library member - here is a Google advanced search argument against the SAS.COM site for DOC and supplemental technical/conference reference material on the topic:

import excel file site:sas.com


Then after you have a SAS member, you will need to compose a SAS program to read your SAS member iteratively for each "date" using a WHERE statement and generate a separate SAS file for each date and export the selected data to a new (and uniquely named) Excel file -- here is a Google advanced search argument against the SAS.COM site for DOC and supplemental technical/conference reference material on the topic:

generate sas code site:sas.com

Within these DOC/website technical referencs below, you will see various options to choose, from PROC SQL using " INTO " and also using CALL EXECUTE to generate your SAS code for subsequent execution, either as a macro call/execution or as DATA and/or PROC step execution code.

Suggest you start with the DOC references below, develop a working program that reads and writes a single file, and then come back to the forum for input/feedback about how you can take that program and turn it into something that will be executed multiple times based on your input data "date" values.

Scott Barry
SBBWorks, Inc.

SAS Macro Language: Reference, Introduction to the Macro Facility
http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/a002293969.htm


SAS Macro Programming for Beginners
Susan J. Slaughter, Avocet Solutions, Davis, CA
Lora D. Delwiche, Delwiche Consulting, Winters, CA
http://www2.sas.com/proceedings/sugi29/243-29.pdf


Intermediate and Advanced SAS Macros
Steven First, Katie Ronk, Systems Seminar Consultants, Madison, WI
http://www2.sas.com/proceedings/sugi31/107-31.pdf
N/A
Posts: 0

Re: Excel splitter

Creating a Sample Dataset as below:

data kamal;
input col1 col2;
datalines;
1 1
2 1
3 1
4 1
5 1
1 2
2 2
3 2
4 2
5 2
1 3
2 3
3 3
4 3
5 3
;
run;

Take the count of dictinct values of col2:

Proc sql;
select count(distinct col2)into: count from kamal;
quit;

Create a seperate dataset with only the distinct values:

Proc sql;
create table kamal1 as
select distinct col2 from kamal;
quit;



%macro x(first,second);

%If &first. = 0 %then %do;
data kamal2;
set kamal1(obs=1);
call symput('col2',col2);
run;
%end;
%else %do;
data kamal2;
set kamal1(firstobs = &first. obs=&obs.);
call symput('col2',col2);
run;
%end;

data kamal3_&second.;
set kamal;
where col2 = &col2.;
run;

%mend;

I'm getting error in this part:

data _null_;
do i = 1 to &count.;
%x( i-1,i.);
end;
run;
Super Contributor
Super Contributor
Posts: 3,174

Re: Excel splitter

It is not possible to wrap a DATA step around a macro execution request, nor is it possible to run a combination of SAS DATA step logic and macro logic as you have demonstrated.

You are on a reasonable track to generate a table with unique col2 values. That should be sufficient to use as input to a DATA step that invokes CALL EXECUTE passing a macro invocation command. And the macro invocation really only needs to pass the col2 variable value so you can reference that value in a WHERE statement to subset your primary input file (your sample file WORK.kamal).

From what logic you have demonstrated, I still don't see a working SAS program without involving any macro logic.

Scott Barry
SBBWorks, Inc.
SAS Super FREQ
Posts: 8,719

Re: Excel splitter

Hi:
If you want a separate dataset for some other reason, then you will need to pursue some kind of macro approach.

However, if ALL you want to do is split the file based on COL2 values, you can simply do that with ODS and the NEWFILE=BYGROUP option.

NEWFILE= will create multiple ODS HTML, ODS CSV, ODS RTF or ODS PDF files based on the value that you provide to the option. If you have the file, work.kamal, shown above, one way to quickly and simply split the file for Excel is to do this:

[pre]
** create work.kamal, as described above or using PROC IMPORT;

** Sort by "splitting" variable;
proc sort data=kamal;
by col2;
run;

** make a CSV file for every BY GROUP;
ods csv file='c:\temp\bygrp1.csv'
newfile=bygroup;

proc print data=kamal noobs;
by col2;
var col1 col2;
run;

ods csv close;

[/pre]

With this approach, if you have 3 distinct values for COL2, the NEWFILE= option would cause ODS to create 3 files: BYGRP1.CSV, BYGRP2.CSV and BYGRP3.CSV. If NEWFILE= finds a number in the beginning file name, then that number is incremented for every unique by group. The downside of this approach is that you do not have a SAS dataset for each separate by group -- but it seemed that the only purpose of your separate SAS datasets were to make files for Excel.

cynthia
[/pre]
N/A
Posts: 0

Re: Excel splitter

Yup!!

That's the actual purpose. To create seperate Excels for each variable.

Thanks for your advice. :-)
Ask a Question
Discussion stats
  • 5 replies
  • 397 views
  • 0 likes
  • 3 in conversation