topic Re: SAS SQL INTO CLAUSE in SAS Programming

SAS SQL INTO CLAUSE

Ankur32 — Wed, 14 Jun 2017 14:41:15 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 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

Re: SAS SQL INTO CLAUSE

Astounding — Wed, 14 Jun 2017 14:51:06 GMT

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

Re: SAS SQL INTO CLAUSE

BrunoMueller — Wed, 14 Jun 2017 14:52:35 GMT

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

Re: SAS SQL INTO CLAUSE

art297 — Wed, 14 Jun 2017 15:07:16 GMT

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

Re: SAS SQL INTO CLAUSE

sasmih — Wed, 14 Jun 2017 15:13:52 GMT

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

Re: SAS SQL INTO CLAUSE

Haikuo — Wed, 14 Jun 2017 15:44:16 GMT

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;

Re: SAS SQL INTO CLAUSE

Ksharp — Thu, 15 Jun 2017 14:15:42 GMT

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

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

Re: SAS SQL INTO CLAUSE

Ankur32 — Thu, 15 Jun 2017 16:20:17 GMT

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