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 info;
input country $ sales;
cards;
a 100
b 100
a 200
c 500
d 300
;
run; proc print; run;

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

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

%macro name();
%do i=1 %to &n.;
data &&country&i..new;
set work.new;
where country= &&country&i;
run;
%end;
%mend name;
% name();

 

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

 

4 REPLIES 4
Astounding
PROC Star

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.

 

Ankur32
Obsidian | Level 7

that problem has been solved by using separated by ' '

 

but problem is somewhere in parametrize macro and 

Reeza
Super User
If you know where the problem is, please say that upfront.
Check your WHERE clause, it likely needs quotes.

As always, note that splitting your datasets is usually not good practice but I assume this is homework/practice not a real implementation.
ballardw
Super User

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.

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
  • 4 replies
  • 1318 views
  • 0 likes
  • 4 in conversation