Hi
I have a dataset with the following info
1234 30004 USD
1234 30012 USD
1289 30056 USD
1389 45009 INR
1456 56789 INR
1456 30067 USD
1567 30099 USD
1567 67890 GBP
1567 30088 INR
1567 30092 INR
I need ouput like this
1234 USD
1289 USD
1389 INR
1456 INR,USD
1567 USD,GBP,INR
how to do it,please help me
data have;
input id1 $ id2 $ currency $;
cards;
1234 30004 USD
1234 30012 USD
1289 30056 USD
1389 45009 INR
1456 56789 INR
1456 30067 USD
1567 30099 USD
1567 67890 GBP
1567 30088 INR
1567 30092 INR
;
proc sql;
select max (ct) into :ct from (select count(*) as ct from have group by id1) ;quit;
data want;
array t(&ct) $3 _temporary_;
call missing(of t(*));
do _n_=1 by 1 until (last.id1);
set have;
by id1 notsorted;
if currency not in t then
t(_n_)=currency;
end;
cat_currency=catx(',', of t(*));
keep id1 cat_currency;
run;
As you can see, this code is not as efficient as I want it to be, an additional pre-process has to be done to get the range of array().
Haikuo
Thanks for the reply and can you please provide the code using base sas
Alterrnate solution -
proc sql;
create table want1 as
select id
, currency
from have
group by 1,2
order by 1,2;
quit;
proc transpose data=want1 out=want_wide;
by id;
var currency;
run;
data want_final;
set want_wide;
currency1=trim(col1)||' '||trim(col2)||' '||trim(col3);
run;
I have over-complicated your problem, just simply do:
data want1;
do until (last.id1);
set have;
by id1 notsorted;
length cat_currency $50;
if findw(cat_currency,currency)=0 then cat_currency=catx(',',cat_currency,currency);
end;
keep id1 cat_currency;
run;
No array or macro variable is needed.
Haikuo
I tried like this, but i am not getting accurate answer
The actual data is in dataset smple
DATA SAMPLE1;
LENGTH CURRENCY CURRENCY1 $50;
SET SAMPLE;
BY CUST_ID;
IF FIRST.CUST_ID THEN CURRENCY1='';
PUT _ALL_;
CURRENCY1=CATX(',',trim(CURRENCY1),trim(CURRENCY));
IF LAST.CUST_ID;
RUN;
You need to check for currency to already be in currency1. Also, trim is not necessary in in catx arguments, but it is in findw word argument. Most importantly, you must use a retain statement on currency1 because otherwise it is reset to missing at every iteration. Try it this way :
DATA SAMPLE1;
LENGTH CURRENCY CURRENCY1 $50;
retain currency1;
SET SAMPLE;
BY CUST_ID;
IF FIRST.CUST_ID THEN call missing(CURRENCY1);
if findw(currency1,trim(currency))=0 then CURRENCY1=CATX(',', CURRENCY1, CURRENCY);
IF LAST.CUST_ID;
RUN;
PG
data temp;
input id zip cntr $;
cards;
1234 30004 USD
1234 30012 USD
1289 30056 USD
1389 45009 INR
1456 56789 INR
1456 30067 USD
1567 30099 USD
1567 67890 GBP
1567 30088 INR
1567 30092 INR
;
proc sort data=temp(drop=zip) out=temp1 nodupkey;
by id cntr;
run;
data temp2(drop=cntr);
set temp1;
retain cntry;
length cntry $20.;
by id cntr;
if first.id and last.id then do;
cntry=cntr;
output temp2;
end;
else if first.id and last.id = 0 then do;
cntry=cntr;
end;
else if first.id = 0 and last.id = 0 then do;
cntry = catx(',',cntry,cntr);
end;
else if first.id = 0 and last.id = 1 then do;
cntry = catx(',',cntry,cntr);
output temp2;
end;
run;
@avishek2411: I think you have the right idea. But the data step after proc sort can be simplified greatly by have a SET and BY statement inside a DO UNTIL (LAST.ID) loop:
data temp;
input id zip cntr $;
cards;
1234 30004 USD
1234 30012 USD
1289 30056 USD
1389 45009 INR
1456 56789 INR
1456 30067 USD
1567 30099 USD
1567 67890 GBP
1567 30088 INR
1567 30092 INR
;
proc sort data=temp(drop=zip) out=temp1 nodupkey;
by id cntr;
run;
data want;
do until (last.id);
set temp1;
by id;
length cntr_list $20;
cntr_list=catx(',',cntr_list,cntr);
end;
drop cntr;
run;
The only downside is that the currency list for each id is alphabetic. It is no longer in the order of appearance. Have to ask @sahaji whether that is meaningful deficiency.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.