Hey!
I am kinda new to the SAS Base Programming, and I just want to learn it as much as I can. Thus I did some exercises, but I got stucked in a problem. I have a huge table with full of datas about cars. I have to sort them by "make" wich means that I have to categorize the car types. There are 38 types of cars (Azura - 1, Audi - 2 ect). After It I have to export the whole table by a macro. But each type (1, 2, 3, 4 ect) should be on different sheets. And there is the problem. How to export a single table, with one macro to multiple sheets by type?
Here is the code, it might help you understand what I did:
%macro sort; proc sort data = sashelp.cars out= test.cars; by Make; run; %mend; %macro set; data test.cars_sorted (rename=count=uniqid); <-- make them into groups set test.cars; by Make; if first.make then count+1; else count=count; run; %mend; %macro export(x); proc export data = test.cars_sorted outfile = "D:\&x." dbms = XLSX replace; sheet = "car_uniqid"; <-- It should make them into sheets like "car_1". run; %mend export; %sort; %set; %export(cars);
Can you guys help me?
Thank you! 🙂
Okay everyone!
I found the answer. 😛
Here is the code:
%macro sort; proc sort data = sashelp.cars out= test.cars; by Make; run; %mend; %sort; %macro set; data test.cars_sorted (rename=count=uniqid); set test.cars; by Make; if first.make then count+1; else count=count; run; %mend; %set; %macro multisheet (x); proc sql noprint; select distinct uniqid into :carid1 - :carid38 from test.cars_sorted; %let type = &sqlobs; quit; %do i = 1 %to &type; proc export data = test.cars_sorted(where=(uniqid=&&carid&i)) outfile="D:\Munka\&x." dbms= xlsx replace; sheet = "car_&&carid&i"; run; %end; %mend multisheet; %multisheet(cars);
Okay everyone!
I found the answer. 😛
Here is the code:
%macro sort; proc sort data = sashelp.cars out= test.cars; by Make; run; %mend; %sort; %macro set; data test.cars_sorted (rename=count=uniqid); set test.cars; by Make; if first.make then count+1; else count=count; run; %mend; %set; %macro multisheet (x); proc sql noprint; select distinct uniqid into :carid1 - :carid38 from test.cars_sorted; %let type = &sqlobs; quit; %do i = 1 %to &type; proc export data = test.cars_sorted(where=(uniqid=&&carid&i)) outfile="D:\Munka\&x." dbms= xlsx replace; sheet = "car_&&carid&i"; run; %end; %mend multisheet; %multisheet(cars);
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.