topic subsetting each row into new dataset using macros? in SAS Programming

subsetting each row into new dataset using macros?

praveenkotte — Mon, 17 Oct 2016 11:23:25 GMT

I want to create new data set for each obseravtion from sashelp.class using dynamic macros code?

hot coding:

data ds ds1 ds2 ds3----- ds19;

set sashelp.class;

if _n_=1 then output ds;

else if _N_=2 then output ds1;

and so on

else output ds19;

run ;

i want to perform above task to be throught macros

Re: subsetting each row into new dataset using macros?

Kurt_Bremser — Mon, 17 Oct 2016 12:33:57 GMT

Short answer: don't do that. Instead use by group processing, where conditions and subsetting if's.

What is your rationale for trying this?

Re: subsetting each row into new dataset using macros?

Astounding — Mon, 17 Oct 2016 13:00:27 GMT

As Kurt mentioned, it is unlikely that there is a good technical reason to do this. However, if this is just an exercise to get practice in macro programming, it could be useful.

Remember that macro language is only going to generate the same SAS language statements that you already have coded. So inside of a macro you could generate the DATA statement like this:

%local i;

data ds %do i=1 %to 19;

ds&i

%end;

;

It's the same DATA statement, but you don't have to type it out. You can let macro language generate the statement for you.

Similarly, you can use a macro loop to generate the OUTPUT statements. Since you've now seen one loop for the DATA statement, I'll leave the OUTPUT statements to you.

Re: subsetting each row into new dataset using macros?

user24feb — Mon, 17 Oct 2016 13:08:13 GMT

I think that every macro solution is more complicated than a data step. Try:


Data _NULL_;
  Set Sashelp.Class;
  i+1;
  Call Execute("Data xxx"!!Strip(Put(i,Best.))!!";");
  Call Execute("  Set Sashelp.Class (Firstobs="!!Strip(Put(i,Best.))!!" Obs="!!Strip(Put(i,Best.)) !!");");
  Call Execute("Run;");
Run;

Re: subsetting each row into new dataset using macros?

Kurt_Bremser — Mon, 17 Oct 2016 13:18:47 GMT

The advantage of @user24feb's solution is that it does scale well.

Trying to do such an operation in a single data step will sooner or later crack the limit for a single SAS statement (32767 characters) when enough observations in the input will cause a too large data statement.

Re: subsetting each row into new dataset using macros?

praveenkotte — Mon, 17 Oct 2016 13:34:40 GMT

it is one of the interview question that i was asked in one company.

Re: subsetting each row into new dataset using macros?

Reeza — Mon, 17 Oct 2016 13:36:29 GMT

Sometimes interviewers want to hear -I would never do that 🙂

Re: subsetting each row into new dataset using macros?

user24feb — Mon, 17 Oct 2016 13:47:39 GMT

I think it is actually a proc-sql-select-into-question then:

Proc SQL NoPrint;
  Select Max(NObs) Into :NObs Trimmed From (Select Count(*) As NObs From Sashelp.Class);
Quit;

%Macro OutputSingleLine;
%Do i=1 %To &NObs.;
Data DS&i.;
  Set Sashelp.Class (Firstobs=&i. Obs=&i.);
Run;
%End;
%Mend;
%OutputSingleLine;

Re: subsetting each row into new dataset using macros?

Ksharp — Tue, 18 Oct 2016 05:50:40 GMT

Or No need a MACRO .

data _null_;
 if _n_=1 then do;
  if 0 then set sashelp.class(obs=0);
  declare hash h(dataset:'sashelp.class(obs=0)');
  h.definekey(all:'y');
  h.definedata(all:'y');
  h.definedone(); 
 end;
set sashelp.class;
h.add();
h.output(dataset:cats('ds',_n_));
h.clear();
run;

Re: subsetting each row into new dataset using macros?

Kurt_Bremser — Tue, 18 Oct 2016 06:22:57 GMT

@Reeza wrote:

Sometimes interviewers want to hear -I would never do that 🙂

That put a smile on my face. Been there, done that, from both sides (interviewer and interviewee).