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