BookmarkSubscribeRSS Feed
Veeresh
Fluorite | Level 6

 

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.

 

9 REPLIES 9
Kurt_Bremser
Super User

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.

Veeresh
Fluorite | Level 6

Yes, 

sas codes stored in variable as a data in sas dataset.

Kurt_Bremser
Super User

@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.

Veeresh
Fluorite | Level 6

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.

Kurt_Bremser
Super User

@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.

ed_sas_member
Meteorite | Level 14

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;
Veeresh
Fluorite | Level 6

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. 

Tom
Super User Tom
Super User

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;
ballardw
Super User

@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 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.

 


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.

SAS Innovate 2025: Call for Content

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!

Submit your idea!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 9 replies
  • 1572 views
  • 0 likes
  • 5 in conversation