topic How to store multiple observations data into single observation one below one. in New SAS User

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

Veeresh — Tue, 04 Feb 2020 11:51:14 GMT

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.

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

Kurt_Bremser — Tue, 04 Feb 2020 12:11:25 GMT

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.

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

ed_sas_member — Tue, 04 Feb 2020 13:10:57 GMT

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;

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

Veeresh — Tue, 04 Feb 2020 13:11:28 GMT

Yes,

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

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

Kurt_Bremser — Tue, 04 Feb 2020 13:29:50 GMT

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

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

Veeresh — Tue, 04 Feb 2020 13:50:13 GMT

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.

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

Veeresh — Tue, 04 Feb 2020 13:56:49 GMT

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.

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

Tom — Tue, 04 Feb 2020 14:27:13 GMT

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;

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

Kurt_Bremser — Tue, 04 Feb 2020 14:31:33 GMT

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

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

ballardw — Tue, 04 Feb 2020 15:44:05 GMT

@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-data-AKA-generate/ta-p/258712 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.