BookmarkSubscribeRSS Feed
Ankur32
Obsidian | Level 7

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

7 REPLIES 7
Astounding
PROC Star

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
SAS Super FREQ

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
Opal | Level 21

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
SAS Employee

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
Onyx | Level 15

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
Super User

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
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1839 views
  • 1 like
  • 7 in conversation