BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
varshabansal
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
varshabansal
Obsidian | Level 7
data null;
%let path=/home;
%let filename= sheet&i.sas;
%put &=&path.&filename;
file= "&path.&filename.";
put "put line inside the file";
run;

View solution in original post

7 REPLIES 7
andreas_lds
Jade | Level 19

Why do you want to create a program for each sheet? What will happen in those programs?

varshabansal
Obsidian | Level 7

I want to create different jobs for different  sheets.

Reeza
Super User

Why do you need one for different sheets? Is the processing different? Is it called under different conditions?

 

varshabansal
Obsidian | Level 7

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

Quentin
Super User

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?

The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.
varshabansal
Obsidian | Level 7
data null;
%let path=/home;
%let filename= sheet&i.sas;
%put &=&path.&filename;
file= "&path.&filename.";
put "put line inside the file";
run;

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 2028 views
  • 2 likes
  • 5 in conversation