How to store multiple observations data into single observation one below one.

[Veeresh]

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.

[Kurt_Bremser]

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]

Yes, 

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

[Kurt_Bremser]

---

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

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]

---

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

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]

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]

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]

---

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