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
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).
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
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
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
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;
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.