topic Re: proc sql into clause in SAS Programming

proc sql into clause

Ankur32 — Thu, 15 Jun 2017 16:38:26 GMT

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

Re: proc sql into clause

Astounding — Thu, 15 Jun 2017 16:46:04 GMT

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.

Re: proc sql into clause

Ankur32 — Thu, 15 Jun 2017 16:49:14 GMT

that problem has been solved by using separated by ' '

but problem is somewhere in parametrize macro and

Re: proc sql into clause

Reeza — Thu, 15 Jun 2017 17:12:49 GMT

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.

Re: proc sql into clause

ballardw — Fri, 16 Jun 2017 17:38:21 GMT

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.