SAS SQL INTO CLAUSE

[Ankur32]

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 new;
input country $ sales;
cards;
a 100
b 200
a 400
c 500
z 800
;
run; proc print; run;

proc sql;
selecct count(distinct(country)) into :n
from work.new;
quit;

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

there are showing some error in 2nd sql command because i am not able to see 

a in &var1

b in &var2

etc..

can you guys help me out, where I am comminting mistake and to complete the code.

Thanks in advance

[Astounding]

The problem is occurring because macro variables hold character strings.  Instead of getting "4" as the value of &n, SQL is converting the value 4 to a character string, and inserting leading blanks as part of the conversion process.  The solution is relatively easy.  Insert this line:

%let n = &n;

It can go anywhere after the SELECT statement that creates &n (before or after the QUIT statement doesn't matter).

[BrunoMueller]

In your code you use something like

proc sql;
select count(distinct(make)) into :n
from sashelp.cars;
quit;

%put NOTE: *&n*;
%let n = &n;
%put NOTE: *&n*;

when you look at the content of the macro var N, the first %PUT, you will see that you have leading blanks, they propably cause the error. A simple assignment statement will remove the leading blanks and everything will be fine.

Depending on your SAS version you might also be able to use the TRIMMED option after the :n

[art297]

You can also fix it as you create the macro variable. e.g.:

data new;
input country $ sales;
cards;
a 100
b 200
a 400
c 500
z 800
;
run; 

proc sql;
  select strip(put(count(distinct(country)),8.)) into :n
    from work.new
  ;
quit;

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

Art, CEO, AnalystFinder.com

[sasmih]

Another option is to use a single proc sql and then you don't need to worry about &n: 

data new;
   input country $ sales;
cards;
a 100
b 200
a 400
c 500
z 800
;

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

... Martha

[Haikuo]

This topic has been touched many times, @Tom had a very nice summary, I couldn't find his original post, but this is what I have saved:

2    proc sql noprint ;

3      select max(age) into :a1 from sashelp.class;

4    %put a1=|&a1|;

a1=|      16|

5    %let a1=&a1;

6    %put a1=|&a1|;

a1=|16|

7

8      select max(age) into :a1 - :a1 from sashelp.class;

9    %put a1=|&a1|;

a1=|16|

10

11     select max(age) into :a1 separated by 'anything' from sashelp.class;

12   %put a1=|&a1|;

a1=|16|

13

14   * SAS 9.3 or higher ;

15     select max(age) into :a1 -  from sashelp.class;

16   %put a1=|&a1|;

a1=|16|

17

18     select max(age) into :a1 trimmed from sashelp.class;

19   %put a1=|&a1|;

a1=|16|

20   quit;

[Ksharp]

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

OR

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

[Ankur32]

Now I want 4 datasets for separte separte countries which contains the country and their sales.

i write this code-

%macro name ()

%do i=1 to&n.;

data &&country&i..;

set work.new;

where country=&&country&i;

run;

%end;

%mend name();

% name ();

but 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