topic Re: SAs in SAS Programming

How to utilize SAS Programming to explicitly output to multiple datasets ?

qazwsx1 — Tue, 18 Feb 2020 12:44:33 GMT

data a;
set sashelp.class nobs=aee;
a=ceil(_n_/(aee/4));
b=put(a,words23.);
run;

using macro do loops I need all the ones in one dataset and all twos in other datasets using the variable a or b.

Re: SAs

Rick_SAS — Tue, 18 Feb 2020 12:50:26 GMT

In your class, have you learned about the OUTPUT statement, which can take a data set as an argument?

Here's a hint:

1. On the DATA statement, list all the data sets that you want to create (for example, A1 and A2).

2. Use an IF-THEN-ELSE statement to check the value of the A variable and use OUTPUT A1 or OUTPUT A2 to send each observation to the data set that it belongs in.

If you get stuck, there is an example in the documentation for the OUTPUT statement.

Good luck!

Re: SAs

qazwsx1 — Tue, 18 Feb 2020 12:08:24 GMT

Hi Rich,

data a;
set sashelp.class nobs=aee;
a=ceil(_n_/(aee/4));
b=put(a,words23.);
run;

proc sort data=a; by b; run;

data a13;
set a ;
call symput("bc"||compress(_n_),b);
run;

%macro a(bc,i);
%do &i= 1 %to 19;
data &&bc&&i.;
set a;
by b;
if _n_= &i. then output &&bc&&i.;
%end;
run;
%mend;
%a(b,i,)

here I'm getting 19 datasets same way I need all the 1,2 3,4 in different datasets

Re: SAs

Rick_SAS — Tue, 18 Feb 2020 12:57:17 GMT

No macro is needed here, as shown in the link I posted. However, if the assignment REQUIRES that you use a macro, then you'll have to figure it out.

The biggest mistake that new SAS programmers make is to jump into macro programming without understanding the basics of DATA step programming. When people struggle to write a macro, I always advise them to FIRST figure out how to write it without using a macro, AFTER the program works, then modify the program so that the repetitive portions of the program are replaced by macro substitution.

Re: How to utilize SAS Programming to explicitly output to multiple datasets ?

Kurt_Bremser — Tue, 18 Feb 2020 13:22:16 GMT

Create code dynamically, depending on the contents of variable b:

data a;
set sashelp.class nobs=aee;
a=ceil(_n_/(aee/4));
b=put(a,words23.);
run;

/* get a list if distinct values in b */
proc sort
  data=a (keep=b)
  out=c
  nodupkey
;
by b;
run;

/* use call execute() to create code */
data _null_;
call execute('data'); /* begin a new data step */
do until (end1); /* populate the data statement with dataset names */
  set c end=end1;
  call execute(' ' !! strip(b));
end;
call execute('; set a;'); /* terminate the data statement and define the source dataset */
do until (end2); /* create a series of if/then output statements  */
  set c end=end2;
  call execute('if b = "' !! strip(b) !! '" then output ' !! strip(b) !! ';');
end;
call execute('run;'); /* terminate the data step */
stop; /* stop here, otherwise we would get a single spurious "data" in the execution queue
  from a partial execution of the next data step iteration */
run;

Re: SAs

Tom — Tue, 18 Feb 2020 20:40:39 GMT

So your first step:

data a;
  set sashelp.class nobs=aee;
  a=ceil(_n_/(aee/4));
  b=put(a,words23.);
run;

Makes two variables with the same information. A is a number an B is a string. Let's see how well it worked.

proc freq data=a;
  tables a*b/list;
run;

Result:

                                       Cumulative    Cumulative
a    b        Frequency     Percent     Frequency      Percent
---------------------------------------------------------------
1    one             4       21.05             4        21.05
2    two             5       26.32             9        47.37
3    three           5       26.32            14        73.68
4    four            5       26.32            19       100.00

Now it sounds like you want to make 4 datasets using the value of B as the name. (Not sure how well that will work when there are more than twenty groups.)

So it looks like you want to generate code like this:

data one two three four;
  set a;
  if a=1 then output one;
  else if a=2 then output two;
  else if a=3 then output three;
  else if a=4 then output four;
run;

Or perhaps like this:

data one two three four;
  set a;
  select (a);
    when (1) output one;
    when (2) output two;
    when (3) output three;
    when (4) output four;
  end;
run;

So here is a simple way. Since dataset A is sorted by variable A (by definition of how you generated it) we could just use it to drive the process.

filename code temp;
data _null_;
  file code;
  put 'data ' @;
  do until (eof1);
    set a end=eof1;
    by a;
    if first.a then put b @;
  end;
  put ';' / 'set a;' / 'select a;' ;
  do until (eof2);
    set a end=eof2;
    by a;
    if first.a then put 'when (' a ') output ' b ';' ;
  end;
  put 'end;' / 'run;' ;
  stop;
run;

Then to run the generated code just use %INCLUDE.

%include code;

Not sure how MACRO comes into this. Perhaps you could create a macro where you pass in the name of the dataset that is being split and the number of datasets you want to create? If so that will just impact the first step.

%macro split(datain,number);
data a;
  set &datain. nobs=aee;
  a=ceil(_n_/(aee/&number.));
  b=put(a,words23.);
run;
....
%mend split;