06-15-2017 12:38 PM
I have a dataset like this
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-
input country $ sales;
run; proc print; run;
select count(distinct(country)) into :n separated by ''
select distinct(country) into :var1-:var&n.
%do i=1 %to &n.;
where country= &&country&i;
but in log window error is showing as "Statement is not valid or it is used out of proper order."
and 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
06-15-2017 12:46 PM
After the first QUIT statement, add this line:
%let n = &n;
That will remove the leading blanks from the value of &n that are causing the problem. There are other ways to solve the leading blanks, that's just one easy way.
06-15-2017 01:12 PM
06-16-2017 01:38 PM
Why are you trying to use macro variables named COUNTRY when you created VAR1 - varn.
Also I think you were going to be comparing 'A' to 'a', at least from your example data.
%macro name(); %do i=1 %to &n.; data &&country&i..new; set work.new; where UPcase(country)= upcase( "&&Var&i"); run; %end; %mend name;
Also you only need one proc sql.
proc sql; select distinct(country) into :var1-:var9999 from work.info; quit; %let n = &sqlobs;
Provide a larger number than you expect and only the ones assigned values are created. The automatic variable &sqlobs holds the number of the results from the select.
You can use
%put _user_; to verify that only the number of VAR variables is created that you need.