topic Re: export tables according to a column in SAS Programming

export tables according to a column

Geo- — Tue, 30 Oct 2018 02:37:00 GMT

Hi, I have a table like:

a	b	c
a101	b1	c1
a102	b2	c2
a103	b3	c3

and will create 3 excel files like:

table1_b2
a	b	c
a102	b2	c2

table_b1
a	b	c
a101	b1	c1

table_b3
a	b	c
a103	b3	c3

how to use macro to create and export tables according to distinct value of column b and create table_&bi,thanks!

Re: export tables according to a column

ChrisNZ — Tue, 30 Oct 2018 22:36:17 GMT

You don't need macros.

Something like this works:

data _null_;
  set TABLE(keep=B);
  by B;
  if first.B then call execute ('ods excel .....sheet_name= ....    '
                             || 'proc print data=TABLE noobs; where B='||quote(B)||';run;'
                             || 'ods ..... ');
run;

Re: export tables according to a column

Geo- — Wed, 31 Oct 2018 06:44:09 GMT

macro is needed..if column B have hundreds of values,this way could not work

Re: export tables according to a column

ChrisNZ — Wed, 31 Oct 2018 06:48:19 GMT

And you know this because? Hundreds should work.

Re: export tables according to a column

Geo- — Wed, 31 Oct 2018 09:35:37 GMT

data _null_;
  set TABLE(keep=company);
  by company;
  if first.company then call execute ('ods excel file="c:\temp.xlsx"  sheet_name="sales report"    '
                             || 'proc print data=TABLE noobs; where company='||quote(company)||';run;'
                             || 'ods excel close ');
run;

Hi expert ,would you please check where is wrong with the code

Re: export tables according to a column

ChrisNZ — Wed, 31 Oct 2018 22:02:38 GMT

1. Missing semicolons

2. You overwrite the same file over and over