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
%do i =1 %to &sqlobs;
data out_&&country&i ;
set have(where=(country="&&country&i"));
run;
%end;
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
%do i =1 %to &sqlobs;
data out_&&country&i ;
set have(where=(country="&&country&i"));
run;
%end;
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.