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
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;
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;
Of course, change the path to suit your needs..
Thanks Draycut, I'll give it a go!
A
Anytime. Let me know how it goes 🙂
Worked perfectly first time!
Thanks again
Andy
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.