SAS SQL INTO CLAUSE

Reply
Occasional Contributor
Posts: 13

SAS SQL INTO CLAUSE

[ Edited ]

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

Super User
Posts: 5,518

Re: SAS SQL INTO CLAUSE

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).

SAS Super FREQ
Posts: 709

Re: SAS SQL INTO CLAUSE

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

PROC Star
Posts: 7,492

Re: SAS SQL INTO CLAUSE

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

 

SAS Employee
Posts: 1

Re: SAS SQL INTO CLAUSE

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

 

Respected Advisor
Posts: 3,156

Re: SAS SQL INTO CLAUSE

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;
Super User
Posts: 10,046

Re: SAS SQL INTO CLAUSE

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;

Occasional Contributor
Posts: 13

Re: SAS SQL INTO CLAUSE

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

Ask a Question
Discussion stats
  • 7 replies
  • 349 views
  • 1 like
  • 7 in conversation