data country;
input id name$ country$;
cards;
11 alex usa
12 reno uk
14 tanya india
15 john china
;run;
as id columns is unique, i want to create new dataset based on id column.
%macro createm;
proc sql noprint;
select distinct id into :id1-:id99
from country;
quit;
%let cnum=&sqlobs;
%do i=1 %to &cnum;
%let dsname=name_&&id&i;
data &dsname;
set country;
where id =&id;
run;
%end;
%mend;
Options macrogen symbolgen mlogic mprint;
%createm;
here datasets are created but datasets are blank and i'm getting the error at where line : ERROR: Syntax error while parsing WHERE clause.
when i change it to where id ="&id" //then here also getting error :Where clause operator requires compatible variables.
also can i make it more dymanic here like id1-:id99 ,can i pass a macro here without knowing the no of observations
Pls help
Note it is probably a terrible idea to create many tiny datasets, but let's play along and answer your questions.
You are making macro variables named ID1, ID2, etc. from the variable named NAME.
That is why
where id="&id";
is wrong.
Use
where name="&&id&i";
To your second question, you are already somewhat making it independent of the number of observations. Only you set the upper bound on the number of observations at 99. You could just raise that upper bound.
into :id1-:id99999
But you can also just not set any upper bound.
into :id1-
Are you sure the values of your variable are valid strings to use in the name of the generated datasets? Perhaps instead you should just name the datasets using the do loop index, then you are sure the value is valid in a name. You could put the value into LABEL of the dataset instead.
%let dsname=name_&i;
data &dsname (label="&&id&i");
Here is one way.. This will handle when there are multiple obs for each id
data country;
input id name$ country$;
cards;
11 alex usa
12 reno uk
14 tanya india
15 john china
;run;
data _null_;
if _n_=1 then do;
if 0 then set country;
declare hash h(dataset:"country(obs=0)", multidata:'y');
h.definekey('id');
h.definedata(all:'y');
h.definedone();
end;
do until(last.id);
set country;
by id;
h.add();
end;
h.output(dataset:cats('id_', id));
h.clear();
run;
Note it is probably a terrible idea to create many tiny datasets, but let's play along and answer your questions.
You are making macro variables named ID1, ID2, etc. from the variable named NAME.
That is why
where id="&id";
is wrong.
Use
where name="&&id&i";
To your second question, you are already somewhat making it independent of the number of observations. Only you set the upper bound on the number of observations at 99. You could just raise that upper bound.
into :id1-:id99999
But you can also just not set any upper bound.
into :id1-
Are you sure the values of your variable are valid strings to use in the name of the generated datasets? Perhaps instead you should just name the datasets using the do loop index, then you are sure the value is valid in a name. You could put the value into LABEL of the dataset instead.
%let dsname=name_&i;
data &dsname (label="&&id&i");
@TomThanks it is working fine now as expected.
But you mentioned to make dynamic
into :id1-
so implenting above gives me syntax error.
I have to use into :id1-
id99.
Thanks!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.