Hello everyone!
I need help please help me, I want write a code to generate a new .sas file from a sas code and a new file at a particular location.
Thanks in advance
I have Extract excel sheet name in sas and want to continue with generate new .sas file from this code.
/**Assign a libname for excel sheet*/
libname inexcel xlsx '/home/viadmin/casuser/mapping.xlsx';
PROC CONTENTS DATA=inexcel._ALL_ noprint
OUT=Sheets (KEEP=Memname);
RUN;
PROC SORT DATA=Sheets NODUPLICATES;
BY Memname;
RUN;
DATA Sheets;
SET Sheets;
ID=_N_;
RUN;
DATA _NULL_;
IF 0 THEN SET Sheets NOBS=X;
CALL SYMPUT('Recount', X);
STOP;
RUN;
%macro Excel;
%DO i = 1 %TO &Recount;
PROC SQL ;
create table sheet&i. as
SELECT Memname INTO :Name
FROM Sheets WHERE ID = &i;
quit;
%end;
%mend;
%excel;
Till here code is working fine. But after this i want to generate new .sas file for different Names like- sheet1.sas, sheet2.sas
Why do you want to create a program for each sheet? What will happen in those programs?
I want to create different jobs for different sheets.
Why do you need one for different sheets? Is the processing different? Is it called under different conditions?
Please post your code for the treatment of one sheet.
This is my code:-
/**Assign a libname for excel sheet*/
libname inexcel xlsx '/home/viadmin/casuser/mapping.xlsx';
PROC CONTENTS DATA=inexcel._ALL_ noprint
OUT=Sheets (KEEP=Memname);
RUN;
PROC SORT DATA=Sheets NODUPLICATES;
BY Memname;
RUN;
DATA Sheets;
SET Sheets;
ID=_N_;
RUN;
DATA _NULL_;
IF 0 THEN SET Sheets NOBS=X;
CALL SYMPUT('Recount', X);
STOP;
RUN;
%macro Excel;
%DO i = 1 %TO &Recount;
PROC SQL ;
create table sheet&i. as
SELECT Memname INTO :Name
FROM Sheets WHERE ID = &i;
quit;
%end;
%mend;
%excel;
from this i got sheet names- Sheet1,sheet2,
now i want to generate .sas file for this so that It will be job for sheet.
like- Job_sheet1.sas , Job_sheet2.sas
If you want to make a copy of a .sas file, the easiest way is to use your operating systems, COPY command. If you have options XCMD enabled, you could call this command from SAS.
But often having multiple programs (one per sheet) to maintain becomes a difficult mess.
Since you already have one macro in your program, did you consider developing a macro that would process each sheet, rather than creating a separate .sas program for each sheet?
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.