CODEWITHSUNDEEP
sqlsassas-macro
Pecanbutter
Pecanbutter

Reputation: 11

SAS MACRO Looping

I am working on a dataset that has the following structure:

Color    Apple  Orange  Grape  Avocado  Blueberry
Yellow   1      .       .      .        .
Orange   .      1       .      .        . 
Purple   .      .       1      .        1

I want to write a macro that creates table for each fruit type, selecting all the colors (rows) that have a value of 1. For example, the table for apple TBL_APPLE will have 4 rows like the following

Currently I am thinking looping through both the rows and columns. As a frist step, I turned all the row and column variables into macros:

    /*rows*/
    proc sql noprint; 
    select count(*) into :Nobs
    from work.fruit;
    select Color into :Attr1-:Attr%left(&Nobs)
    from work.fruit;quit;

    /*columns*/
    proc contents data=work.fruit out=contents noprint; run;
    %let n=&sqlobs;
    proc sql; select name into :fruit1-fruit%left(&n) from contents; quit;

    %macro fruit;
    %do i=1 %to &NObs;
    %do j=1 %to &n;
    proc sql;
    create table tlb_&&fruit&j as
    select *
    from work.fruit
    where &n = &n;
    quit;
    %end;
    %end;
    %mend fruit;
    %fruit;

Upvotes: 1

Views: 110

Answers (3)

Shenglin Chen
Shenglin Chen

Reputation: 4554

%color_fruit;
proc sql;
      select name into:fruit_name separated by ' ' from dictionary.columns
      where libname='WORK' and memname='FRUIT' and upcase(name)^='COLOR';
quit;
%let nums_fruit=%sysfunc(countw(&fruit_name));
%do i=1 %to &nums_fruit;
    %let fruit=%scan(&fruit_name,&i,%str( ));
     data tab_&fruit;
          set fruit(keep=color &fruit);
          if &fruit=1 then output;
       run;
 %end;
 run;
 %mend;

Upvotes: 0

yukclam9
yukclam9

Reputation: 336

Not sure if it is something you want, but by my understanding the problem could be simplify as below:

%macro fruit( type);    
   data &type ;
   set dataset;
   where &type = 1;
   run;
%mend fruit;

Upvotes: 1

DWal
DWal

Reputation: 2762

I would first define a macro that performs the simple task you want to repeat:

%macro fruitds(fruit);
data &fruit.(keep=color);
  set fruit;
  where &fruit eq 1;
run;
%mend fruitds;

Then use a data step reading the column names from sashelp.vcolumns and call execute the macro for each column name that is not Color

data _null;
  set sashelp.vcolumn;
  call execute(cats('%fruitds(',name,')'));
  where libname eq 'WORK'
    and memname eq 'FRUIT'
    and name ne 'Color';
run;

Upvotes: 0

Related Questions