SAS out-putting datasets from variable categories

Question

I have a large dataset with multiple variables.

Using two of these variables, et1 and bl1, I would like to create output datasets with the categories of each variable.

Et1 has seven levels which are coded as numeric (although I have a format that is applied to the numerical categories.

Bl1 has four level which are also coded as numeric (once again there is a format to this as well).

I would like to create 28 output datasets (et1 * 7 and bl1 * 4 = 28), ideally using the format names as dataset names.

Cheers, John

Why do you want to create so many datasets? It seems like a really bad idea to me. Keep your data in one dataset and work with it as needed. What are you trying to do here? — Joe
I agree with Joe, you're probably approaching the problem incorrectly. If you insist on creating the 28 data sets, then I'd suggest hard-coding in 28 IF/THEN/ELSE statements to output to the data sets depending on the value of your ET1 and BL1 variables. There are more efficient and dynamic ways to do this with macro processing and/or arrays, but you'd need to provide a better example of what you're attempting before I'd suggest coding it. — RWill
Guys - yes I see your point about keeping the data together, however, disaggregating the data is a requirement of the project. — john
I had considered writing if/then statements but I had decided that would be incredibly arduous and unnecessary. I would prefer to write a macro that does it. I've experimented with including two do loops however haven't been able to get this to work. — john

user1989742 user1989742 · Accepted Answer · 2013-01-18T10:27:32

proc format;
value classa
   1='ADATA'
   2='BDATA'
;
value classb
  1='CDATA'
  2='DDATA'
;
run;

data in;
length class_a class_b $ 8;
do i=1 to 2;
   do j=1 to 2;
      class_a=trim(left(put(i,classa.)));
      class_b=trim(left(put(j,classb.)));
      someVar=ranuni(0);
      someOtherVar=ranuni(0);
      output;
   end;
end;
run;



options mprint symbolgen mlogic;

%macro separate(dsn=, classvar1=, classvar2=);

proc sql;
select distinct trim(left(&classvar1)) || '_' || trim(left(&classvar2))
into :dsnames separated by ' '
from &dsn;
quit;

data &dsnames.;
set &dsn;
%let idx=1;
%let thiscombo=%sysfunc(scan(&dsnames,&idx,%str( )));
%do %while(%length(&thiscombo) > 0);
IF ( &classvar1 = %sysfunc(quote(%sysfunc(scan(&thiscombo,1,%str(_))))) AND
     &classvar2 = %sysfunc(quote(%sysfunc(scan(&thiscombo,2,%str(_))))) )
    THEN OUTPUT &thiscombo;
    %let idx=%eval(&idx+1);
    %let thiscombo=%sysfunc(scan(&dsnames,&idx,%str( )));
%end;
run;

%mend;

%separate(dsn=in, classvar1=class_a, classvar2=class_b);

SAS out-putting datasets from variable categories

1 Answers