Senario is
We have stored SAS codes into SAS DATASET under a single column and every SAS code is stored under separate observation wrt the obs line.
The requirement is
If %macro or %mend that should be in separate line
Else
remain codes exist within macro loop
that should be comes or store it into a single observation one below one.
Same end result need to export to the excel.
I have attached excel for the better understanding the requirement.
Note: So many macros are exist in single column with other SAS codes like data step process step process SQL steps within macro loop
Don't consider obs column just for reference I have added to understand scenario.
I see no data in your xlsx, only code.
Datasets are for data, NOT for code.
If you want to store codes for later reuse, you store them as text files (usually with extension .sas) and %include them where needed.
Yes,
sas codes stored in variable as a data in sas dataset.
@Veeresh wrote:
Yes,
sas codes stored in variable as a data in sas dataset.
WHAT FOR??
In 20+ years of working with SAS, I've never done that, never had the need for it, never saw it.
Its a requirement from HR analytics and business analytics department.
Exporting SAS end results to the excel sheet as i mentioned.
Further i requested for usage of this output got reply like departmental expertise analysts can utilize these coding examples to showcase business stockholders and analysts.
@Veeresh wrote:
Its a requirement from HR analytics and business analytics department.
Exporting SAS end results to the excel sheet as i mentioned.
Further i requested for usage of this output got reply like departmental expertise analysts can utilize these coding examples to showcase business stockholders and analysts.
Oh, the ones that can't even have a sh*t without storing it in Excel first. And make a PowerPoint presentation out of it.
Hi @Veeresh
Here is an attempt to do this.
However, it is not a good practice to store SAS code in dataset. Don't you have any other possibility?
Best,
data have;
input SAS_CODES $80.;
datalines4;
%Macro student(x=,y=);
data &x;
set student_data;
run;
proc sort data=&x; out=&y;
by age;
run;
%mend student;
%student(x=student_age, y=std_age_sorted)
%macro employee(ID=, Location);
data emp;
set emp_hist;
where Emp_id="&id" and location="&location";
run;
proc sort data=emp out=emp_loc;
run;
%mend employee;
%employee(ID=101_FS, Location=China);
;;;;
data have_temp;
set have;
length SAS_CODES2 $ 1000;
M_start = find(SAS_CODES, %nrstr('%Macro'),'i')>0;
M_end = find(SAS_CODES, %nrstr('%Mend'),'i')>0;
retain M_flag 0;
if M_start=1 then M_flag=1;
if M_end=1 then M_flag=0;
M_flag_F = M_flag;
if M_start=1 then M_flag_F=0;
retain SAS_CODES2;
if M_flag_F=1 then SAS_CODES2 = catx('',SAS_CODES2,SAS_CODES);
SAS_CODESF = SAS_CODES2;
if M_flag_F=0 then SAS_CODESF=SAS_CODES;
drop SAS_CODES SAS_CODES2;
run;
data want;
set have_temp;
by M_flag_F notsorted;
if M_flag_F=0 or (M_flag_F=1 and last.M_flag_F=1) then output;
drop M:;
run;
Thanks for your valuable input !
It is not related to saving SAS codes concepts , It other departmental scenario basis requirement from HR analytics and business analytics department.
Exporting SAS end results to the excel sheet as i mentioned.
Are you just asking to insert extra blank lines before and after macro definitions?
Let's assume your dataset is named HAVE and the variable with the SAS code is named LINE.
Here is a way to write it into an Excel file name example.xlsx.
ods listing close;
ods excel file= 'example.xlsx';
data _null_;
set codes;
file print;
if findw(line,'%macro','i') then put;
len=length(line);
put line $varying200. len;
if findw(line,'%mend','i') then put;
run;
ods excel close;
ods listing;
@Veeresh wrote:
Senario is
We have stored SAS codes into SAS DATASET under a single column and every SAS code is stored under separate observation wrt the obs line.
The requirement is
If %macro or %mend that should be in separate lineElse
remain codes exist within macro loop
that should be comes or store it into a single observation one below one.
Same end result need to export to the excel.
I have attached excel for the better understanding the requirement.
Note: So many macros are exist in single column with other SAS codes like data step process step process SQL steps within macro loop
Don't consider obs column just for reference I have added to understand scenario.
Your XLSX file shows "current output".
Then you should provide 1) an actual example of the dataset and 2) the current code creating the output.
Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.
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 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.