BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
adi121
Fluorite | Level 6

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

 

View solution in original post

4 REPLIES 4
PeterClemmensen
Tourmaline | Level 20

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
Super User Tom
Super User

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
Fluorite | Level 6

@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
Super User Tom
Super User
Update to a version of SAS that was released sometime in the last 5 years.

SAS Innovate 2025: Call for Content

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!

Submit your idea!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 4 replies
  • 762 views
  • 1 like
  • 3 in conversation