topic Re: Defining tab names in an export to excel in SAS Programming

Defining tab names in an export to excel

AJChamberlain — Tue, 11 Dec 2018 13:50:10 GMT

Greetings all

I have a simple SAS dataset with reference numbers for each observation, a variable for Zone (Zone1 or Zone 2) and then further data, something like this:

Reference number	Zone	Var 1	Var 2	Var 3 etc
102965952	Zone2	Data	Data	Data
102987292	Zone1	Data	Data	Data
103008632	Zone1	Data	Data	Data
103029972	Zone2	Data	Data	Data
103032102	Zone2	Data	Data	Data
103033962	Zone1	Data	Data	Data
103035902	Zone2	Data	Data	Data
103037842	Zone2	Data	Data	Data
103039782	Zone2	Data	Data	Data
103041722	Zone1	Data	Data	Data
103043662	Zone2	Data	Data	Data
103045602	Zone1	Data	Data	Data
103047542	Zone2	Data	Data	Data

I would like to write a piece of code to divide this data into two datasets, split by zone and then export each dataset into the tab of a single spreadsheet with each tab having the name 'Zone1' or 'Zone2', drawing the name from the Zone variable, example excel sheet attached.

Any help gratefully received!

Thanks

Andy

Re: Defining tab names in an export to excel

PeterClemmensen — Tue, 11 Dec 2018 14:08:52 GMT

One way

data have;
input RefNum Zone $ (Var1-Var3)($);
datalines;
102965952 Zone2 Data Data Data
102987292 Zone1 Data Data Data
103008632 Zone1 Data Data Data
103029972 Zone2 Data Data Data
103032102 Zone2 Data Data Data
103033962 Zone1 Data Data Data
103035902 Zone2 Data Data Data
103037842 Zone2 Data Data Data
103039782 Zone2 Data Data Data
103041722 Zone1 Data Data Data
103043662 Zone2 Data Data Data
103045602 Zone1 Data Data Data
103047542 Zone2 Data Data Data
;

proc sort data=have(keep=Zone) out=temp nodupkey; 
   by Zone;
run;

data callstack;
 length string $500;
 set temp;
 string=compbl(cats("
            proc export data=have(where=(Zone='", Zone, "'))
            			outfile= 'c:\Users\Peter\Desktop\MyExcelFile.xlsx'
            			dbms=xlsx replace;
            			sheet='", Zone, "';
            run;
           "));
 call execute(string);
run;

Re: Defining tab names in an export to excel

PeterClemmensen — Tue, 11 Dec 2018 14:09:27 GMT

Of course, change the path to suit your needs..

Re: Defining tab names in an export to excel

AJChamberlain — Tue, 11 Dec 2018 14:12:31 GMT

Thanks Draycut, I'll give it a go!

Re: Defining tab names in an export to excel

PeterClemmensen — Tue, 11 Dec 2018 14:22:56 GMT

Anytime. Let me know how it goes 🙂

Re: Defining tab names in an export to excel

AJChamberlain — Tue, 11 Dec 2018 14:33:32 GMT

Worked perfectly first time!

Thanks again

Andy