Macro - To create multiple dataset

[AshokD]

Hi All,

I need some of your help on the below scenario:-

#1)  I have data like below and need to create multiple dataset using macro based on the country, so need to create 5 different datasets Can someone provide a good approach for this ?

EmpID  Amount Country

123          $200   US

456          $300  India

678          $500  China

980          $300  Japan

700          $600   Singapore

#2)  I have a data like below and need to pull the highlighted rows since age is different in the 3rd transaction when compared to 1st and 2nd transaction. I tried self join ,it's working fine but do we have anyother way in datastep ?

EmpID  TrnsctID  Age

123        900         30

123        500         30

567        400         28

987        300         27

123        200         28

[novinosrin]

http://www.sascommunity.org/wiki/Split_Data_into_Subsets

https://blogs.sas.com/content/sasdummy/2015/01/26/how-to-split-one-data-set-into-many/

[AshokD]

data have;
length Country $30.;
input EmpID Trnsct Country $;
cards;
123 200 US
456 300 India
678 500 China
980 300 Japan
700 600 Singapore
800 240 US
;
run;

%macro a;
option mprint mlogic symbolgen;
proc sql;
select distinct country
into :country1 - :country99
from have;
quit;

%put &country1 &country2 &country3 &country4 &country5 &sqlobs;

%do i =1 %to &sqlobs;
DATA out_'&country..&sqlobs..';
set have(where=(country='&country.&sqlobs.'));
run;
%end;

%mend a;
%a;

I'm facing issue in resolving the macro to split the dataset. Can someone help 

[Tom · Accepted solution]

1. Macro triggers are not evaluated inside of single quotes. Also dataset names do not have quotes in them.
2. You are not using your looping variable inside the loop.
3. You need to delay the evaluation of the first & until after the loop number has been evaluate. So double the first &.

%do i =1 %to &sqlobs;
data out_&&country&i ;
  set have(where=(country="&&country&i"));
run;
%end;

[AshokD]

@Tom

It works, Thanks for the help.

[Reeza]

Regarding #2 you can use FIRST/LAST logic with BY groups. 

You would be looking for the:

if first.age and not first.empID;

---

@AshokD wrote:

Hi All,

I need some of your help on the below scenario:-

#1)  I have data like below and need to create multiple dataset using macro based on the country, so need to create 5 different datasets Can someone provide a good approach for this ?

EmpID  Amount Country

123          $200   US

456          $300  India

678          $500  China

980          $300  Japan

700          $600   Singapore

#2)  I have a data like below and need to pull the highlighted rows since age is different in the 3rd transaction when compared to 1st and 2nd transaction. I tried self join ,it's working fine but do we have anyother way in datastep ?

EmpID  TrnsctID  Age

123        900         30

123        500         30

567        400         28

987        300         27

123        200         28

---

[Ksharp]

For the second question.

data have;
input EmpID  TrnsctID  Age;
cards;
123        900         30
123        500         30
567        400         28
987        300         27
123        200         28
;
run;

proc sql;
create table want as
 select *
  from have 
   group by EmpID
    having count(distinct Age) > 1;
quit;

[AshokD]

@Ksharp@novinosrin@novinosrin

Thanks for your inputs.