create datasets with name of each make

[Anandkvn]

proc sql;
select  count(make) as total 
into:n
from sashelp.cars;
select make
into:make1-:make99%left(&n)
from sashelp.cars;
quit;


%macro cars;
%do i=1 %to &n;
data &&make&i;
set sashelp.cars;
if make="&&make&i";
run;
%end;
%mend;
%cars;

error below

[Tom]

You cannot use a string like Mercedes-Benz as the name of a dataset.

Come up with another way to name your datasets. 

Perhaps just number them and put the value of MAKE into the LABEL of the dataset.

proc freq data=sashelp.cars;
 tables make / out=makes noprint;
run;

filename code temp;
data _null_;
  set makes ;
  make_num +1;
  file code ;
  put 'data make' make_num '(label=' make :$quote. ');'
    / '  set sashelp.cars;'
    / '  where ' make = $quote. ';'
    / 'run;'
  ;
run;

%include code / source2 ;

379 +data make23 (label="Mazda" );
380 +  set sashelp.cars;
381 +  where Make="Mazda" ;
382 +run;

NOTE: There were 11 observations read from the data set SASHELP.CARS.
      WHERE Make='Mazda';
NOTE: The data set WORK.MAKE23 has 11 observations and 15 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


383 +data make24 (label="Mercedes-Benz" );
384 +  set sashelp.cars;
385 +  where Make="Mercedes-Benz" ;
386 +run;

NOTE: There were 26 observations read from the data set SASHELP.CARS.
      WHERE Make='Mercedes-Benz';
NOTE: The data set WORK.MAKE24 has 26 observations and 15 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

[Anandkvn]

Hi  Tom,

i want output same like below code using cars dataset 

proc sql;
select count(name) as total_obs
into : n
from sashelp.class;
/* select name */
/* into: name1 -:name%left(&n) */
/* from sashelp.class; */
quit;

%macro make_ds;
%do i=1 %to &n ;
data &&name&i ;
set sashelp.class;
if name="&&name&i";
run;
%end;
%mend make_ds;
%make_ds;

[Kurt_Bremser]

 Read @Tom 's post again.

You cannot (you know what "cannot" means?) use a string like "Merceces-Benz" as a dataset name. You must come up with a different naming scheme.

[Patrick]

As others already stated you can't use a dash in a SAS table name even if you treat it as a literal. That's documented here.

Not that it is advisable in real life situations to split a SAS table into many SAS tables by some category but assuming this is just a learning exercise:
You could just remove any character that's not suitable for use as a SAS table name. Code like below should do.

proc sql noprint;
  select count(distinct make) 
    into :n trimmed
  from sashelp.cars;
  select distinct compress(make,,'kn') 
    into:make1-:make&n
  from sashelp.cars;
quit;

%macro cars();
  %do i=1 %to &n;
    data &&make&i;
      set sashelp.cars;
      if make="&&make&i";
    run;
  %end;
%mend;

%cars();

[Anandkvn]

Hi Patrick ,

Thanks for your solution but Merdesbenz dataset no observations 

[ballardw · Accepted solution]

Since the data set name variable is changed you now need to add another variable that holds the actual value of the variable to do the selection.

proc sql noprint;
  select count(distinct make) 
    into :n trimmed
  from sashelp.cars;
  select distinct compress(make,,'kn') 
    into:make1-:make&n
  from sashelp.cars;
  select distinct make 
    into:makevalue1-:makevalue&n
  from sashelp.cars;
quit;

%macro cars();
  %do i=1 %to &n;
    data &&make&i;/*< data set name value*/
      set sashelp.cars;
      if make="&&makevalue&i"; /*<variable value*/
    run;
  %end;
%mend;

[Tom]

Or perhaps better is use the same rule in the WHERE clause that subsets the data as that avoids dataset name collisions.

So all of the records for makes like 'X-Y' and 'X/Y' will end up in the same dataset.

data &&make&i;
  set sashelp.cars;
  where compress(make,,'kn')="&&make&i"; 
run;

You might also need to use UPCASE() function in both places to avoid name collisions causes by name like 'FORD' and 'Ford'. 

[Patrick]

---

@Anandkvn wrote:

Hi Patrick ,

Thanks for your solution but Merdesbenz dataset no observations 

---

True. Just add the compress() function also to the if condition. Below will work.

proc sql noprint;
  select count(distinct make) 
    into :n trimmed
  from sashelp.cars;
  select distinct compress(make,,'kn') 
    into:make1-:make&n
  from sashelp.cars;
quit;

%macro cars();
  %do i=1 %to &n;
    data &&make&i;
      set sashelp.cars;
      if compress(make,,'kn')="&&make&i";
    run;
  %end;
%mend;

%cars();

Using a hash table would be an alternative and more efficient coding option as it reads the source data only once.

data _null_;
  if _n_=1 then 
    do;
      dcl hash h1(multidata:'y', dataset:'sashelp.cars(obs=0)');
      h1.defineKey('make');
      h1.defineData(all:'y');
      h1.defineDone();
    end;
  set sashelp.cars;
  by make;
  _rc=h1.add();
  if last.make then
    do;
      _rc=h1.output(dataset:cats('work.',compress(make,,'kn')));
      _rc=h1.clear();
    end;
run;

[Tom]

I do not like that method of generating zillions of macro variables instead of keeping the data in a dataset ...

But if you are going to do it then do it right.  

• There is no need to run the query twice, proc sql can count.
• Make a separate set of macro variables to hold the dataset names so you can add rules to insure the dataset names are valid.

%macro split_ds
(indsname  /* Input dataset name */
,splitvar  /* Variable to split on */
);
proc sql noprint;
select distinct
    quote(trim(&splitvar),"'") 
  , compress(&splitvar,,'kn')
  into :name1-
     , :dsn1-
  from &indsname
;
%let n=&sqlobs;
quit;
%do i=1 %to &n;
data &&dsn&i;
  set &indsname;
  if &splitvar=&&name&i;
run;
%end;
%mend split_ds;

options mprint ;
%split_ds(sashelp.cars,make)

[yabwon]

One more solution for lazy programmers who want SAS to rename their datasets names into correct form:

%let ds=sashelp.cars;
%let var=Make;

data names_list;
  set &ds.(keep=&var.);
run;

proc sort data=names_list nodupkey;
  by &var.;
run;

proc transpose data=names_list out=tmp;
  var &var.;
  Id &var.;
run;

proc transpose data=tmp(drop=_name_) out=tmp(rename=(col1=&var.));
  var _all_;
run;

proc print data=tmp;
run;

data _null_;
  set tmp;
  call execute(cat('data work.',_name_,';'));
  call execute("set &ds.;");
  call execute(cat('where Make ="',&var.,'";'));
  call execute('run;');
run;

Bart