proc sql into clause

Reply
Occasional Contributor
Posts: 13

proc sql into clause

Hello all;

 I have a dataset like this

Country Sales

A             100

B              200

A              500

C              600

D              800

 

I want to create datasets for individual countries, Like in above example there are 4 distnict countries so i want to create 4 datasets using proc sql into clause.

 

I wrote a patial code which is given below-

 


data info;
input country $ sales;
cards;
a 100
b 100
a 200
c 500
d 300
;
run; proc print; run;

proc sql;
select count(distinct(country)) into :n separated by ''
from work.info;
quit;
 

proc sql;
select distinct(country) into :var1-:var&n.
from work.info;
quit;

%macro name();
%do i=1 %to &n.;
data &&country&i..new;
set work.new;
where country= &&country&i;
run;
%end;
%mend name;
% name();

 

but in log window error is showing as "Statement is not valid or it is used out of proper order."

 

and i am not able to create datasets like country1 country2..and so on..
and i have one more doubt, where these datasets will be available, will I have to use libname to save these datasets in a particular location, if yes then how it will use.


Thanks in advance

 

Super User
Posts: 5,074

Re: proc sql into clause

After the first QUIT statement, add this line:

 

%let n = &n;

 

That will remove the leading blanks from the value of &n that are causing the problem.  There are other ways to solve the leading blanks, that's just one easy way.

 

Occasional Contributor
Posts: 13

Re: proc sql into clause

that problem has been solved by using separated by ' '

 

but problem is somewhere in parametrize macro and 

Super User
Posts: 17,750

Re: proc sql into clause

If you know where the problem is, please say that upfront.
Check your WHERE clause, it likely needs quotes.

As always, note that splitting your datasets is usually not good practice but I assume this is homework/practice not a real implementation.
Super User
Posts: 10,466

Re: proc sql into clause

Why are you trying to use macro variables named COUNTRY when you created VAR1 - varn.

 

Also I think you were going to be comparing 'A' to 'a', at least from your example data.

%macro name();
%do i=1 %to &n.;
data &&country&i..new;
set work.new;
where UPcase(country)= upcase( "&&Var&i");
run;
%end;
%mend name;

Also you only need one proc sql.

 

proc sql;
select distinct(country) into :var1-:var9999 
from work.info;
quit;
%let n = &sqlobs;

Provide a larger number than you expect and only the ones assigned values are created. The automatic variable &sqlobs holds the number of the results from the select.

 

You can use

%put _user_; to verify that only the number of VAR variables is created that you need.

Ask a Question
Discussion stats
  • 4 replies
  • 151 views
  • 0 likes
  • 4 in conversation