topic Re: Macro - To create multiple dataset in SAS Programming

Macro - To create multiple dataset

AshokD — Sat, 01 Sep 2018 20:05:45 GMT

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

Re: Macro - To create multiple dataset

novinosrin — Sat, 01 Sep 2018 20:46:34 GMT

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/

Re: Macro - To create multiple dataset

Reeza — Sat, 01 Sep 2018 22:24:02 GMT

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

Re: Macro - To create multiple dataset

Ksharp — Sun, 02 Sep 2018 10:43:13 GMT

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;

Re: Macro - To create multiple dataset

AshokD — Sun, 02 Sep 2018 15:12:02 GMT

@Ksharp @novinosrin @novinosrin

Thanks for your inputs.

Re: Macro - To create multiple dataset

AshokD — Sun, 02 Sep 2018 16:52:22 GMT

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

Re: Macro - To create multiple dataset

Tom — Sun, 02 Sep 2018 17:03:26 GMT

Macro triggers are not evaluated inside of single quotes. Also dataset names do not have quotes in them.
You are not using your looping variable inside the loop.
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;

Re: Macro - To create multiple dataset

AshokD — Sun, 02 Sep 2018 17:29:29 GMT

@Tom

It works, Thanks for the help.