create dataset for every observation

[adi121]

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

[PeterClemmensen]

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;

[Tom · Accepted solution]

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

[adi121]

@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!

[Tom]

Update to a version of SAS that was released sometime in the last 5 years.