topic Re: create datasets with name of each make in SAS Programming

create datasets with name of each make

BrahmanandaRao — Tue, 02 May 2023 03:47:01 GMT

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

Re: create datasets with name of each make

Tom — Tue, 02 May 2023 04:57:31 GMT

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

Re: create datasets with name of each make

BrahmanandaRao — Tue, 02 May 2023 05:18:45 GMT

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;

Re: create datasets with name of each make

Kurt_Bremser — Tue, 02 May 2023 05:28:17 GMT

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.

Re: create datasets with name of each make

Patrick — Tue, 02 May 2023 05:42:06 GMT

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();

Re: create datasets with name of each make

BrahmanandaRao — Tue, 02 May 2023 07:46:03 GMT

Hi Patrick ,

Thanks for your solution but Merdesbenz dataset no observations

Re: create datasets with name of each make

ballardw — Tue, 02 May 2023 08:33:01 GMT

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;

Re: create datasets with name of each make

Tom — Tue, 02 May 2023 14:05:30 GMT

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)

Re: create datasets with name of each make

Tom — Tue, 02 May 2023 14:11:21 GMT

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'.

Re: create datasets with name of each make

yabwon — Tue, 02 May 2023 14:37:43 GMT

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

Re: create datasets with name of each make

Patrick — Tue, 02 May 2023 20:48:02 GMT

@BrahmanandaRao 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;