topic program to export excel automatically in New SAS User

program to export excel automatically

VimalManavalan — Tue, 24 Sep 2019 01:45:44 GMT

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

Re: program to export excel automatically

Haris — Tue, 24 Sep 2019 18:34:22 GMT

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.

Re: program to export excel automatically

PaigeMiller — Tue, 24 Sep 2019 19:14:21 GMT

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.

Re: program to export excel automatically

RW9 — Mon, 30 Sep 2019 08:07:23 GMT

I respond to your point in;

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

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.