program to export excel automatically

[VimalManavalan]

hello all,

i'm new to using sas EG program.

im searching for a solution to export/generate  below data into multiple separate excel file depending on type (Car.xls, bus.xls bike.xls)

separate file.

can anyone suggest best approach it achevie the result.

Input data 

Type	month	cost
Car	01-01-2019	1000
Car	01-02-2019	2000
Car	01-03-2019	2000
Car	01-04-2019	2000
Car	01-05-2019	4000
Car	01-06-2019	2000
Car	01-07-2019	2000
Car	01-08-2019	6000
Car	01-09-2019	2000
Car	01-10-2019	7000
Car	01-11-2019	2000
Car	01-12-2019	2600
bike	01-01-2019	5000
bike	01-02-2019	5000
bike	01-03-2019	5000
bike	01-04-2019	5000
bike	01-05-2019	6333
bike	01-06-2019	5777
bike	01-07-2019	3000
bike	01-08-2019	2354
bike	01-09-2019	4300
bike	01-10-2019	5000
bike	01-11-2019	1222
bike	01-12-2019	9000
bus	01-01-2019	5000
bus	01-02-2019	5000
bus	01-03-2019	5000
bus	01-04-2019	5000
bus	01-05-2019	5000
bus	01-06-2019	5000
bus	01-07-2019	3000
bus	01-08-2019	4000
bus	01-09-2019	8000
bus	01-10-2019	2000
bus	01-11-2019	5000
bus	01-12-2019	5000
truck	01-01-2019	5000
truck	01-02-2019	5000
truck	01-03-2019	5000
truck	01-04-2019	5000
truck	01-05-2019	3000
truck	01-06-2019	2900
truck	01-07-2019	2900
truck	01-08-2019	2900
truck	01-09-2019	3999
truck	01-10-2019	4000
truck	01-11-2019	2000
truck	01-12-2019	4000

Output file sample separate file

car excel file

Type	month	cost
Car	01-01-2019	1000
Car	01-02-2019	2000
Car	01-03-2019	2000
Car	01-04-2019	2000
Car	01-05-2019	4000
Car	01-06-2019	2000
Car	01-07-2019	2000
Car	01-08-2019	6000
Car	01-09-2019	2000
Car	01-10-2019	7000
Car	01-11-2019	2000
Car	01-12-2019	2600

bus excel file

bus	01-01-2019	5000
bus	01-02-2019	5000
bus	01-03-2019	5000
bus	01-04-2019	5000
bus	01-05-2019	5000
bus	01-06-2019	5000
bus	01-07-2019	3000
bus	01-08-2019	4000
bus	01-09-2019	8000
bus	01-10-2019	2000
bus	01-11-2019	5000
bus	01-12-2019	5000

[Haris]

One way to do this is to write separate EXPORT statements: WHERE Type='Car'/'bike'/'bus'/'truck';

Another common SAS processing workflow is to sort the data by Type and use BY TYPE statement. I am not sure if PROC EXPORT takes BY statement or not but SAS documentation should tell you that easily.

[PaigeMiller]

In the case where you have 3 or 4 levels, I would do what @Haris suggested and write 3 or 4 PROC EXPORTs with the proper WHERE= data set option. If the real problem has dozens or hundreds of levels, you could write a macro to do this, where first you use PROC SQL to determine what the levels are, and then loop through each level found and do the export.

[RW9 · Accepted solution]

I respond to your point in;

https://communities.sas.com/t5/SAS-Programming/SAS-Loop-over-dataset-for-create-export-files/m-p/591...

Yes, you can use call execute.  Call execute creates a string, which is inserted into the code after the executing block.  If that string is valid SAS code then it will run, otherwise you will get errors and warnings per any other SAS code.  From your screenshot

it is clear that there are spaces missing in the generated code which are causing the issues, this is due to the cats() function call.  Alter the code to:

proc sort data=have out=list nodupkey;
  by type;
run;

data _null_;
  set have;
  call execute(cats('proc export data=have outfile="c:\',type,'.xlsx"; where type="',type,'"; run;'));
run;

Where have is your input data.  This will create one proc export command for each unique type string.